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

Comments

  • This content is currently hidden from public view.
    Reason: Removed by member request
    For more information, visit our FAQ's.
Please log in to comment

Answers

1
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.


Answered 09/04/2015 by: chucksteel
Red Belt

  • 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?
    • Does the queue in question have open tickets? Have those tickets been updated by their owner?
      • This content is currently hidden from public view.
        Reason: Removed by member request
        For more information, visit our FAQ's.
      • I see what happened. Only the Submitter entered comments in the ticket, not the Owner.
Please log in to comment
Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share