Difference between revisions of "Stored Queries"

From SDU
Jump to: navigation, search
Line 1: Line 1:
−
[[Category:Miscellaneous]][[Category:Definitions]]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.
+
[[Category:Miscellaneous]][[Category:Definitions]]
 +
Stored Queries are used exclusively for the [[Scoreboard]]. Stored Queries have no capabilities or functionality outside of the Scoreboard.  
  
−
This article is catered to Service Desk r6.0, however many of the queries can be used for older released.
+
== All tickets ==
 +
'''Active:''' active = 1<br>
 +
'''Inactive:''' active = 0<br>
  
−
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.
+
'''Has Assignee:''' assignee is not null<br>
 +
'''No Assignee:''' assignee is null<br>
 +
'''Assignee is the person logged in:''' assignee = @cnt.id<br>
 +
'''Assignee is a specific contact:''' assignee = nnnnnn<br>
  
 +
'''Callback Today:''' call_back_flag=1 AND call_back_date < EndAtTime(\'TODO_TODAY\')<br>
  
−
== All tickets ==
+
'''Category or Area is specific:'''category.id = nnnnnn<br>
−
Active: active = 1
+
 
−
Inactive: active = 0
+
'''Group is specific:''' group=nnnnnn<br>
−
Assigned: assignee is not null
+
'''Member of the Group is the person logged in:''' group.group_list.member IN (@cnt.id)<br>
−
Unassigned: assignee is null
+
 
−
Assignee is the person logged in: assignee = @cnt.id
+
'''Created by the person logged on:''' log_agent = @cnt.id<br>
−
Assignee is a specific contact: assignee = nnnnnn
+
 
−
Callback Today: call_back_flag=1 AND call_back_date < EndAtTime(\'TODO_TODAY\')
+
'''Priority is 1:''' priority = 5<br>
−
Category is specific:category.id = nnnnnn
+
'''Priority is 5:''' priority = 1<br>
−
Group is specific: group=nnnnnn
+
 
−
Member of the Group is the person logged in: group.group_list.member IN (@cnt.id)
+
'''Resolved:''' active = 1 and status = \'RE\'<br>
−
Created by the person logged on: log_agent = @cnt.id
+
 
−
Priority is 1: priority = 5
+
'''SLA has been violated:''' sla_violation != 0<br>
−
Priority is 5: priority = 1
+
'''SLA has not been violated:''' sla_violation = 0<br>
−
Request Area is specific:category.id = nnnnnn
+
 
−
Resolved: active = 1 and status = \'RE\'
+
'''Created in the past 60 minutes:''' open_date > StartAtTime(\'PAST_HOUR\')<br>
−
SLA has been violated: sla_violation != 0
+
'''Created in the past 24 hours:''' open_date > StartAtTime(\'PAST_DAY\')<br>
−
SLA has not been violated: sla_violation = 0
+
'''Created in the past 7 days:''' open_date > StartAtTime(\'PAST_WEEK\')<br>
−
Created in the past 60 minutes: open_date > StartAtTime(\'PAST_HOUR\')
+
'''Created in the past 30 days:''' open_date > StartAtTime(\'PAST_MONTH\')<br>
−
Created in the past 24 hours: open_date > StartAtTime(\'PAST_DAY\')
+
'''Created in the past 365 days:''' open_date > StartAtTime(\'PAST_YEAR\')<br>
−
Created in the past 7 days: open_date > StartAtTime(\'PAST_WEEK\')
+
'''Created last hour:''' open_date > StartAtTime(\'LAST_HOUR\')<br>
−
Created in the past 30 days: open_date > StartAtTime(\'PAST_MONTH\')
+
'''Created last month:''' open_date > StartAtTime(\'LAST_MONTH\')<br>
−
Created in the past 365 days: open_date > StartAtTime(\'PAST_YEAR\')
+
'''Created last year:''' open_date > StartAtTime(\'LAST_YEAR\')<br>
−
Created last hour: open_date > StartAtTime(\'LAST_HOUR\')
+
'''Created this hour:''' open_date > StartAtTime(\'THIS_HOUR\')<br>
−
Created last month: open_date > StartAtTime(\'LAST_MONTH\')
+
'''Created this month:''' open_date > StartAtTime(\'THIS_MONTH\')<br>
−
Created last year: open_date > StartAtTime(\'LAST_YEAR\')
+
'''Created this month last year:''' open_date > StartAtTime(\'THIS_MONTH_LAST_YEAR\')<br>
−
Created this hour: open_date > StartAtTime(\'THIS_HOUR\')
+
'''Created today:''' open_date > StartAtTime(\'TODAY\')<br>
−
Created this month: open_date > StartAtTime(\'THIS_MONTH\')
+
'''Created today after noon:''' open_date > StartAtTime(\'AFTER_NOON\')<br>
−
Created this month last year: open_date > StartAtTime(\'THIS_MONTH_LAST_YEAR\')
+
'''Created today before noon:''' open_date > StartAtTime(\'BEFORE_NOON\')<br>
−
Created today: open_date > StartAtTime(\'TODAY\')
+
'''Created yesterday:''' open_date > StartAtTime(\'YESTERDAY\')<br>
−
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 only ==
 
== Requests, Incidents, Problems only ==

Revision as of 21:57, 13 January 2008

Stored Queries are used exclusively for the Scoreboard. Stored Queries have no capabilities or functionality outside of the Scoreboard.

All tickets

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
Assignee is a specific contact: assignee = nnnnnn

Callback Today: call_back_flag=1 AND call_back_date < EndAtTime(\'TODO_TODAY\')

Category or Area 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

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 only

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

Change Orders & Issues only

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

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