Generate reports for tickets with last comment "not within" 5 Days
So, I am new to my department and have had very little experience with KACE as of yet. I have 0 SQL experience and have been working through trying to make different ticket filters or reports.
I am revamping our tickets and prioritizing them. As of right now we are entering "P1" through "P9" for priority. We are starting to use the priority column but most of our tickets just have P# in the summary. My goal is to implement a report with time frames for last comment. For example if there are any P2's with no comment within the last 5 days then it is shown on the report. I don't even need the comment shown just standard ticket information. I attempting to get an idea of how to put one together to then substitute the different time frames and "P#", so I can customize the report based on priority.
I was attempting to use the "Modified" category but worker comments do not update the "Modified" date. Also, I want to avoid the escalation emails unless I can make them send out ONLY if a comment has not been made within the time frame. I do not want to have escalation emails go out for everything as we have a backlog and this would generate a lot of emails if there was no rule on a last comment time frame.
2 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
6 years ago
Top Answer
I have a similar report in my GitHub repository that is based on ticket changes:
SELECT T.ID, T.TITLE, T.CREATED, LAST_CHANGE.TIMESTAMP, LAST_CHANGE.DESCRIPTION, T.TIME_CLOSED
FROM ORG1.HD_TICKET T
JOIN HD_TICKET_CHANGE LAST_CHANGE ON LAST_CHANGE.HD_TICKET_ID = T.ID
and LAST_CHANGE.ID=(select MAX(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = T.ID)
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.IDleft join HD_PRIORITY on HD_PRIORITY.ID = T.HD_PRIORITY_ID
WHERE
HD_PRIORITY.NAME = "P4"and HD_STATUS.STATE != "closed"
and LAST_CHANGE.TIMESTAMP < NOW() - INTERVAL 4 HOUR
By changing the INTERVAL 4 HOUR to INTERVAL 5 DAY you should get the ticket you are looking for. Note that this report will include all queues. If you want to limit to one particular queue, then add:
and T.HD_QUEUE_ID = 1
at the end of the query and only tickets in the queue with ID of 1 (the original queue on the K1000) will be included.
Based on what you want to ultimately report, I would use something like this:
SELECT HD_QUEUE.NAME, HD_PRIORITY.NAME, COUNT(T.ID)
FROM ORG1.HD_TICKET T
JOIN HD_TICKET_CHANGE LAST_CHANGE ON LAST_CHANGE.HD_TICKET_ID = T.ID
and LAST_CHANGE.ID=(select MAX(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = T.ID)
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
left join HD_PRIORITY on HD_PRIORITY.ID = T.HD_PRIORITY_ID
left join HD_QUEUE on T.HD_QUEUE_ID = HD_QUEUE.ID
WHERE
HD_STATUS.STATE != "closed"
and LAST_CHANGE.TIMESTAMP < NOW() - INTERVAL 4 HOUR
GROUP BY HD_PRIORITY.ID
ORDER BY HD_QUEUE.NAME, HD_PRIORITY.NAME
This will give you a breakdown of all open tickets in all queues that have not been updated in the given interval grouped by their priority.
Comments:
-
Between both answers I was able to get this exactly how I wanted! Thanks to both of you! - tjmollohan 6 years ago
SELECT HD_TICKET.ID,
HD_TICKET.TITLE,
HD_TICKET.CREATED,
HD_TICKET_CHANGE.`COMMENT`,
HD_TICKET.MODIFIED AS T_MODIFIED,
HD_WORK.NOTE AS W_COMMENT,
HD_WORK.MODIFIED As W_MODIFIED
FROM HD_TICKET
LEFT JOIN HD_WORK ON (HD_WORK.HD_TICKET_ID = HD_TICKET.ID)
LEFT JOIN HD_TICKET_CHANGE ON (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)
WHERE DATEDIFF(NOW(), HD_WORK.MODIFIED) >= 5
If you want to refine the search by the P# values in the Summary add this to the end
AND (HD_TICKET.TITLE LIKE 'P2%') - Druis 6 years ago