Difference between revisions of "Stored Queries"

Revision as of 21:43, 13 January 2008

The possibilities for stored queries are limitless. So instead of listing all the possible combinations that can be used in authoring queries, this article will act as an archive of basic syntax you can use. You can then piece-meal the strings together using Boolean arguments.

This article is catered to Service Desk r6.0, however many of the queries can be used for older released.

This listing is in no way complete and I encourage you to submit new ideas via a Private Message to Gregg so that they can be added to the contents of this article. Please do not use the comments feature as that does not update the article itself.

All tickets

Active: active = 1 Inactive: active = 0 Assigned: assignee is not null Unassigned: assignee is null Assignee is the person logged in: assignee = @cnt.id Assignee is a specific contact: assignee = nnnnnn Callback Today: call_back_flag=1 AND call_back_date < EndAtTime(\'TODO_TODAY\') Category is specific:category.id = nnnnnn Group is specific: group=nnnnnn Member of the Group is the person logged in: group.group_list.member IN (@cnt.id) Created by the person logged on: log_agent = @cnt.id Priority is 1: priority = 5 Priority is 5: priority = 1 Request Area is specific:category.id = nnnnnn Resolved: active = 1 and status = \'RE\' SLA has been violated: sla_violation != 0 SLA has not been violated: sla_violation = 0 Created in the past 60 minutes: open_date > StartAtTime(\'PAST_HOUR\') Created in the past 24 hours: open_date > StartAtTime(\'PAST_DAY\') Created in the past 7 days: open_date > StartAtTime(\'PAST_WEEK\') Created in the past 30 days: open_date > StartAtTime(\'PAST_MONTH\') Created in the past 365 days: open_date > StartAtTime(\'PAST_YEAR\') Created last hour: open_date > StartAtTime(\'LAST_HOUR\') Created last month: open_date > StartAtTime(\'LAST_MONTH\') Created last year: open_date > StartAtTime(\'LAST_YEAR\') Created this hour: open_date > StartAtTime(\'THIS_HOUR\') Created this month: open_date > StartAtTime(\'THIS_MONTH\') Created this month last year: open_date > StartAtTime(\'THIS_MONTH_LAST_YEAR\') Created today: open_date > StartAtTime(\'TODAY\') Created today after noon: open_date > StartAtTime(\'AFTER_NOON\') Created today before noon: open_date > StartAtTime(\'BEFORE_NOON\') Created yesterday: open_date > StartAtTime(\'YESTERDAY\')

Requests, Incidents, Problems

Affected end user is the person logged in: customer = @cnt.id Attached CO: (change IS NOT NULL AND change != 0)

Change Orders & Issues

Affected end user is the person logged in: affected_contact = @cnt.id Requester is the person logged in: requestor = @cnt.id

Workflow & Issue Workflow

Assignee is logged in person: assignee = @cnt.id Assignee is a specific Group: assignee.last_name = \'xxxxxxxxxx\' note: xxxxxxxxxx references the group name Future: status.allow_task_update = 0 AND status = \'WAIT\' Waiting: status = \'WAIT\' Pending: status = \'PEND\' To do today: status = \'PEND\' AND est_completion_date < EndAtTime(\'TODO_TODAY\') Cannot be edited: status.allow_task_update = 0 Is not complete:status.task_complete = 0 Is not next in the workflow:status.do_next_task = 0

Contacts

All Analysts: type = 2307 Analyst is available: available = 1 Analyst is unavailable: available=0 OR available IS NULL Contact is active: delete_flag = 1 Contact is inactive: delete_flag = 0 Contact's location is the same as the logged in person's location: location = @cnt.location

Configuration Items

Knowledge