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

Helpful Helpdesk Queries

Created: 24 Jan 2008 • Updated: 24 Jan 2008 • 5 comments
dfnkt_'s picture
+1 1 Vote
Login to vote

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

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

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.

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!

Comments 5 CommentsJump to latest comment

jjesse's picture

Thanks for this, nice info to get me started

Jonathan Jesse Practice Principal ITS Partners

Login to vote
BIvey's picture

Evan if this is basic knowledge. Basic knowledge can be very useful to others at times.

Login to vote
dfnkt_'s picture

Glad I could help, hope that it gets everyone off to a good start.

Login to vote
JMoreno28's picture

Thanks for taking the time and helping explain things for some of us who are just starting off. I would hope to find more articles like this.

Login to vote
imi so ubu's picture

I am also glad that you shared the basics, especially in the helpdesk environment. Even though that is not my specific area, it could be one day and I found this to be very informative and easy to follow.

I am still seeking more knowledge and growth too. Please advise if you find more info that may help beginners.

Login to vote