KACE report to show number of tickets opened and closed per month by queue
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 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.
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'
HD_TICKET T ON HD_QUEUE.ID = T.HD_QUEUE_ID
HD_CATEGORY ON (HD_CATEGORY.ID = T.HD_CATEGORY_ID)
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