/bundles/itninjaweb/img/Breadcrumb_cap_w.png

Hello,

I am looking for a Kace report that will show ALL OPEN TICKETS with "LAST CHANGE of them" & Time Since Last Change!


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 to trace and push them to check their tickets.

Looking to see "ALLĀ OPEN tickets" with Ticket ID, Queue Name, Category, Title, Submitter, Time open, Current Owner, LAST CHANGE & Time Since Last Change.

Thank you for your helps.


Answer Summary:
2 Comments   [ + ] Show comments

Comments

  • Try this:-

    SELECT HD.ID AS TICK,
    Q.NAME AS QUEUE,
    C.NAME AS CATEGORY_N,
    HD.TITLE AS TITLE,
    U.FULL_NAME AS SUBMITTER,
    O.FULL_NAME AS OWNER,
    HD.CREATED AS TIME_CREATED,
    CH.COMMENT AS CHANG,
    CH.TIMESTAMP AS LAST_UPDATE
    FROM HD_TICKET HD
    JOIN HD_QUEUE Q ON (Q.ID = HD.HD_QUEUE_ID)
    JOIN HD_CATEGORY C ON (C.ID = HD.HD_CATEGORY_ID)
    JOIN HD_STATUS S ON (S.ID = HD.HD_STATUS_ID)
    LEFT JOIN USER U ON (U.ID = HD.SUBMITTER_ID)
    LEFT JOIN USER O ON (U.ID = HD.OWNER_ID)
    JOIN HD_TICKET_CHANGE CH ON (CH.HD_TICKET_ID = HD.ID)
    WHERE S.NAME NOT LIKE '%CLOSED%'
    AND HD.MODIFIED = CH.TIMESTAMP
    GROUP BY HD.ID
    • Thank you, I run this comment, it shows all of tickets rather then JUST new and Open tickets, Also Owner of ticket is not shown. Looking to have "ALL NEW/OPEN tickets" with Ticket ID, Queue Name, Category, Title, Submitter, Time open, Current Owner, LAST CHANGE & Time Since Last Change.
      Thank you for your kind help :)
  • Slight change:-

    SELECT HD.ID AS TICK,
    Q.NAME AS QUEUE,
    C.NAME AS CATEGORY_N,
    HD.TITLE AS TITLE,
    U.FULL_NAME AS SUBMITTER,
    O.FULL_NAME AS OWNER,
    HD.CREATED AS TIME_CREATED,
    CH.COMMENT AS CHANG,
    CH.TIMESTAMP AS LAST_UPDATE
    FROM HD_TICKET HD
    JOIN HD_QUEUE Q ON (Q.ID = HD.HD_QUEUE_ID)
    JOIN HD_CATEGORY C ON (C.ID = HD.HD_CATEGORY_ID)
    JOIN HD_STATUS S ON (S.ID = HD.HD_STATUS_ID)
    LEFT JOIN USER U ON (U.ID = HD.SUBMITTER_ID)
    LEFT JOIN USER O ON (U.ID = HD.OWNER_ID)
    JOIN HD_TICKET_CHANGE CH ON (CH.HD_TICKET_ID = HD.ID)
    WHERE S.STATE NOT LIKE 'closed'
    AND HD.MODIFIED = CH.TIMESTAMP
    GROUP BY HD.ID

    Are you getting anything at all from the Owner Column?
    • Thank you so much, really appreciated for your time and attention to this!
      It just shows owner as "admin" and rest of them are blank on Owner column! Also we have about 200 open/new tickets but it shows just 32 tickets!
      Thank you and let me know if u need more information.

Answer Chosen by the Author

1
This report will show last change by the ticket owner and the time since that change:
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"
If you want the last change by anyone:
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.ID = (select MAX(ID) FROM HD_TICKET_CHANGE WHERE HD_TICKET_ID = T.ID)
WHERE T.TIME_CLOSED = "0000-00-00 00:00:00"

Answered 07/17/2018 by: chucksteel
Red Belt

  • Thank you so much :)

Share