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.
count(HD_TICKET.ID) as Total_Tickets,
USER.FULL_NAME as OWNER_NAME,
AVG(HD_TICKET.SATISFACTION_RATING) as Sat_Rating,
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
There are no answers at this time