Is it possible to report the calculation of collective 'Open' state time where the clock is not 'Stalled'?
Answers (2)
Interesting question.. there is a thread discussing Kace's lack of reports with standard practice service desk metrics. Here's the problem, Kace keeps ZERO metrics in the DB. Yep, you wont find an report that gives you ticket open time. The report Wizard doesnt have any math in it.. so you are on your own to write a sql script, use power-bi or the API to make your own metrics.
If Im wrong someone chime in.
What is it with you kiwi's and your service desks ?? ;o)
We still have some old SQL that works great and shows the time duration the ticket has been open and the stalled time when a ticket is moved into an on hold state. It is a little bit less flexible that the standard built in SLA stuff with regards to working time, but I think it can yield better results
Perhaps you'd like to get in touch and we can discuss?
In the meantime, keep the pressure up on KACE product management, it really is about time they took a cold hard look at this and maybe, just maybe, actually added some ne functionality that everyone wants......
Comments:
-
What's a kiwi? :) - barchetta 1 year ago
The only thing I have been able to do is what I've accomplished through the below SQL:
SELECT
T.ID,
TITLE,
T.CREATED,
/* TIME_OPENED,
format((time_to_sec(timediff(TIME_OPENED, T.CREATED)))/3600.0,2) as TIME2OPEN, */
DUE_DATE,
TIME_CLOSED,
format((time_to_sec(timediff(TIME_CLOSED, T.CREATED)))/3600.0,2) as TTL_AGE_IN_HOURS,
format((time_to_sec(timediff(TIME_CLOSED, DUE_DATE)))/3600.0,2) as TTL_TIMEPAST_DUEDATE,
/* SLA_NOTIFIED, */
P.NAME AS PRIORITY,
I.NAME AS IMPACT,
O.FULL_NAME AS OWNER_NAME,
E.FULL_NAME AS SUBMITTER_NAME,
S.NAME AS STATUS,
Q.NAME AS QUEUE,
C.NAME AS CATEGORY
FROM
HD_TICKET T
JOIN
HD_STATUS S ON S.ID = HD_STATUS_ID
JOIN
HD_PRIORITY P ON P.ID = HD_PRIORITY_ID
JOIN
HD_IMPACT I ON I.ID = HD_IMPACT_ID
JOIN
USER O ON O.ID = OWNER_ID
JOIN
USER E ON E.ID = SUBMITTER_ID
JOIN
HD_QUEUE Q ON Q.ID = T.HD_QUEUE_ID
JOIN
HD_CATEGORY C ON C.ID = HD_CATEGORY_ID
WHERE
Q.NAME = 'IT Support'
AND SLA_NOTIFIED != '0000-00-00 00:00:00'
AND S.NAME = 'Closed'
AND DUE_DATE < TIME_CLOSED
AND ((DATE(T.CREATED) >= DATE_ADD(DATE_SUB(CURDATE(),
INTERVAL DAYOFYEAR(CURDATE()) - 1 DAY),
INTERVAL ((QUARTER(CURDATE()) - 1) * 3) - (3 * 1) MONTH)
AND DATE(T.CREATED) < DATE_ADD(DATE_SUB(CURDATE(),
INTERVAL DAYOFYEAR(CURDATE()) - 1 DAY),
INTERVAL ((QUARTER(CURDATE()) - 1) * 3) MONTH)))
ORDER BY T.ID DESC; - RyanTech 1 year ago