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.
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

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
Answered 07/14/2016 by: grayematter
Fourth Degree Black Belt

Please log in to comment
Answer this question or Comment on this question for clarity