/build/static/layout/Breadcrumb_cap_w.png

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.


0 Comments   [ + ] Show comments

Answers (1)

Answer Summary:
Posted by: chucksteel 4 years ago
Red Belt
1

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 4 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 4 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 4 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 4 years ago
      • That got it, thanks! - tromo 4 years ago
 
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