[rt-users] Search Ticket Transaction History in Query Builder?

[Brian Schrock]

All,

The issues are:


1)      How do I search transaction history using the query builder?

2)      How do I convert an SQL statement like the one lower down into something I can use in a custom report?

People here in my company have asked me if they can get a report on the number of tickets whose status was changed from stalled or resolved into open during a specific time frame because of an external e-mail from a customer. I think I have the SQL statement working, but I cannot figure out how to do the same thing with RT's query builder. I have been all over google and the docs and I am not finding anything useful to help point me in the right direction. It looks like this issue comes up every so often on the mailing list, but no one has really been answered well, I hope I fare better. :)

Any suggestions or help would be greatly appreciated.

SELECT Tickets.id, Tickets.Created, Tickets.Subject, Tickets.Status, Queues.Name
FROM Transactions, Tickets, Queues
WHERE
Tickets.Queue=Queues.id
AND Queues.Name="Test Queue 1"
AND Tickets.Status='open'
AND Transactions.ObjectId=Tickets.id
AND Transactions.OldValue REGEXP "stalled|resolved"
AND Transactions.NewValue='open'
AND Transactions.Data = 'Ticket auto-opened on incoming correspondence'
AND Transactions.Created >= @START
AND Transactions.Created <= @FINISH

Brian Schrock
Linux Administrator
Network Operations
The Garden City Group, Inc.
5151 Blazer Parkway Suite A
Dublin, ohio 43017
Telephone: 614-289-5457
Mobile: 614-745-5491
Email: Brian.Schrock at gardencitygroup.com
________________________________

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20120612/444af39c/attachment.htm>