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