Video Screencast Help

Building a Basic Seadog / Landlubber ID Report

Created: 19 Sep 2008 • Updated: 19 Sep 2008 • 7 comments
dfnkt_'s picture
+2 2 Votes
Login to vote

Grab your flagon o' grog and hoist ye colors! I'm going to cover some easy reports you can create in the Notification Server that should be of use to at least a few people here. When you are writing queries for the helpdesk you often may need to know a contact ID or a worker/queue ID. I have written 2 reports that you can run from the NS that will give you a list of all the active workers/queues and their worker numbers, as well as the contacts.

The first thing you need to do is go to your NS Console and create a new report. To do this in console 6.0 you simply go to the reports tab, right click on the folder under "Reports" where you want the report to appear and select "New" and then "Report" from the right click menu. You can always move the report later from the same menu if you want to place it somewhere else.

A report builder dialog box will come up and present you with some options for building your new report. Give the report a name that makes sense to you; I named mine "Worker Names and ID's". You can give the report a description, this is useful for later identifying why the report was created and what it's for. Finally you need to select the "Enter SQL Directly" radio button and enter the following query:

USE Altiris_Incidents
SELECT
name as 'Worker Name',
id as 'ID Number'
FROM
dbo.worker
WHERE
status like 'a'
ORDER BY
name

You can click Test to make sure your query is working, it should display all your users/queues and show their worker ID number. Click finish once you're satisfied with it and it will create the report and you can then run the report anytime you need to lookup a worker/queue ID.

If you also want one for contacts this is very simple. Just change the line in the above query "dbo.worker" to "dbo.contact" so it would look like this:

USE Altiris_Incidents
SELECT
name as 'Contact Name',
id as 'ID Number'
FROM
dbo.contact
WHERE
status like 'a'
ORDER BY
name

The best example I can give of where this is useful is in writing an Incident Query in the Helpdesk that shows all open tickets for all the users at a particular office. You could immediately know what their worker ID is after running this report, instead of having to connect to the table and run the query by hand.

Comments 7 CommentsJump to latest comment

MBHarmon's picture

This is actually a great idea. I might suggest seting up some parameters to help filter your search criteria, because if you've got over a hundred active worker accounts in your helpdesk module, like I do, it could be quite frustrating.

This would be easy to do by modifying your where statement to something like

WHERE
status like 'a'
AND name like '%name%'

then just set the parameter to prompt before running the report.

- Matt

0
Login to vote
dfnkt_'s picture

Nice suggestion mate!

-4
Login to vote
nyquist's picture

This is very useful. I was just trying to work out how to do this last week. Previously we had been going into the SQL table to get the information but that does it a whole lot easier and whole lot safer!

+2
Login to vote
drew.ohara's picture

In my case I have hundreds of workers (people) and queues so I have ordered the results different to display people workers together and queue workers together. To keep all the workers and queues seperate you can use the SQL like this:

SELECT
name as 'Worker Name',
id as 'ID Number'
FROM
dbo.worker
WHERE
status like 'a'
ORDER BY
is_virtual, name ASC

the only difference is in the 'Order By'. I added the field 'is_virtual' before 'name'. The is_virtual field displays whether the worker is a Queue Worker (1) or a Person Worker (0).

+4
Login to vote
aaron_t's picture

This is exactly what I was looking for.

Thank you very much!

+2
Login to vote
drew.ohara's picture

Glad I could help.

+2
Login to vote
mboggs's picture

You can also go to Workers, List Workers, right-click and Copy Table and paste to Excel. There is a hidden column that displays the ID. Of course you get ALL workers, but once in Excel you can manipulate it however you need to. Works for queues and contacts too.

-2
Login to vote