Kace Report to show last time a ticket was updated by Owner
Hello,
I am looking for a Kace report that will show the last time a ticket has been modified by the owner. We have a case where we feel the owners are not updating their tickets, and would like to know which ones have not bee updated and how long since they did.
Looking for ID, Queue Name, Category, Title, Submitter, Owner_User_Name, Status Name Created Time open last Modfied time in DaysHoursMinutesSeconds base on timestamp.
Thank you
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
chucksteel
8 years ago
I believe this report will work for you:
SELECT T.ID, Q.NAME, CAT.NAME, T.TITLE, S.FULL_NAME, O.USER_NAME, STATUS.NAME, T.CREATED, TC.TIMESTAMP as "Last Change",
CONCAT(
FLOOR(HOUR(TIMEDIFF(NOW(), TC.TIMESTAMP)) / 24), ' days, ',
MOD(HOUR(TIMEDIFF(NOW(), TC.TIMESTAMP)), 24), ' hours, ',
MINUTE(TIMEDIFF(NOW(), TC.TIMESTAMP)), ' minutes, ',
SECOND(TIMEDIFF(NOW(), TC.TIMESTAMP)), ' seconds')
AS "Time Since Last Change"
FROM HD_TICKET T
JOIN HD_QUEUE Q on Q.ID = T.HD_QUEUE_ID
JOIN USER O on O.ID = T.OWNER_ID
JOIN USER S on S.ID = T.SUBMITTER_ID
JOIN HD_CATEGORY CAT on CAT.ID = T.HD_CATEGORY_ID
JOIN HD_STATUS STATUS on STATUS.ID = T.HD_STATUS_ID
JOIN HD_TICKET_CHANGE TC on TC.HD_TICKET_ID = T.ID and TC.USER_ID = T.OWNER_ID and TC.ID = (select MAX(ID) FROM HD_TICKET_CHANGE WHERE HD_TICKET_ID = T.ID)
WHERE T.TIME_CLOSED = "0000-00-00 00:00:00"
Note that if you have tickets that haven't been changed in over a year this won't report correctly (yes, we have a few of those in our system, sigh). This only includes open tickets although you didn't specify that in your question.
Comments:
-
This is great code. I ran this recently but I seem to have encountered unusual behavior on the report. For example, I'm missing a queue in the report. Would you happen to know what could cause the report to skip a queue? - tanichea 7 years ago
-
Does the queue in question have open tickets? Have those tickets been updated by their owner? - chucksteel 7 years ago
-
I see what happened. Only the Submitter entered comments in the ticket, not the Owner. - tanichea 7 years ago
