/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Report shows closed tickets

07/06/2016 446 views
Hello All
I would like to create a report that shows the total amount of closed for each month for the past year, for example.

october 400
november 425
december 410

we currently run this script to show the amount of tickets closed for the previous day

SELECT T.ID, T.CREATED, T.TIME_CLOSED, OWNER.FULL_NAME as "Owner", 
CLOSED.TIMESTAMP, CLOSED.DESCRIPTION,
CLOSER.FULL_NAME as "Closer"
FROM ORG1.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 USER OWNER ON OWNER.ID = T.OWNER_ID
LEFT JOIN USER CLOSER ON CLOSER.ID = CLOSED.USER_ID
WHERE T.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 24 HOUR)
AND (CLOSER.USER_NAME = "jwinn" or CLOSER.USER_NAME = "twinn")

Answer Summary:
1 Comment   [ + ] Show comment

Comments

  • I am trying to edit grayematter's code in mysql workbench but I am getting an error for user R1 Error Code: 1142. SELECT command denied to user 'R1'@'nrsranch-it01.goropin.local' for table 'HD_TICKET_CHANGE'

    I have changed the password for R1 to verify it is the correct password.
    any Ideas

Answer Chosen by the Author

0
Thank you very much, that script works great! Is there a way to only show closed tickets from 2 specified users?
Answered 07/07/2016 by: joshuaer
Senior Yellow Belt

  • Add
    AND (CLOSER.USER_NAME = "jwinn" or CLOSER.USER_NAME = "twinn")
    just before the "GROUP BY" line.

All Answers

0

This should give you what you need.  If you want to limit the counts to closers "jwinn" or "twinn", just add that line from your original query above.

SELECT 
    YEAR(T.TIME_CLOSED) AS 'Closed Year',
    MONTH(T.TIME_CLOSED) AS 'Closed Month',
    COUNT(T.ID) AS 'Closed Count'
FROM
    ORG1.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
    USER OWNER ON OWNER.ID = T.OWNER_ID
        LEFT JOIN
    USER CLOSER ON CLOSER.ID = CLOSED.USER_ID
WHERE
    T.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 1 YEAR)
GROUP BY YEAR(T.TIME_CLOSED) , MONTH(T.TIME_CLOSED)
Answered 07/07/2016 by: grayematter
5th Degree Black 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