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

Example Query

The query below will generate 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 change 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 represents the Archived status.  

Archiving is an optional feature, but if it’s 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'.