I'm trying to create a custom report to find out which agents take "waiting" tickets by themselfes and who of them are just waiting untill some other agent assign them to it.
I was trying to use SQL query for table "ticket_history". Records with type_id 23 are resposible for change owner action. And I took the very first "id-23 record" for each ticket and was comparing new owner with "change by" column. If it was the same it meant that agent took the ticket by himself. In the other way the ticket was assigned to him.
It work fine for small instance of OTRS. Unfortunatelly in my production environment I've got couple milions of records in ticket_history table and my script just stucks.
Have you ever tried to do simmilar thing and some of you does know more optimal solution for it?
Code: Select all
SELECT users.login, SUM(owner_id=th.change_by) as self, SUM(owner_id!=th.change_by) as sb
FROM ticket_history th LEFT JOIN users ON th.owner_id=users.id
WHERE th.id IN (SELECT min(id) FROM ticket_history
WHERE history_type_id=23 GROUP BY ticket_id) AND DATE(th.change_time) >= 'Start Date' AND DATE(th.change_time) <= 'End Date'
GROUP BY users.login ORDER BY users.login