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'