KACE Product Support Question
KACE - getting details on reopened tickets
02/19/2016 1038 views
I'm working on a report to show how many times a ticket has been reopened. Ideally, I would be able to see an open and close date for each open and close on the ticket, and from there I could generate the report. At a minimum, being able to report the number of times a given ticket has been reopened would be helpful. Please let me know if you have any solutions for this!
Answer Chosen by the Author
Please log in to answer
I came up with this report:
SELECT T.ID, COUNT(DISTINCT(CLOSED.ID)) AS TIMESCLOSED, COUNT(DISTINCT(REOPENED.ID)) AS TIMESREOPENED, T.TITLE, T.CREATED,
GROUP_CONCAT(DISTINCT(REOPENED.TIMESTAMP)) AS WHENREOPENED,
GROUP_CONCAT(DISTINCT(CLOSED.TIMESTAMP)) AS WHENCLOSED
FROM HD_TICKET T
LEFT JOIN HD_TICKET_CHANGE CLOSED on CLOSED.HD_TICKET_ID = T.ID and CLOSED.DESCRIPTION like '%Changed ticket Status from%to "Closed"%'
LEFT JOIN HD_TICKET_CHANGE REOPENED on REOPENED.HD_TICKET_ID = T.ID and REOPENED.DESCRIPTION like '%Changed ticket Status from "Closed" to%'
GROUP BY T.ID
HAVING TIMESCLOSED > 1
ORDER BY TIMESCLOSED DESC
Answered 02/22/2016 by: chucksteel