Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.
Endpoint Management Community Blog

Helpdesk 6 Queue Member SQL Query

Created: 05 Apr 2010
dfnkt_'s picture
+1 1 Vote
Login to vote

This query will show you all the members for each of your queue's in HD 6. It will require a bit of effort on your part to figure out the ID numbers of your Queues (versus workers) but I think it is time well spent. I put this together in about 10 minutes so it won't take long.

You can find your queue ID numbers in one of two ways:

1.) Visit my former article on writing a query for worker/queue names and ID's.

2.) Run the following query:
                    USE Altiris_Incidents

                   SELECT
                      ID,
                      Name,
                      Email
                   FROM dbo.contact

In the above query, it is a little tedious to scroll through the list of workers and queues. I found that there were 2 ways to cut down the result set:

  • None of my queues had an NT_ID
  • Several of my contacts had no email set

By adding this logic to my query I had:
                    USE Altiris_Incidents

                   SELECT 
                      ID, 
                      Name, 
                      Email 
                   FROM dbo.contact
                   WHERE 
                       NT_ID = ''
                   AND
                       email != ''

That query gave me a much more manageable list to get my ID's.With your ID numbers written down, in notepad, or whatever you fancy we will continue to our real query.

USE Altiris_Incidents  
SELECT    
    name AS 'Queue Name',  
    email AS 'Members'  
FROM
    dbo.contact  
WHERE
     id IN ('ID','ID','ID')

You just need to place your ID numbers into the 'ID' placeholders above. A real example might be: id IN ('7','9','30','7431'). The result of this you need to head over to your Altiris 6 Console Reports tab and create a new report with your query pasted into it. When you run the query you should see a list of your Queues and their respective members. To take this further you could build a workflow that shows a dropdown of all of the Queue names and then displays their members or you could create a report that accepts a parameter and then looks at the corresponding queue.