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.schr...@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.schr...@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.
