/build/static/layout/Breadcrumb_cap_w.png

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 Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

0
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
Red Belt

  • Awesome, that did it! Thanks so much for your help chucksteel.
    • This content is currently hidden from public view.
      Reason: Removed by member request For more information, visit our FAQ's.
    • 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.
      • 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
      • 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?

All Answers

0
Thanks for the request for clarification question. I am looking for the feedback score not all of the text associated with the feedback. 
Answered 12/06/2016 by: solarissparc
Senior White Belt

  • A comma separated list or average?
    • Feedback for each ticket that was reopened. I assumed that it will be only one feedback score per ticket.
      • Right, but you also said grouped by owner, did you mean that you want the owner displayed in the report?
0
Yes I was thinking something like:

Ticket owner:
    1  .. . . ..
Ticket owner 2:
    1  .. . . ..
    2   .. . . ..
    3  .. . . ..
Answered 12/06/2016 by: solarissparc
Senior White Belt

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ