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:
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
-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:
| ||||||||||||||||||||||||||
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:
Please log in to answer
Posted by:
h2opolo25
9 years ago
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 9 years ago
-
Welcome, just noticed an error in the code.... there should be 2 colons in the second category:
Access::LAN Expired - h2opolo25 9 years ago
