K1000 Ticket Report: Time from ticket create to first ownership change.
The title pretty much says it.
I'm looking for away to get a report on the number of tickets for last month and the average time it took from ticket creation to a ownership change based on priority.
Or broke down like the popular ticket closure times report that is VERY help on here.
Priority - Number tickets - Average time to take ownership
High - 15 - 00:22:00
medium - 80 - 00:45:00
Low - 200 - 01:015:00
Something like that
I'm looking for away to get a report on the number of tickets for last month and the average time it took from ticket creation to a ownership change based on priority.
Or broke down like the popular ticket closure times report that is VERY help on here.
Priority - Number tickets - Average time to take ownership
High - 15 - 00:22:00
medium - 80 - 00:45:00
Low - 200 - 01:015:00
Something like that
0 Comments
[ + ] Show comments
Answers (2)
Please log in to answer
Posted by:
chucksteel
9 years ago
How does this look?
SELECT
P.NAME, COUNT(T.ID), TIME_FORMAT(AVG(TIMEDIFF(C.TIMESTAMP, T.CREATED)), "%H:%i:%s")
FROM HD_TICKET T
JOIN HD_TICKET_CHANGE C on C.HD_TICKET_ID = T.ID
JOIN HD_TICKET_CHANGE_FIELD CF on CF.HD_TICKET_CHANGE_ID = C.ID AND FIELD_CHANGED = "OWNER_ID" and CF.BEFORE_VALUE = 0
JOIN HD_PRIORITY P on P.ID = T.HD_PRIORITY_ID
WHERE T.HD_QUEUE_ID = 2
AND T.CREATED > NOW() - INTERVAL 31 DAY
GROUP BY P.ID
Comments:
-
That seems close, but might be averaging on the wrong value.
Here is the results i get back
# NAME, #, Time
'Medium', '113', NULL
'High', '35', NULL
'Low', '313', NULL
'Critical', '4', '00:53:54'
'Assessment/Investigation', '3', NULL - jharrell 9 years ago-
It looks like a problem with the time_format command although I'm not sure why. If I use just AVG(TIMEDIFF(C.TIMESTAMP, T.CREATED)) it reports the number of seconds correctly, but for some values the time_format command is returning NULL. I added a function to convert seconds to time and that seems to work. Use TIME_FORMAT(SEC_TO_TIME(AVG(TIMEDIFF(C.TIMESTAMP, T.CREATED))),"%H:%i:%s") instead of TIME_FORMAT(AVG(TIMEDIFF(C.TIMESTAMP, T.CREATED)) and see if that works for you. - chucksteel 9 years ago
-
Thats better.
# NAME, Time
'Medium', '14:08:16'
'Low', '06:32:10'
'High', '03:20:03'
'Critical', '01:01:45'
'Assessment/Investigation', '13:49:32'
Just for some understanding, this is looking at the avg time it takes a ticket's owner to change from Unassigned to TechName?
1 hour, for the last 30 days worth of tickets, seems way off. I would hope its more like 1 minute.
SELECT
P.NAME, TIME_FORMAT(SEC_TO_TIME(AVG(TIMEDIFF(C.TIMESTAMP, T.CREATED))),"%H:%i:%s") as 'Time'
FROM HD_TICKET T
JOIN HD_TICKET_CHANGE C on C.HD_TICKET_ID = T.ID
JOIN HD_TICKET_CHANGE_FIELD CF on CF.HD_TICKET_CHANGE_ID = C.ID AND FIELD_CHANGED = "OWNER_ID" and CF.BEFORE_VALUE = 0
JOIN HD_PRIORITY P on P.ID = T.HD_PRIORITY_ID
WHERE T.HD_QUEUE_ID = 1
AND T.CREATED > NOW() - INTERVAL 31 DAY
GROUP BY P.ID - jharrell 9 years ago -
Yes. To sanity check the data you can use this query:
SELECT
T.TITLE, T.ID, Q.NAME, P.NAME, C.TIMESTAMP, T.CREATED, TIMEDIFF(C.TIMESTAMP, T.CREATED)
FROM HD_TICKET T
JOIN HD_TICKET_CHANGE C on C.HD_TICKET_ID = T.ID
JOIN HD_TICKET_CHANGE_FIELD CF on CF.HD_TICKET_CHANGE_ID = C.ID AND FIELD_CHANGED = "OWNER_ID" and CF.BEFORE_VALUE = 0
JOIN HD_PRIORITY P on P.ID = T.HD_PRIORITY_ID
JOIN HD_QUEUE Q on Q.ID = T.HD_QUEUE_ID
WHERE
T.CREATED > NOW() - INTERVAL 31 DAY
To restrict that to a specific Priority and Queue add these lines:
and P.NAME = "Priority"
and Q.NAME = "Queue"
This will show each ticket with their priority, when they were created, the time of the change and the difference. - chucksteel 9 years ago -
Perfect!!.
That did it. I was seeing tickets that we never take ownership of untill they are approved. That was throwing off the avg.
Chuck, you the man!! - jharrell 9 years ago
Posted by:
chucksteel
9 years ago
This query might be interesting for you to look at then:
SELECT
T.TITLE, T.ID, Q.NAME, P.NAME,
T.CREATED,
C.TIMESTAMP as "Owner Changed",
TIMEDIFF(C.TIMESTAMP, T.CREATED) as "Created to Owner",
CA.TIMESTAMP as "Approval Changed",
TIMEDIFF(CA.TIMESTAMP, T.CREATED) as "Created to Approval",
TIMEDIFF(C.TIMESTAMP, CA.TIMESTAMP) as "Approval to Owner"
FROM HD_TICKET T
JOIN HD_TICKET_CHANGE C on C.HD_TICKET_ID = T.ID
JOIN HD_TICKET_CHANGE CA on CA.HD_TICKET_ID = T.ID
JOIN HD_TICKET_CHANGE_FIELD CF on CF.HD_TICKET_CHANGE_ID = C.ID AND CF.FIELD_CHANGED = "OWNER_ID" and CF.BEFORE_VALUE = 0
JOIN HD_TICKET_CHANGE_FIELD CFA on CFA.HD_TICKET_CHANGE_ID = CA.ID AND CFA.FIELD_CHANGED = "APPROVAL" and CFA.AFTER_VALUE = "approved"
JOIN HD_PRIORITY P on P.ID = T.HD_PRIORITY_ID
JOIN HD_QUEUE Q on Q.ID = T.HD_QUEUE_ID
This will include when the approval was created and how long it took the ticket to get an owner. This is doesn't compute averages.
