Reporting on reopened tickets

You can use the tickets_logs table  to report on tickets which have been reopened after an Agent or User has assigned the Resolved status.


Example Query


The query below will genetate a list of reopened ticket IDs with the subject, agent name and user name.

SELECT tickets_logs.ticket.subject, tickets_logs.ticket.agent, tickets_logs.ticket.person.name

FROM tickets_logs

WHERE tickets_logs.action_type = 'changed_status' AND tickets_logs.id_before = '200' AND tickets_logs.id_after <> '210'

GROUP BY tickets_logs.ticket.id


In this example the id 200 represents resolved. Hence the status changed being measured is from 'Resolved' to another status.


Handling Archiving in the query


You can see we've also used the ID 210 in the query. This actually represents the archived status.  

Archiving is an optional feature but if you have it enabled you would need to ensure you have tickets moved from Resolved -> Archived excluded from the stat as this would not constitute a reopen.

 Hence we use the tickets.id_after function alongside the does not equal operator <> to exclude them  tickets_logs.id_after <> '210'  

Helpful Unhelpful

1 of 1 people found this page helpful

Add a comment

You need to log in before you can submit a comment.

Need a password reminder?