KACE - getting details on reopened tickets
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!
0 Comments
[ + ] Show comments
Answers (3)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
8 years ago
Top 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
Comments:
-
Awesome, that did it! Thanks so much for your help chucksteel. - mankerbrandt 8 years ago
-
Hello, I know this thread is pretty old I am attempting to make the following changes to this report:
- group this report by Owner of the ticket
- add the feedback on the ticket on this report.
- Restrict the report to the last 30 days.
Any help would be appreciated. - solarissparc 7 years ago-
I was able to get the query started, but I am having an issue with grouping by owner and restricting by 30 days.
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, ifnull((select FULL_NAME from USER where T.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME,T.CREATED as Created
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 - solarissparc 7 years ago -
If I understand correctly you want the number of tickets per owner that were re-opened? If it were grouped by owner then including feedback on individual tickets could result in a rather long text field, are you sure that is what you want? - chucksteel 7 years ago
Posted by:
solarissparc
7 years ago
Thanks for the request for clarification question. I am looking for the feedback score not all of the text associated with the feedback.
Comments:
-
A comma separated list or average? - chucksteel 7 years ago
-
Feedback for each ticket that was reopened. I assumed that it will be only one feedback score per ticket. - solarissparc 7 years ago
-
Right, but you also said grouped by owner, did you mean that you want the owner displayed in the report? - chucksteel 7 years ago
Posted by:
solarissparc
7 years ago
Yes I was thinking something like:
Ticket owner:
1 .. . . ..
Ticket owner 2:
1 .. . . ..
2 .. . . ..
3 .. . . ..
Comments:
-
When I use break by owner it all blends together. - solarissparc 7 years ago
-
Are you breaking on OWNER_NAME? - chucksteel 7 years ago
-
Yes I am - solarissparc 7 years ago
