KACE Product Support

Hello all,

For quite some time now, I have been using a custom query (that was generously developed by another user of this forum) to generate a report that show the average time taken by each technician to take action on a ticket after it is created/submitted. This query has worked flawlessly up until the K1000 was upgraded to the most recent server version.

The query will stil run without error, however, the average time data that it returns is zero. For example, where previously the query would report that Technician had an average response time of '0h 1d 39m 45s', all that is returned now is '0d 0h 0m 0s' for all technicians.

It is worth noting that if I run the query against all tickets specifying a date range prior to the server upgrade, the data returns as expected. I can only suspect then that something has changed in the way that the ticket data or timestamps are stored in this current version.

Provided below is a copy of the query - if any experts were inclined to take a look at it and sort out where the trouble might be, I would be exceptionally appreciative.

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 > '2013-01-01' /*change the start date here*/
 AND C.TIMESTAMP < '2013-01-01' /*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 > '2013-01-01' /*change the start date here*/
 AND C.TIMESTAMP < '2013-01-01' /*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 (2) /*add queue numbers here*/
GROUP BY OWNER