Helpdesk Custom Report - Time taken to change ticket owner from "unassigned" to anything else per day
I am looking to create a report that tells me how long it took for a ticket owner to be changed from "unassigned" to someone else, on the previous days tickets.
We have an SLA that emailed tickets (which automatically log in the unassigned queue) must be picked up and assigned to an individual/team within 2 hours, but we don't have a report that can monitor this.
It will need to be a daily report, running on the previous days logged tickets.
We need the report to show, Ticket ID, time created, time Owner was changed from "unassigned"
Ideally, we would like the report to only show tickets where the time owner was changed from unassigned to something else breached the 2 hour SLA
2 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
chucksteel
5 years ago
This should work:
SELECT T.ID, O.FULL_NAME as "Owner", T.TITLE, T.CREATED,
C.TIMESTAMP as "Ticket Created",
TIMESTAMPDIFF(MINUTE, T.CREATED, C.TIMESTAMP) as "Owner Changed (Minutes)"
FROM HD_TICKET T
LEFT JOIN HD_TICKET_CHANGE C on C.HD_TICKET_ID = T.ID
LEFT JOIN HD_TICKET_CHANGE_FIELD CF on CF.HD_TICKET_CHANGE_ID = C.ID
LEFT JOIN USER O on O.ID = T.OWNER_ID
WHERE
CF.FIELD_CHANGED = "OWNER_ID"
and CF.BEFORE_VALUE = 0
and TIMESTAMPDIFF(MINUTE, T.CREATED, C.TIMESTAMP) > 120
and DATE(T.CREATED) > DATE_SUB(NOW(), INTERVAL 1 DAY)
That will return for all tickets. If you just want tickets created via an email use this:
SELECT T.ID, O.FULL_NAME as "Owner", T.TITLE, T.CREATED,
C.TIMESTAMP as "Ticket Changed",
TIMESTAMPDIFF(MINUTE, T.CREATED, C.TIMESTAMP) as "Owner Changed (Minutes)"
FROM HD_TICKET T
JOIN HD_TICKET_CHANGE IC on IC.HD_TICKET_ID = T.ID and IC.ID = (select min(ID) from HD_TICKET_CHANGE where HD_TICKET_ID = T.ID)
LEFT JOIN HD_TICKET_CHANGE C on C.HD_TICKET_ID = T.ID
LEFT JOIN HD_TICKET_CHANGE_FIELD CF on CF.HD_TICKET_CHANGE_ID = C.ID
LEFT JOIN USER O on O.ID = T.OWNER_ID
WHERE
IC.VIA_EMAIL != ""
and CF.FIELD_CHANGED = "OWNER_ID"
and CF.BEFORE_VALUE = 0
and TIMESTAMPDIFF(MINUTE, T.CREATED, C.TIMESTAMP) > 120
and DATE(T.CREATED) > DATE_SUB(NOW(), INTERVAL 1 DAY)
select C.HD_TICKET_ID,
H.CREATED,
O.FULL_NAME,
H.TITLE,
TIMEDIFF(C.TIMESTAMP,H.CREATED) as TIME_TAKEN,
C.TIMESTAMP as TIME_REASSIGNED from HD_TICKET_CHANGE C
JOIN HD_TICKET H ON (H.ID = C.HD_TICKET_ID)
JOIN USER O ON (O.ID = C.USER_ID)
where description LIKE '%from "unassigned"%' - Druis 5 years ago