Creating a KACE Report to show all queue departments current open and stalled tickets
Hey guys,
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.
-
Is there a separate queue for each department? - Druis 8 years ago
-
So each Department is a Queue. I have 15 Queue/departments. I saw a report a while back where I saw a list of the departments on the left and to the right it showed tickets created. I wanted the departments on the left and opened/stalled ticket count on the right. - Flashkickhero 8 years ago
-
if it needs to be 1 queue per report that is also fine. Probably better for privacy purposes but would also be nice for the CEO here to see all of them in one report. - Flashkickhero 8 years ago
-
This is great. I couldn't find anything like this through hours of searching. You are awesome! - Flashkickhero 8 years ago
Answers (2)
Top Answer
Hi,
Try this:
SELECT HD_QUEUE.NAME AS Q_NAME,
HD_STATUS.NAME AS STATS,
COUNT(HD_TICKET.ID) AS NO_TICK
FROM HD_TICKET
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
