/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Run a report comparing Tickets Closed for the last two years per month

10/20/2020 170 views

We use KACE Systems Management Appliance and looking to compare this years tickets to last year and break up by month. I have tried the below report but that just gives the latest year and if I make it 24 months, it only appends the numbers to the months.

SELECT
COUNT(HD_TICKET.ID) AS NUMBER_OPENED,
date_format(HD_TICKET.CREATED, '%M') AS MONTH_OPENED
FROM
HD_TICKET
JOIN
HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
JOIN
HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
WHERE
((TIMESTAMP(HD_TICKET.CREATED) <= NOW()
AND TIMESTAMP(HD_TICKET.CREATED) >= DATE_SUB(NOW(), INTERVAL 12 MONTH)))
GROUP BY MONTH_OPENED
ORDER BY MONTH_OPENED

Answer Summary:
1 Comment   [ + ] Show comment

Comments

  • Can you explain how you think the report will look? What columns would be included and what data is in each column? Perhaps include a few mock rows of data as an example?
    • Honestly, I would like to keep it simple. I only want it from one queue. The "IT" Queue and basically, the same as the one about but with the year after it.
      So:
      Month / Year / # of closed tickets

      I have tried the one below and it breaks things down a lot more than I need but is ok. However, they will only go 12 months back also.

      select case
      when MONTH(T.CREATED) between 1 and 3 then '1st Quarter'
      when MONTH(T.CREATED) between 4 and 6 then '2nd Quarter'
      when MONTH(T.CREATED) between 7 and 9 then '3rd Quarter'
      when MONTH(T.CREATED) between 10 and 12 then '4th Quarter'
      end as QUARTER,
      C.NAME,
      COUNT(T.ID) as NUMBER_TICKETS
      from HD_TICKET T
      join HD_CATEGORY C on T.HD_CATEGORY_ID = C.ID
      where T.HD_QUEUE_ID = 1
      and YEAR(T.CREATED) = YEAR(CURTIME())
      group by QUARTER,
      T.HD_CATEGORY_ID
      order by QUARTER asc
  • This content is currently hidden from public view.
    Reason: Removed by member request For more information, visit our FAQ's.

Answer Chosen by the Author

1
SELECT concat(month(CREATED), "/", year(CREATED)) as "Month/Year",
COUNT(HD_TICKET.ID) as "Tickets Opened"
FROM HD_TICKET
WHERE HD_TICKET.HD_QUEUE_ID = 2
AND CREATED > date_sub(NOW(), INTERVAL 24 MONTH)
GROUP BY YEAR(CREATED),MONTH(CREATED)
ORDER BY YEAR(CREATED),MONTH(CREATED)

Be sure to change the Queue ID to match the ID for your IT queue.

Answered 10/22/2020 by: chucksteel
Red Belt

  • That's it! Thank you so much Chuck!
    Unfortunately, it looks like KACE must archive after a year or something. The numbers drop to double digits after a year. No way to pull that data too, or is that untouchable?

    9 6/2019 15
    10 7/2019 14
    11 8/2019 21
    12 9/2019 52
    13 10/2019 866
    14 11/2019 700
    15 12/2019 574
    16 1/2020 808
    • Never mind. I found the setting in the queue. It was set to 1 year. We have fixed that. Thank you Chuck for the reply. Works perfect.
 
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