KACE SQL Report Help
I need to pull a quarterly report and show the total numbers of tickets....broken down by each category and the sub categories.....
This is an example of the queue for hardware categories....Thanks for the help
I dont have any experience with SQL so any help would be great.....
Monthly report would also be ok.....
Community Chosen Answer
The following query returns for the previous three months, which is a quarter. You should theoretically be able to use the QUARTER function in MySQL to make the more elegant, but this should work:
select HD_CATEGORY.NAME, COUNT(HD_TICKET.ID) from HD_TICKET JOIN HD_CATEGORY on HD_TICKET.HD_CATEGORY_ID = HD_CATEGORY.ID Where HD_TICKET.TIME_CLOSED BETWEEN date_format(NOW() - INTERVAL 4 MONTH, '%Y-%m-01') AND last_day(NOW() - INTERVAL 1 MONTH) AND HD_TICKET.HD_QUEUE_ID = 2 GROUP BY HD_TICKET.HD_CATEGORY_ID
Be sure to change the HD_QUEUE_ID to match the queue that you are running the report for.