Kace Reporting for tickets two days or longer
I have seen many reports on IT Ninja, but I am looking for one that I hope someone can help me with.
I am looking to finish this report and report on tickets that are 2 days or older, and need help finishing this. Thanks everyone
SELECT HD_TICKET.ID, Q.NAME AS QUEUE_NAME, HD_CATEGORY.NAME AS CATEGORY, GROUP_CONCAT(DISTINCT HD_TICKET_CHANGE.COMMENT SEPARATOR '\n') AS HD_TICKET_CHANGE_COMMENT_GROUPED, S.FULL_NAME AS SUBMITTER_NAME, O.USER_NAME AS OWNER_USER_NAME, HD_PRIORITY.NAME AS PRIORITY, HD_STATUS.NAME AS STATUS_NAME, HD_TICKET.CREATED, HD_TICKET.TIME_OPENED FROM HD_TICKET JOIN HD_QUEUE Q ON Q.ID = HD_TICKET.HD_QUEUE_ID JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID) LEFT JOIN HD_TICKET_CHANGE ON (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID) LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID) JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 41) AND ((HD_STATUS.NAME = 'New') AND (HD_STATUS.NAME = 'Open') AND ((Q.NAME = '1 - North America') OR (Q.NAME = 'IT Network/Telecom') OR (Q.NAME = 'IT Infrastructure Systems') OR (Q.NAME = 'IT End User Computing'))) GROUP BY HD_TICKET.ID ORDER BY ID.
Answers (1)
SELECT
HD_TICKET.ID,
Q.NAME AS QUEUE_NAME,
HD_CATEGORY.NAME AS CATEGORY,
GROUP_CONCAT(DISTINCT HD_TICKET_CHANGE.COMMENT
SEPARATOR '
') AS HD_TICKET_CHANGE_COMMENT_GROUPED,
S.FULL_NAME AS SUBMITTER_NAME,
O.USER_NAME AS OWNER_USER_NAME,
HD_PRIORITY.NAME AS PRIORITY,
HD_STATUS.NAME AS STATUS_NAME,
HD_TICKET.CREATED,
HD_TICKET.TIME_OPENED
FROM
HD_TICKET
JOIN
HD_QUEUE Q ON Q.ID = HD_TICKET.HD_QUEUE_ID
JOIN
HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
LEFT JOIN
HD_TICKET_CHANGE ON (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)
LEFT JOIN
USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
LEFT JOIN
USER O ON (O.ID = HD_TICKET.OWNER_ID)
JOIN
HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID)
JOIN
HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
WHERE
((HD_STATUS.NAME = 'New')
OR (HD_STATUS.NAME = 'Open'))
AND ((Q.ID = 41)
OR (Q.NAME = '1 - North America')
OR (Q.NAME = 'IT Network/Telecom')
OR (Q.NAME = 'IT Infrastructure Systems')
OR (Q.NAME = 'IT End User Computing'))
and HD_TICKET.CREATED < date_sub(now(), interval 2 day)
GROUP BY HD_TICKET.ID
ORDER BY ID
Comments:
-
Grayematter,
Thank you for your prompt response. I feel that I am very close. Is there a way to take the Time opened column, and display in overall elapsed time from creation.
Forexample:
Ticket Datetime Days_old
34581 2014-07-27 10.3
Where Days Old is displayed in time - blueglo3506 9 years ago-
Try adding one of these to the SELECT part of the query. You may want CREATED instead of TIME_OPENED, depending on the exact measure you seek.
datediff(NOW(), HD_TICKET.TIME_OPENED) as elapsed1,
date_format(sec_to_time( timestampdiff(second, HD_TICKET.TIME_OPENED, NOW())), '%m-%d %H:%i') as elapsed2, - grayematter 9 years ago-
Thank you for your help, as this has helped my tremendously. - blueglo3506 9 years ago
