Stored Queries
From SDU
To make corrections or additions to this article, select the edit tab above.
To discuss or ask questions about this article, select the discussion tab above.
To discuss or ask questions about this article, select the discussion tab above.
Contents
Overview
Stored Queries are used exclusively for the Scoreboard. Stored Queries have no capabilities or functionality outside of the Scoreboard.
When defining Stored Query you must prefix all single quotation marks (') with backslash (\) character.
To simply check Stored Query you can fill it into the Additional Search Argument field in the Search window. But you must fullfill these rules:
- Select proper base type (Contact, Request, Change...)
- Remove backslash (\) characters
- Change all variables (ie. @cnt.id) with correct values (@cnt is object with logged in user)
Examples
Requests, Incidents, Problems, Change Orders & Issues
| Description | Code | Notes |
|---|---|---|
| Active | active = 1 | |
| Inactive | active = 0 | |
| Has Assignee | assignee is not null | |
| No Assignee | assignee is null | |
| Assignee is the person logged in | assignee = @cnt.id | |
| Affected End User is the person logged in | customer = @cnt.id | Requests, Incidents & Problems only |
| Affected End User's Location | customer.location = U\'A26C98F436C43149A700CC0ED3BA74E5\' | Requests, Incidents & Problems only |
| Affected End User's Site | customer.location.site = 400001 | Requests, Incidents & Problems only |
| Affected End User's Location is the same as the person logged in | customer.location = @cnt.location | Requests, Incidents & Problems only |
| Affected End User's Site is the same as the person logged in | customer.location.site = @cnt.location.site | Requests, Incidents & Problems only |
| Affected End User is the person logged in | customer = @cnt.id | Requests, Incidents & Problems only |
| Attached CO | (change IS NOT NULL AND change != 0) | Requests, Incidents & Problems only |
| Affected End User is the person logged in | affected_contact = @cnt.id | Applies to Change Orders and Issues only |
| Affected End User's Location | affected_contact.location = U\'A26C98F436C43149A700CC0ED3BA74E5\' | Applies to Change Orders and Issues only |
| Affected End User's Site | affected_contact.location.site = 400001 | Applies to Change Orders and Issues only |
| Affected End User's Location is the same as the person logged in | affected_contact.location = @cnt.location | Applies to Change Orders and Issues only |
| Affected End User's Site is the same as the person logged in | affected_contact.location.site = @cnt.location.site | Applies to Change Orders and Issues only |
| Requester is the person logged in | requestor = @cnt.id | Applies to Change Orders and Issues only |
| Callback Today | call_back_flag=1 AND call_back_date < EndAtTime(\'TODO_TODAY\') | |
| Category or Request/Incident/Problem Area is specific | category.id = nnnnnn | |
| Group is specific | group=U\'7D15BCD6D0B7E8468E58EA600E61F484\' | For pre-r11 releases, just reference the id. |
| 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 | References the enum value not the sym value. |
| Priority is 5 | priority = 1 | References the enum value not the sym value. |
| 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 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\') AND open_date < EndAtTime(\'YESTERDAY\') | |
| Created yesterday and today | open_date > StartAtTime(\'YESTERDAY\') |
Workflow & Issue Workflow
| Description | Code | Notes |
|---|---|---|
| Assignee is logged in person | assignee = @cnt.id | |
| Assignee is a specific Group | assignee=U\'7D15BCD6D0B7E8468E58EA600E61F484\' | |
| Logged in person is a member of the Group set as the Assignee | assignee.[group]group_list.member IN (@cnt.id) | |
| Group is specific | group=U\'7D15BCD6D0B7E8468E58EA600E61F484\' | |
| 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
| Description | Code | Notes |
|---|---|---|
| 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 | |
| Members of concrete group | id.[member]group_list.group IN (U\'0969194BE95B974F9535687570A8E5B1\') | U\'0969194BE95B974F9535687570A8E5B1\' is an contact_uuid attribute of the group |
