Creating a KACE Report to show all queue departments current open and stalled tickets
I am a total amateur when using KACE and SQL. I was able to get MYSQL to see the tables that I want but im having issues compiling the tables to make a basic count report. Here is my goal and any advice or a script would be fantastic.
I want it to show all my departments which I have 14 in the company and I just want to see next to it how many tickets that have that are stalled or opened.
The company is huge on tickets that are currently opened outstanding. Getting a count per Department would be awesome.
SELECT HD_QUEUE.NAME AS Q_NAME,
HD_STATUS.NAME AS STATS,
COUNT(HD_TICKET.ID) AS NO_TICK
JOIN HD_QUEUE ON (HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID)
JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
WHERE HD_TICKET.HD_QUEUE_ID IN (1,2,3,4)
AND HD_STATUS.NAME not like 'Closed'
GROUP BY STATS
ORDER BY Q_NAME DESC
You'll need to change the Queue ID numbers to include each of your queues. If you are not sure the numbers of your queues, simply run the following query:-
SELECT ID, NAME FROM HD_QUEUE