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

[Thomas Sibley]

> 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

Your join between Tickets and Transactions is wrong.  Transactions also
apply to objects other than tickets, so you need to limit by ObjectType
too not just ObjectId.

(The query is also not quite what you want because it will miss tickets
which are currently Status != 'open' but were auto-opened at some point
earlier in the time frame.)

To solve your problem using RT's normal customization routes, I suggest
extending the default auto-open scrip (user-defined action) to set a
DateTime custom field on the ticket when it fires.  This greatly
simplifies your search and lets you run it in RT from the web.

Your report can then be a standard RT saved search used in a chart or a
dashboard.