/build/static/layout/Breadcrumb_cap_w.png

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.


4 Comments   [ + ] Show comments
  • Is there a separate queue for each department? - Druis 4 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 4 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 4 years ago
  • This is great. I couldn't find anything like this through hours of searching. You are awesome! - Flashkickhero 4 years ago

Answers (2)

Answer Summary:
Posted by: Druis 4 years ago
Third Degree Green Belt
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





Posted by: Spicy2312 4 years ago
White Belt
0
The "View by" option to the top of the screen allows you to filter through stalled tickets, opened tickets, etc

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ