/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Kace SQL Report to show all open tickets by week

09/09/2017 1423 views
Hello guys,

I want to have a report that shows how many open tickets we have had per week for the past 12 months. If I was looking at the data for the last week, it would have to show how many opens tickets we had even for just last week. This would include tickets that were created weeks before, but it would exclude tickets that were closed this week.

So far I have a SQL query for only this week. This only shows tickets that are not closed this week. Some of the tickets in the result were created weeks ago but they are not yet closed. 

SELECT 
 COUNT(HD_TICKET.ID) AS NUMBER_OPENED,
  Q.NAME
FROM
 HD_TICKET
 JOIN
 HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
 JOIN
 HD_QUEUE Q ON Q.ID = HD_TICKET.HD_QUEUE_ID
WHERE
Q.NAME = "North America" and HD_STATUS.NAME != "CLOSED" 

0 Comments   [ + ] Show comments

Comments


All Answers

0
I think you are looking for the impossible! If your question is correct, you want to know how many calls were in an open state last week, the week before etc? From a reporting perspective you would have needed to capture that figure at the time, much as we have to with our DASHboard. You can report how many calls were logged per week...calls with a log date in the date range, any status : and you can report how many calls were closed per week, calls that are closed and have a closed date within the date range. But the only way you have of getting close is to get all of the above figures and do the maths....

i.e. This morning we have 50 calls open
last week we logged 60 and closed 49, therefore our backlog has increased by the difference of 11 and last week was 61

etc etc  but it will not be an exact science if you are able to delete tickets, as that will throw out the numbers.


Answered 09/11/2017 by: Hobbsy
Red 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