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

[Joe Harris]

This is not available in the query builder. I asked the same question a while back and basically the query builder works on the tickets taw only and cannot do cross queries. I ended up writing a shell script for my needs which basically was "any update performed by any member of a particular group yesterday". The next morning it is sent to the manager (or the group) depending on the system and manager. I can share if you're interested. I "may" have already shared an example on the list here if you search back a month or two. 

Sent from my mobile device. 

On Jun 12, 2012, at 5:22 PM, Brian Schrock <Brian.Schrock at gardencitygroup.com> wrote:

> 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. J
>  
> 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/2aea9044/attachment.htm>