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
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0
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

Answered 08/31/2015 by: chucksteel
Red Belt

  • 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
    • 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.
      • 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
      • 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.
      • 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!!
Please log in to comment
0
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.
Answered 09/09/2015 by: chucksteel
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity