We are using RT3 and I am writing reports in Crystal to send out weekly emails to people that say “Here are all your Open Tickets… blah blah”
I am having a hard time finding the connection between tickets and users.
I was expecting to see a table called Watchers or something that would have ticket_id, user_id, watcher_type but I’ve looked through all the tables
and nothing comes close to matching that.
I’ve searched the archives and I’ve found some hints… but I can’t use them.
I have no access to the web server, so I can’t look at the log files in debug mode or even look at the script behind the pages. All I have access to
is the MySql Database where RT3 lives.
I also do not want to use the Perl API which was also suggested in another thread.
Currently, my query looks like this:
select Queues.Name, Tickets.id, Tickets.Subject, Tickets.Priority, Tickets.Status, Owners.Name, Owners.EmailAddress, Owners.RealName
, Tickets.Created, Tickets.LastUpdated
from Users Owners,
Queues,
Tickets
where Tickets.Owner = Owners.id
and Tickets.Queue = Queues.id
and Tickets.status in (‘open’, ‘stalled’)
and I just need to add Requestors and I’ve got it.
As far as I remember you’ll find these in the Groups table where the
Instance is your Ticket number and the Type describe the kind of watcher
(admincc, requestor etc), you need to join with the cachedgroupmembers …
sorry can’t be more specific, I have not an RT console accessible at the
moment.
We are using RT3 and I am writing reports in Crystal to send out weekly
emails to people that say “Here are all your Open Tickets… blah blah”
I am having a hard time finding the connection between tickets and users.
I was expecting to see a table called Watchers or something that would
have ticket_id, user_id, watcher_type but I’ve looked through all the tables
and nothing comes close to matching that.
I’ve searched the archives and I’ve found some hints… but I can’t use
them.
I have no access to the web server, so I can’t look at the log files in
debug mode or even look at the script behind the pages. All I have access to
is the MySql Database where RT3 lives.
I also do not want to use the Perl API which was also suggested in another
thread.
Currently, my query looks like this:
select Queues.Name, Tickets.id, Tickets.Subject, Tickets.Priority,
Tickets.Status, Owners.Name, Owners.EmailAddress, Owners.RealName
, Tickets.Created, Tickets.LastUpdated
from Users Owners,
Queues,
Tickets
where Tickets.Owner = Owners.id
and Tickets.Queue = Queues.id
and Tickets.status in (‘open’, ‘stalled’)
and I just need to add Requestors and I’ve got it.
We are using RT3 and I am writing reports in Crystal to send out weekly emails to people that say “Here are all your Open Tickets… blah blah”
I am having a hard time finding the connection between tickets and users.
I was expecting to see a table called Watchers or something that would have ticket_id, user_id, watcher_type but I’ve looked through all the tables
and nothing comes close to matching that.
I’ve searched the archives and I’ve found some hints… but I can’t use them.
I have no access to the web server, so I can’t look at the log files in debug mode or even look at the script behind the pages. All I have access to
is the MySql Database where RT3 lives.
I also do not want to use the Perl API which was also suggested in another thread.
Currently, my query looks like this:
select Queues.Name, Tickets.id, Tickets.Subject, Tickets.Priority, Tickets.Status, Owners.Name, Owners.EmailAddress, Owners.RealName
, Tickets.Created, Tickets.LastUpdated
from Users Owners,
Queues,
Tickets
where Tickets.Owner = Owners.id
and Tickets.Queue = Queues.id
and Tickets.status in (‘open’, ‘stalled’)
and I just need to add Requestors and I’ve got it.