This report will output a list of tickets that have been reopened with agent and customer details:
SELECT tickets_logs.ticket.id AS 'Ticket ID', tickets_logs.ticket.subject, tickets_logs.ticket.agent, tickets_logs.ticket.person.name, tickets_logs.date_created AS 'Date Reopened'
WHERE tickets_logs.action_type = 'changed_status' AND tickets_logs.id_before = '200' AND tickets_logs.id_after <> '210'
GROUP BY tickets_logs.id AS 'Ticket log ID'
ORDER BY tickets_logs.ticket.agent
Here we're using the ticket log ids to see when a ticket is changing from the status 'Resolved' to another status.
We're referencing the status code '200' which represents resolved.
We're also using '210' which is the code for archived to exclude archived tickets from the report.
The full list of status codes is:
And you can read the full list of available ticket fields in the logs table in our reports guide.