KACE report to show number of tickets opened and closed per month by queue
Hello,
The CIO would like a report from KACE that shows how many tickets are opened and closed for each queue per month, I did a lot of searching on ITNinja but couldn't find exactly what I was looking for, and unfortunately I'm too rusty on my SQL to be able to do this on my own. I don't mind having a different report/query for each queue because I think that would simplify things, but what I'm looking for is a report that will tell me how many tickets were opened, and how many were closed each month for the past year.
Thank you everyone in advance.
The CIO would like a report from KACE that shows how many tickets are opened and closed for each queue per month, I did a lot of searching on ITNinja but couldn't find exactly what I was looking for, and unfortunately I'm too rusty on my SQL to be able to do this on my own. I don't mind having a different report/query for each queue because I think that would simplify things, but what I'm looking for is a report that will tell me how many tickets were opened, and how many were closed each month for the past year.
Thank you everyone in advance.
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
grayematter
8 years ago
The query below should get you started. It pulls all tickets from all queues with no date restrictions. You can add a WHERE clause to limit if you like, but filtering in Excel is easy enough. Open the output in Excel and create a pivot table to aggregate and filter the data.
SELECT
DATE_FORMAT(T.CREATED, '%Y') AS Created_Year,
DATE_FORMAT(T.CREATED, '%m') AS Created_Month,
DATE_FORMAT(T.TIME_CLOSED, '%Y') AS Closed_Year,
DATE_FORMAT(T.TIME_CLOSED, '%m') AS Closed_Month,
HD_QUEUE.NAME AS 'Queue',
HD_CATEGORY.NAME AS 'Category',
COUNT(T.id) AS 'Count'
FROM
HD_QUEUE
INNER JOIN
HD_TICKET T ON HD_QUEUE.ID = T.HD_QUEUE_ID
JOIN
HD_CATEGORY ON (HD_CATEGORY.ID = T.HD_CATEGORY_ID)
JOIN
HD_STATUS ON (HD_STATUS.ID = T.HD_STATUS_ID)
GROUP BY Created_Year , Created_Month , Closed_Year , Closed_Month , HD_QUEUE.NAME , HD_CATEGORY.NAME
ORDER BY Created_Year , Created_Month , Closed_Year , Closed_Month , HD_QUEUE.NAME , HD_CATEGORY.NAME
