/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Creating auotmated Weekly Reports from specific queue

12/09/2015 793 views
hello, 

Can someone please help me
 i am trying to create a weekly report using SQL. i will like it to include Date created, Ticket ID, Category, Status,  time closed, and Owner

any assistance will be much appreciated 


Answer Summary:
1 Comment   [ + ] Show comment

Comments

  • Chuck I tried that but there was no option to pull automated reports from the last 7 days

Answer Chosen by the Author

1
I forgot that the wizard doesn't allow for those types of date queries.

This query will find tickets closed in the past seven days:
SELECT T.CREATED, T.ID, T.TIME_CLOSED, CAT.NAME as Category, T.TIME_CLOSED, OWNER.FULL_NAME
FROM ORG1.HD_TICKET T
JOIN HD_CATEGORY CAT ON CAT.ID = T.HD_CATEGORY_ID
JOIN HD_STATUS S ON S.ID = T.HD_STATUS_ID
JOIN USER OWNER ON OWNER.ID = T.OWNER_ID
WHERE TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 7 DAY)
AND T.HD_QUEUE_ID = 2
Change the T.HD_QUEUE_ID = 2 to match the queue that you want to report.

For tickets opened in the past seven days:
SELECT T.CREATED, T.ID, T.TIME_CLOSED, CAT.NAME as Category, T.TIME_CLOSED, OWNER.FULL_NAME
FROM ORG1.HD_TICKET T
JOIN HD_CATEGORY CAT ON CAT.ID = T.HD_CATEGORY_ID
JOIN HD_STATUS S ON S.ID = T.HD_STATUS_ID
JOIN USER OWNER ON OWNER.ID = T.OWNER_ID
WHERE T.CREATED > DATE_SUB(NOW(), INTERVAL 7 DAY)
AND T.HD_QUEUE_ID = 2


Answered 12/11/2015 by: chucksteel
Red Belt

  • thank you it worked!!. How can add total # of tickets created?
    • That would be a different kind of report. This will show you the number of tickets opened in the past seven days:

      SELECT Q.NAME, COUNT(T.ID) AS "Count"
      FROM ORG1.HD_TICKET T

      JOIN HD_QUEUE Q ON Q.ID = T.HD_QUEUE_ID
      WHERE T.CREATED > DATE_SUB(NOW(), INTERVAL 7 DAY)
      GROUP BY Q.ID

All Answers

1
Have you tried creating the report with the wizard? You aren't asking for anything that the wizard shouldn't be able to produce. Once you have the report created you can schedule it to be emailed once a week.
Answered 12/10/2015 by: chucksteel
Red Belt

  • Chuck I tried that but there was no option to pull automated reports from the last 7 days

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