Kace Service Desk Report: Open Ticket Times Omitting any 'stalled' time.
Ninja's. I have a report I wrote in KACE that pulls data from our help desk queue. It's grouped by 'owner' totalling up all the tickets that owner completed for a given time frame. In addition, I have time to close which takes the ticket open time and close time and comes up with a number of days/hours. This works great, however our ticket times are huge due to not ommitting time when the ticket was in a stalled status.
Example. I have a ticket open on 10/30/14 at 1pm I respond to the customer with a question so I change the status to 'waiting on customer'. Customer responds a couple hours later so I change the ticket to agent working on and then complete it. I want my report to subtract that time when the ticket was waiting on customer.
Any way this is possible?!? Below is my code.
>>>>>
SELECT
count(HD_TICKET.ID) as Total_Tickets,
USER.FULL_NAME as OWNER_NAME,
HD_STATUS.NAME,
AVG(HD_TICKET.SATISFACTION_RATING) as Sat_Rating,
CONCAT((sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED))))/count(HD_TICKET.id)
div 86400),'d ',
TIME_FORMAT(SEC_TO_TIME(sum(TIME_TO_SEC(TIMEDIFF(HD_TICKET.TIME_CLOSED,IF(HD_TICKET.TIME_OPENED='0',HD_TICKET.TIME_CLOSED,HD_TICKET.TIME_OPENED))))/count(HD_TICKET.id) mod 86400 ), '%kh')) as AVG_TIME_TO_CLOSE
FROM HD_TICKET LEFT JOIN HD_TICKET_CHANGE ON (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID) JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID) JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) JOIN USER ON (USER.ID = HD_TICKET.OWNER_ID)
WHERE ((HD_TICKET.HD_QUEUE_ID = 1) and (TIME_CLOSED>'2014-09-15') and (TIME_CLOSED<'2014-10-15') and (HD_STATUS.NAME like '%closed%')) GROUP BY OWNER_NAME
1 Comment
[ + ] Show comment
-
I believe that in order to do this you would have to look at all of the ticket changes and find the timestamps that correlate to status changing to and from states that are "stalled". If you could make a total of all of those times then it would be possible to subtract that from the total time open. - chucksteel 9 years ago
Answers (0)
Please log in to answer
Be the first to answer this question
