/build/static/layout/Breadcrumb_cap_w.png

KACE report for 2 categories

Good afternoon! I was wondering if I might get some help creating a report that has the following characteristics:
-Collects the number of tickets for 2 categories - Access::LAN Locked & Access::LAN Expired
-Segments the count of the tickets by month created, where it goes back to the beginning of the year
-Has a total count of tickets at the bottom of the report
for instance:

1
72
Access::LAN ExpiredSeptember 2014
2
38
Access::LAN LockedSeptember 2014
3
24
Access::LAN ExpiredOctober 2014
4
58
Access::LAN Locked
October 2014













This report (provided by another user on this site, thanks!) is quite close to what I am looking for, but again, I'd like to be able to narrow it down by specific categories and have it go back a full year with each month showing the number of tickets for that category and have a total count at the bottom.

SELECT
COUNT(HD_TICKET.ID) AS NUMBER_TICKETS,
HD_CATEGORY.NAME AS CATEGORY,
date_format(HD_TICKET.CREATED, '%M %Y') AS DATE_CREATED
FROM
HD_TICKET
JOIN
HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
WHERE
(HD_TICKET.HD_QUEUE_ID = 1)
AND (TIMESTAMP(HD_TICKET.CREATED) BETWEEN date_format(NOW() - INTERVAL 1 MONTH, '%Y-%m-01')
AND last_day(NOW() - INTERVAL 1 MONTH))
GROUP BY DATE_CREATED , CATEGORY


0 Comments   [ + ] Show comments

Answers (1)

Answer Summary:
Posted by: h2opolo25 10 years ago
Red Belt
0
This should do it. I changed the interval of the created tickets from 1 month to 1 year and added filters to only look for the two categories you are interested in.


SELECT
COUNT(HD_TICKET.ID) AS NUMBER_TICKETS,
HD_CATEGORY.NAME AS CATEGORY,
date_format(HD_TICKET.CREATED, '%M %Y') AS DATE_CREATED
FROM
HD_TICKET
JOIN
HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
WHERE
(HD_TICKET.HD_QUEUE_ID = 1)
and HD_CATEGORY.NAME = ('Access::LAN Locked')
or HD_CATEGORY.NAME = ('Access:LAN Expired')
AND (TIMESTAMP(HD_TICKET.CREATED) BETWEEN date_format(NOW() - INTERVAL 1 YEAR, '%Y-%m-01')
AND last_day(NOW() - INTERVAL 1 MONTH))
GROUP BY DATE_CREATED , CATEGORY

Comments:
  • Thank you so very much! - annleacock 10 years ago
    • Welcome, just noticed an error in the code.... there should be 2 colons in the second category:
      Access::LAN Expired - h2opolo25 10 years ago
 
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