Difference between revisions of "Stored Queries"

From SDU
Jump to: navigation, search
(→‎All tickets)
(→‎Requests, Incidents, Problems, Change Orders & Issues)
 
(47 intermediate revisions by 10 users not shown)
Line 1: Line 1:
−
[[Category:Miscellaneous]][[Category:Definitions]]
+
[[Category:Configuration]]
−
Stored Queries are used exclusively for the [[Scoreboard]]. Stored Queries have no capabilities or functionality outside of the Scoreboard.
+
{{Global Header}}
 +
{{Global Announcement}}
  
−
== All tickets ==
+
== Overview ==
−
== All tickets ==
+
Stored Queries are used exclusively for the [[Scoreboard]]. Stored Queries have no capabilities or functionality outside of the Scoreboard.
−
{| style="width:100%;"
+
 
 +
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 fulfill 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 ===
 +
{| style="width:100%;vertical-align:top;"
 
|-
 
|-
 
! style="width:30%;" | Description
 
! style="width:30%;" | Description
Line 29: Line 40:
 
| assignee = @cnt.id
 
| 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 Location contains specific text
 +
| customer.location.name like \'%keyword%\'
 +
|-
 +
| 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 (this doesn't work)
 +
| Requests, Incidents & Problems only
 +
|-
 +
| Affected End User is the person logged in
 +
| customer = @cnt.id
 +
| Requests, Incidents & Problems only
 +
|-
 +
| Affected End User's Primary Contact
 +
| customer.location.primary_contact
 +
| Requests, Incidents & Problems only
 +
|-
 +
| Person logged in is the Primary Contact for the Affected End User's Location
 +
| customer.location.primary_contact = @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 Location contains specific text
 +
| affected_contact.location.name like \'%keyword%\'
 +
| 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
 +
|-
 +
| Logged in person's CAB(s) group tickets
 +
| cab.[group]group_list.member IN (@cnt.id)
 +
| Applies to Change Orders and Issues only
 
|-
 
|-
 
| Callback Today
 
| Callback Today
Line 34: Line 116:
 
|
 
|
 
|-
 
|-
−
| Category or Area is specific
+
| Category or Request/Incident/Problem Area is specific
 
| category.id = nnnnnn
 
| category.id = nnnnnn
 
|
 
|
 
|-
 
|-
 
| Group is specific
 
| Group is specific
−
| group=nnnnnn
+
| group=U\'7D15BCD6D0B7E8468E58EA600E61F484\'
−
|
+
| For pre-r11 releases, just reference the id.
 
|-
 
|-
 
| Member of the Group is the person logged in
 
| Member of the Group is the person logged in
Line 52: Line 134:
 
| Priority is 1
 
| Priority is 1
 
| priority = 5
 
| priority = 5
−
|
+
| References the enum value not the sym value.
 
|-
 
|-
 
| Priority is 5
 
| Priority is 5
 
| priority = 1
 
| priority = 1
−
|
+
| References the enum value not the sym value.
 
|-
 
|-
 
| Resolved
 
| Resolved
Line 63: Line 145:
 
|-
 
|-
 
| SLA has been violated
 
| SLA has been violated
−
| sla_violation != 0
+
| sla_violation = 0
 
|
 
|
 
|-
 
|-
 
| SLA has not been violated
 
| SLA has not been violated
−
| sla_violation = 0
+
| sla_violation != 0
 
|
 
|
 
|-
 
|-
Line 123: Line 205:
 
|-
 
|-
 
| Created yesterday
 
| Created yesterday
−
| open_date > StartAtTime(\'YESTERDAY\')
+
| open_date > StartAtTime(\'YESTERDAY\') AND open_date < EndAtTime(\'YESTERDAY\')
 
|
 
|
 
|-
 
|-
−
|}
+
| Created yesterday and today
−
 
+
| open_date > StartAtTime(\'YESTERDAY\')
−
== Requests, Incidents, Problems only ==
+
−
{| style="width:90%;"
+
−
|-
+
−
! style="width:50%;" | Description
+
−
! style="width:50%;" | Code
+
−
|-
+
−
| Affected end user is the person logged in
+
−
| customer = @cnt.id
+
−
|-
+
−
| Attached CO
+
−
| (change IS NOT NULL AND change != 0)
+
 
|-
 
|-
 
|}
 
|}
  
−
== Change Orders & Issues only ==
+
=== Workflow & Issue Workflow ===
−
{| style="width:90%;"
+
{| style="width:100%;"
 
|-
 
|-
−
! style="width:50%;" | Description
+
! style="width:30%;" | Description
−
! style="width:50%;" | Code
+
! style="width:40%;" | Code
−
|-
+
! style="width:30%;" | Notes
−
| Affected end user is the person logged in
+
−
| affected_contact = @cnt.id
+
−
|-
+
−
| Requester is the person logged in
+
−
| requestor = @cnt.id
+
−
|-
+
−
|}
+
−
 
+
−
== Workflow & Issue Workflow ==
+
−
{| style="width:90%;"
+
−
|-
+
−
! style="width:50%;" | Description
+
−
! style="width:50%;" | Code
+
 
|-
 
|-
 
| Assignee is logged in person
 
| Assignee is logged in person
 
| assignee = @cnt.id
 
| assignee = @cnt.id
 +
|
 
|-
 
|-
 
| Assignee is a specific Group
 
| Assignee is a specific Group
−
| assignee.last_name = \'xxxxxxxxxx\'
+
| 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
 
| Future
 
| status.allow_task_update = 0 AND status = \'WAIT\'
 
| status.allow_task_update = 0 AND status = \'WAIT\'
 +
|
 
|-
 
|-
 
| Waiting
 
| Waiting
 
| status = \'WAIT\'
 
| status = \'WAIT\'
 +
|
 
|-
 
|-
 
| Pending
 
| Pending
 
| status = \'PEND\'
 
| status = \'PEND\'
 +
|
 
|-
 
|-
 
| To do today
 
| To do today
 
| status = \'PEND\' AND est_completion_date < EndAtTime(\'TODO_TODAY\')
 
| status = \'PEND\' AND est_completion_date < EndAtTime(\'TODO_TODAY\')
 +
|
 
|-
 
|-
 
| Cannot be edited
 
| Cannot be edited
 
| status.allow_task_update = 0
 
| status.allow_task_update = 0
 +
|
 
|-
 
|-
 
| Is not complete
 
| Is not complete
 
| status.task_complete = 0
 
| status.task_complete = 0
 +
|
 
|-
 
|-
−
| Is not next in the workflow
+
| Is not next in the Workflow
 
| status.do_next_task = 0
 
| status.do_next_task = 0
 +
|
 +
|-
 +
| Specific Task Type
 +
| task.sym = \'[task name]\'
 +
|
 
|-
 
|-
 
|}
 
|}
  
−
== Contacts ==
+
=== Contacts ===
−
{| style="width:90%;"
+
{| style="width:100%;"
 
|-
 
|-
−
! style="width:50%;" | Description
+
! style="width:30%;" | Description
−
! style="width:50%;" | Code  
+
! style="width:40%;" | Code
 +
! style="width:30%;" | Notes
 
|-
 
|-
 
| All Analysts
 
| All Analysts
 
| type = 2307
 
| type = 2307
 +
|
 
|-
 
|-
 
| Analyst is available
 
| Analyst is available
 
| available = 1
 
| available = 1
 +
|
 
|-
 
|-
 
| Analyst is unavailable
 
| Analyst is unavailable
 
| available=0 OR available IS NULL
 
| available=0 OR available IS NULL
 +
|
 
|-
 
|-
 
| Contact is active
 
| Contact is active
 
| delete_flag = 1
 
| delete_flag = 1
 +
|
 
|-
 
|-
 
| Contact is inactive
 
| Contact is inactive
 
| delete_flag = 0
 
| delete_flag = 0
 +
|
 
|-
 
|-
−
| Contact's location is the same as the logged in person's location
+
| Contact's Location is the same as the logged in person's Location
 
| location = @cnt.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
 
|-
 
|-
 
|}
 
|}
  
−
== Configuration Items ==
+
=== Configuration Items ===
−
<br>
+
 
 +
=== Knowledge ===
 +
 
 +
== See Also ==
 +
[[Scoreboard]]
  
−
== Knowledge ==
+
[[Time-based Stored Queries]]
−
<br>
+

Latest revision as of 07:48, 2 October 2012

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.

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 fulfill 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 Location contains specific text customer.location.name like \'%keyword%\'
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 (this doesn't work) Requests, Incidents & Problems only
Affected End User is the person logged in customer = @cnt.id Requests, Incidents & Problems only
Affected End User's Primary Contact customer.location.primary_contact Requests, Incidents & Problems only
Person logged in is the Primary Contact for the Affected End User's Location customer.location.primary_contact = @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 Location contains specific text affected_contact.location.name like \'%keyword%\' 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
Logged in person's CAB(s) group tickets cab.[group]group_list.member IN (@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
Specific Task Type task.sym = \'[task name]\'

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

Configuration Items

Knowledge

See Also

Scoreboard

Time-based Stored Queries

Retrieved from "http://greggsmith.net/wiki/index.php?title=Stored_Queries&oldid=4803"