/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


Kace Report to show last time a ticket was updated by Owner

09/03/2015 2029 views

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.

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

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ