KACE Service Desk Report with Queue Name and Category Information
I am trying to modify a report I found on this site so that it includes the name of the queue where the ticket resides and, ideally, the Category that has been assigned to it. I think I have the right SELECT statement with:
T.HD_QUEUE_ID AS 'Queue'
But I'm having trouble with the JOIN statement. I tried this:
JOIN HD_QUEUE Q ON (T.HD_QUEUE_ID = Q.ID AND Q.NAME ='Queue Name')
But it's not returning any results.
Also, if someone could provide the same statements for the Category field, that would be helpful.
Thank you.
T.HD_QUEUE_ID AS 'Queue'
But I'm having trouble with the JOIN statement. I tried this:
JOIN HD_QUEUE Q ON (T.HD_QUEUE_ID = Q.ID AND Q.NAME ='Queue Name')
But it's not returning any results.
Also, if someone could provide the same statements for the Category field, that would be helpful.
Thank you.
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
7 years ago
Top Answer
Don't include the AND Q.NAME = 'Queue Name' in the join statement, just use the ID to match.
The category join is
JOIN HD_CATEGORY C on T.HD_CATEGORY_ID = C.ID
You can then select the category name using C.NAME
Comments:
-
Thanks chucksteel, that gets me a bit closer. I removed AND Q.NAME = 'Queue Name' and finally got some results. However, in the Queue column, all that's listed are the Q.IDs (3, 4, 11, etc.). I really don't care about the Q.ID being displayed in the report, but would rather have the actual Q.NAME, such as "IT Helpdesk."
BTW, I tried AND Q.NAME = 'IT Helpdesk' and it narrowed the results down to Q.ID 3, but that's not really what I want for this report.
Category statements worked perfectly! - tromo 7 years ago-
I missed that you are selecting T.HD_QUEUE_ID as 'Queue', the selects the queue ID. Change that to Q.NAME as 'Queue' to select the queue's name. - chucksteel 7 years ago
-
Something's still not quite right. Just to simplify things, I created a report that ONLY shows Queue name, but I'm still not getting any results. Here's the full report:
SELECT Q.NAME AS 'Queue' FROM HD_TICKET T
JOIN HD_QUEUE Q ON (T.HD_QUEUE_ID = Q.NAME) - tromo 7 years ago -
The query should be SELECT Q.NAME AS 'Queue' FROM HD_TICKET T
JOIN HD_QUEUE Q ON (T.HD_QUEUE_ID = Q.ID)
You are making a relationship between the two tables based on something that they have in common. In this case the HD_QUEUE_ID column in the HD_TICKET table matches the ID column in the HD_QUEUE table. - chucksteel 7 years ago -
That got it, thanks! - tromo 7 years ago
