Custom Report - Average initial response time grouped by technician for last 28 days
I am looking for a custom report that will output the average response time to a ticket for each technician in each of my queues. I have been unable to create this report myself and any help that you can offer would be greatly appreciated.
0 Comments
[ + ] Show comments
Answers (3)
Please log in to answer
Posted by:
jverbosk
11 years ago
Do either of these help get you going?
http://www.itninja.com/question/how-could-i-change-this-report-to-report-by-ticket-owner-instead-of-queue
http://www.itninja.com/question/i-m-looking-for-a-report-that-would-show-me-hours-worked-per-user
John
Comments:
-
John,
The first link that you provided is very close to what I am looking for. However, My SQL skills are not strong enough to make the necessary modifications. Ideally, what I would like to see instead of an average time to close, is the average time from the time of ticket creation to the first action performed by any technician on the ticket. Any help you can offer in this regard would be superb. - vmescall 11 years ago -
If you run the code in the first link, does it work for you or does it generate errors? Just asking, because it generates errors on my side (as we don't track time here).
John - jverbosk 11 years ago -
Yes, the query from the first link works flawlessly for me. I just need to modify the data so that it is reporting the average time between ticket creation and first action taken by a technician instead of the average time closed.
Basically, my bosses want to see how long a ticket sits, on average, before being addressed by each technician. - vmescall 11 years ago -
Understood. But let me be more specific - does the query I provided as an answer in the first link work OK for you? I never got a reply, so have no idea if it works or not as the original one I based it upon doesn't work for me. And my tweak focuses on the ticket owners, so I can see if it's possible to do what you need, but I want to verify it works before taking the time to do so.
John - jverbosk 11 years ago -
Yes, the query you provided in the first link works great. I appreciate you taking the time to help me sort this out. - vmescall 11 years ago
-
Thanks for confirming it works! It's always disappointing when those asking questions don't bother saying anything after I post a query - particularly ones I can't test myself.
I'll see what I can come up with...
John - jverbosk 11 years ago
Posted by:
jverbosk
11 years ago
OK, give this a spin - this works the same as the last, but the FIRST_ACTION inline view now corresponds to the first comment made by the ticket owner (not counting the ticket created timestamp, in case the ticket owner created the ticket).
Hope that helps (for real this time)! ^_^
John
__________________________
SELECT U.USER_NAME AS OWNER,
(CASE WHEN FIRST_ACTION.MTIMESTAMP < DATE_ADD(IF(OPENED.TIMESTAMP = '0',
FIRST_ACTION.MTIMESTAMP, OPENED.TIMESTAMP), INTERVAL 1 HOUR) THEN '0-1 hour'
WHEN FIRST_ACTION.MTIMESTAMP < DATE_ADD(IF(OPENED.TIMESTAMP = '0',
FIRST_ACTION.MTIMESTAMP, OPENED.TIMESTAMP), INTERVAL 24 HOUR) THEN '1-24 hours'
WHEN FIRST_ACTION.MTIMESTAMP > DATE_ADD(IF(OPENED.TIMESTAMP='0',
FIRST_ACTION.MTIMESTAMP, OPENED.TIMESTAMP), INTERVAL 24 HOUR) THEN '>24 hours'
ELSE 'Error' END) AS RESPONSE_GROUP,
COUNT(T.ID) AS NUMBER_OF_TICKETS,
CONCAT((SUM(TIME_TO_SEC(TIMEDIFF(FIRST_ACTION.MTIMESTAMP,
IF(OPENED.TIMESTAMP = '0', FIRST_ACTION.MTIMESTAMP, OPENED.TIMESTAMP))))
/ COUNT(T.ID) DIV 86400),'d ',
TIME_FORMAT(SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(FIRST_ACTION.MTIMESTAMP,
IF(OPENED.TIMESTAMP = '0', FIRST_ACTION.MTIMESTAMP, OPENED.TIMESTAMP))))
/ COUNT(T.ID) MOD 86400 ), '%kh %im %ss'))
AS AVG_TIME_TO_RESPOND
FROM HD_TICKET T
LEFT JOIN USER U ON (U.ID = T.OWNER_ID)
JOIN
(SELECT C.ID, C.HD_TICKET_ID, C.TIMESTAMP
FROM HD_TICKET_CHANGE C
WHERE
(SELECT COUNT(*)
FROM HD_TICKET_CHANGE FILTER
WHERE FILTER.HD_TICKET_ID = C.HD_TICKET_ID
AND FILTER.ID <= C.ID) = 1
AND C.TIMESTAMP <> 0
AND C.TIMESTAMP > '2012-09-30' /*change the start date here*/
AND C.TIMESTAMP < '2012-10-31' /*change the end date here*/
ORDER BY C.HD_TICKET_ID, C.ID)
OPENED ON (OPENED.HD_TICKET_ID = T.ID)
JOIN
(SELECT C.ID, C.HD_TICKET_ID, MIN(C.TIMESTAMP) AS MTIMESTAMP
FROM HD_TICKET_CHANGE C
JOIN HD_TICKET T ON (T.ID = C.HD_TICKET_ID)
LEFT JOIN USER U ON (U.ID = T.OWNER_ID)
WHERE C.DESCRIPTION != 'Ticket Created'
AND C.TIMESTAMP <> 0
AND C.TIMESTAMP > '2012-09-30' /*change the start date here*/
AND C.TIMESTAMP < '2012-10-31' /*change the end date here*/
GROUP BY C.HD_TICKET_ID
ORDER BY C.HD_TICKET_ID, C.ID)
FIRST_ACTION ON (FIRST_ACTION.HD_TICKET_ID = T.ID)
WHERE T.HD_QUEUE_ID IN (1) /*add queue numbers here*/
GROUP BY OWNER
UNION
SELECT U.USER_NAME AS OWNER,
'ALL' AS RESPONSE_GROUP,
COUNT(T.ID) AS NUMBER_OF_TICKETS,
CONCAT((SUM(TIME_TO_SEC(TIMEDIFF(FIRST_ACTION.MTIMESTAMP,
IF(OPENED.TIMESTAMP = '0', FIRST_ACTION.MTIMESTAMP, OPENED.TIMESTAMP))))
/ COUNT(T.ID) DIV 86400),'d ',
TIME_FORMAT(SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(FIRST_ACTION.MTIMESTAMP,
IF(OPENED.TIMESTAMP = '0', FIRST_ACTION.MTIMESTAMP, OPENED.TIMESTAMP))))
/ COUNT(T.ID) MOD 86400 ), '%kh %im %ss')) AS AVG_TIME_TO_CLOSE
FROM HD_TICKET T
LEFT JOIN USER U ON (U.ID = T.OWNER_ID)
JOIN
(SELECT C.ID, C.HD_TICKET_ID, C.TIMESTAMP
FROM HD_TICKET_CHANGE C
WHERE
(SELECT COUNT(*)
FROM HD_TICKET_CHANGE FILTER
WHERE FILTER.HD_TICKET_ID = C.HD_TICKET_ID
AND FILTER.ID <= C.ID) = 1
AND C.TIMESTAMP <> 0
AND C.TIMESTAMP > '2012-09-30' /*change the start date here*/
AND C.TIMESTAMP < '2012-10-31' /*change the end date here*/
ORDER BY C.HD_TICKET_ID, C.ID)
OPENED ON (OPENED.HD_TICKET_ID = T.ID)
JOIN
(SELECT C.ID, C.HD_TICKET_ID, MIN(C.TIMESTAMP) AS MTIMESTAMP
FROM HD_TICKET_CHANGE C
JOIN HD_TICKET T ON (T.ID = C.HD_TICKET_ID)
LEFT JOIN USER U ON (U.ID = T.OWNER_ID)
WHERE C.DESCRIPTION != 'Ticket Created'
AND C.TIMESTAMP <> 0
AND C.TIMESTAMP > '2012-09-30' /*change the start date here*/
AND C.TIMESTAMP < '2012-10-31' /*change the end date here*/
GROUP BY C.HD_TICKET_ID
ORDER BY C.HD_TICKET_ID, C.ID)
FIRST_ACTION ON (FIRST_ACTION.HD_TICKET_ID = T.ID)
WHERE T.HD_QUEUE_ID IN (1) /*add queue numbers here*/
GROUP BY OWNER
Comments:
-
In case anyone looked at this answer earlier, I figured out a way to avoid having to list the owners' usernames in the FIRST_ACTION inline view. I feel a bit silly, since I already worked out the solution in the main query - funny how thinking about things on the drive home helps!
John - jverbosk 11 years ago
Posted by:
jverbosk
11 years ago
OK, had to figure out how to target the different timestamps (HD_TICKET_CHANGE.TIMESTAMP) and then just the first and second instances for each ticket (this is what the OPENED and FIRST_ACTION inline views accomplish). From there it was a matter of getting them swapped in for the other query's OPENED and CLOSED columns and some adjusting.
Just FYI, unlike the other query, you'll need to adjust the start/end dates 4 times (in the inline views) - I restricted here as the query will run *much* faster than if I let the views parse out the timestamp values for *all* of the tickets four times and *then* filtered the date range at the end. Bit of an inconvenience, but if someone else has a better approach I'm all ears! ^_^
Hope that helps!
John
_________________________________
SELECT USER.USER_NAME AS OWNER,
(CASE WHEN FIRST_ACTION.TIMESTAMP < DATE_ADD(IF(OPENED.TIMESTAMP = '0',
FIRST_ACTION.TIMESTAMP, OPENED.TIMESTAMP), INTERVAL 1 HOUR) THEN '0-1 hour'
WHEN FIRST_ACTION.TIMESTAMP < DATE_ADD(IF(OPENED.TIMESTAMP = '0',
FIRST_ACTION.TIMESTAMP, OPENED.TIMESTAMP), INTERVAL 24 HOUR) THEN '1-24 hours'
WHEN FIRST_ACTION.TIMESTAMP > DATE_ADD(IF(OPENED.TIMESTAMP='0',
FIRST_ACTION.TIMESTAMP, OPENED.TIMESTAMP), INTERVAL 24 HOUR) THEN '>24 hours'
ELSE 'Error' END) AS RESPONSE_GROUP,
COUNT(HD_TICKET.ID) AS NUMBER_OF_TICKETS,
CONCAT((SUM(TIME_TO_SEC(TIMEDIFF(FIRST_ACTION.TIMESTAMP,
IF(OPENED.TIMESTAMP = '0', FIRST_ACTION.TIMESTAMP, OPENED.TIMESTAMP))))
/ COUNT(HD_TICKET.id) DIV 86400),'d ',
TIME_FORMAT(SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(FIRST_ACTION.TIMESTAMP,
IF(OPENED.TIMESTAMP = '0', FIRST_ACTION.TIMESTAMP, OPENED.TIMESTAMP))))
/ COUNT(HD_TICKET.id) MOD 86400 ), '%kh %im %ss'))
AS AVG_TIME_TO_CLOSE
FROM HD_TICKET
LEFT JOIN USER ON (USER.ID = HD_TICKET.OWNER_ID)
JOIN
(SELECT C.ID, C.HD_TICKET_ID, C.TIMESTAMP
FROM HD_TICKET_CHANGE C
WHERE
(SELECT COUNT(*)
FROM HD_TICKET_CHANGE FILTER
WHERE FILTER.HD_TICKET_ID = C.HD_TICKET_ID
AND FILTER.ID <= C.ID) = 1
AND C.TIMESTAMP <> 0
AND C.TIMESTAMP > '2012-09-30' /*change the start date here*/
AND C.TIMESTAMP < '2012-10-31' /*change the end date here*/
ORDER BY C.HD_TICKET_ID, C.ID)
OPENED ON (OPENED.HD_TICKET_ID = HD_TICKET.ID)
JOIN
(SELECT C.ID, C.HD_TICKET_ID, C.TIMESTAMP
FROM HD_TICKET_CHANGE C
WHERE
(SELECT COUNT(*)
FROM HD_TICKET_CHANGE FILTER
WHERE FILTER.HD_TICKET_ID = C.HD_TICKET_ID
AND FILTER.ID <= C.ID) = 2
AND C.TIMESTAMP <> 0
AND C.TIMESTAMP > '2012-09-30' /*change the start date here*/
AND C.TIMESTAMP < '2012-10-31' /*change the end date here*/
ORDER BY C.HD_TICKET_ID, C.ID)
FIRST_ACTION ON (FIRST_ACTION.HD_TICKET_ID = HD_TICKET.ID)
WHERE HD_TICKET.HD_QUEUE_ID in (1) /*add queue numbers here*/
GROUP BY OWNER
UNION
SELECT USER.USER_NAME AS OWNER,
'ALL' AS RESPONSE_GROUP,
COUNT(HD_TICKET.ID) AS NUMBER_OF_TICKETS,
CONCAT((SUM(TIME_TO_SEC(TIMEDIFF(FIRST_ACTION.TIMESTAMP,
IF(OPENED.TIMESTAMP = '0', FIRST_ACTION.TIMESTAMP, OPENED.TIMESTAMP))))
/ COUNT(HD_TICKET.ID) DIV 86400),'d ',
TIME_FORMAT(SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(FIRST_ACTION.TIMESTAMP,
IF(OPENED.TIMESTAMP = '0', FIRST_ACTION.TIMESTAMP, OPENED.TIMESTAMP))))
/ COUNT(HD_TICKET.id) MOD 86400 ), '%kh %im %ss')) AS AVG_TIME_TO_CLOSE
FROM HD_TICKET
LEFT JOIN USER ON (USER.ID = HD_TICKET.OWNER_ID)
JOIN
(SELECT C.ID, C.HD_TICKET_ID, C.TIMESTAMP
FROM HD_TICKET_CHANGE C
WHERE
(SELECT COUNT(*)
FROM HD_TICKET_CHANGE FILTER
WHERE FILTER.HD_TICKET_ID = C.HD_TICKET_ID
AND FILTER.ID <= C.ID) = 1
AND C.TIMESTAMP <> 0
AND C.TIMESTAMP > '2012-09-30' /*change the start date here*/
AND C.TIMESTAMP < '2012-10-31' /*change the end date here*/
ORDER BY C.HD_TICKET_ID, C.ID)
OPENED ON (OPENED.HD_TICKET_ID = HD_TICKET.ID)
JOIN
(SELECT C.ID, C.HD_TICKET_ID, C.TIMESTAMP
FROM HD_TICKET_CHANGE C
WHERE
(SELECT COUNT(*)
FROM HD_TICKET_CHANGE FILTER
WHERE FILTER.HD_TICKET_ID = C.HD_TICKET_ID
AND FILTER.ID <= C.ID) = 2
AND C.TIMESTAMP <> 0
AND C.TIMESTAMP > '2012-09-30' /*change the start date here*/
AND C.TIMESTAMP < '2012-10-31' /*change the end date here*/
ORDER BY C.HD_TICKET_ID, C.ID)
FIRST_ACTION ON (FIRST_ACTION.HD_TICKET_ID = HD_TICKET.ID)
WHERE HD_TICKET.HD_QUEUE_ID IN (1) /*add queue numbers here*/
GROUP BY OWNER
Comments:
-
John,
This is superb and I greatly appreciate your efforts. That being said, this is not quite what I am looking for. Essentially, if I am understanding this correctly, what this query does is look at the time difference between when the ticket was created, and the first action on the ticket (in most cases, an owner being assigned). What I am trying to determine, is how quickly the owner is actually is responding to the submitter.
Is there any way to modify the query so that it looks at the creation time of the ticket and the first comment that was made by the owner (not just the first comment after creation which could be created by the submitter)? I realize that I am asking a lot of your generosity to help me out here, so if I am asking too much, I understand.
Again, thank you for your efforts thus far - it is greatly appreciated.
Vinnie - vmescall 11 years ago -
No problem, I really enjoy writing queries - wish I could do that all day, actually! ^_^
I understand your requirements a bit better now, so let me see what I can come up with.
John - jverbosk 11 years ago
