ALL OPEN TICKETS with "LAST CHANGE of them" & Time Since Last Change!
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.
2 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
6 years ago
Top Answer
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"
Comments:
-
Thank you so much :) - Mett 6 years ago
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 - Druis 6 years ago
Thank you for your kind help :) - Mett 6 years ago
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? - Druis 6 years ago
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. - Mett 6 years ago