Helpful Helpdesk Queries

I posted this a few weeks ago over on the Altiris forums and have finally gotten around to submitting it here.

This might be common sense or well known information to a lot of the more well practiced people here. I started in my current position the 1st of November and had zero experience with Altiris, so everything is a learning experience.

I have created some queries using the helpdesk guide and would like to share what I've learned in hopes of others who come after me can learn something or find a quick answer.

First of all, this information can be found in the Altiris helpdesk 6.0 SP5 guide which can be found here: Helpdesk SP5 Guide - Page 222-226

We can start with something very basic:

A query to find all tickets whose status is "open":

workitem_status_lookup_id = 300

Click to view.

You can substitute the following numbers for 300 to achieve varied results:

ID Value
100 Planned
200 Requested
300 Open
400 Resolved
500 Hold
600 Closed
-www.symantec.com/connect

Now, this is an ok query, but it wont find tickets which have been requested (100) or on hold (500). To improve on this, we will use:

workitem_status_lookup_id <> 600

This query is set to find any ticket with an ID less than or greater than 600 which gives you every single ticket that is NOT closed.

The next query will prompt the user for a number of days and will find tickets with an ID less than or equal to 300 (open):

workitem_status_lookup_id = 300 AND workitem_modified_on >= dateadd(d,-$prompt_number("Enter # of days:")$, getdate())

Next, a query to show open tickets by worker, this will prompt the user for all or part of a worker name and returns tickets whose status is NOT closed:

workitem_status_lookup_id <> 600 AND assigned_to_worker_name like '%' + $prompt_string("Enter all or part of a worker name")$ + '%'
ORDER by workitem_assigned_to_worker_id

And finally, a query which took me while to get the logic in sync with what I wanted it to find.

A query to see all tickets created by contact name that are NOT closed in the last N number of days:

workitem_status_lookup_id <= 300 AND
workitem_modified_on >= dateadd(d,-$prompt_number("Enter # of days:")$, getdate()) AND contact_name like '%' + $prompt_string("Enter all or part of a worker name:")$ + '%'
ORDER by contact_name DESC

This prompts for both a number of days and a contact name, this will find tickets that users create from the winuser console as well as tickets created by someone else as long as the contact is set properly.

Click to view.

Again, this might be basic knowledge but it's not something that I knew on day one. Hopefully it helps someone! If you see a mistake or know of a better way to achieve these results I'm 100% open to suggestions!