Query to show SLA assigned to tickets
Working on Builds: 14500 and above
Database: PGSQL & MSSQL:
SELECT slad.slaname "SLA", ad.ORG_NAME AS "Account", wo.WORKORDERID AS "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Created Time", ti.FIRST_NAME AS "Technician", rtdef.NAME AS "Request Type" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN RequestTypeDefinition rtdef ON wos.REQUESTTYPEID=rtdef.REQUESTTYPEID LEFT JOIN AccountSiteMapping asm ON wo.SITEID=asm.SITEID LEFT JOIN AccountDefinition ad ON asm.ACCOUNTID=ad.ORG_ID LEFT JOIN SLADefinition slad ON wo.slaid=slad.slaid WHERE (wo.ISPARENT='1') AND wo.createdtime>=<from_thismonth> and wo.createdtime<=<to_thismonth>
NOTE: Date filter highlighted in the query can be modified and below parameters can be used instead.
How to compare date column with auto filled date templates?
- Here is the example for getting this week data - CREATEDTIME >= <from_thisweek> AND CREATEDTIME <= <to_thisweek>
- <from_thisweek> - Starting date of this week
- <to_thisweek> - Ending date of this week
- Available Date Templates
- Today - <from_today> - <to_today>
- This week - <from_thisweek> - <to_thisweek>
- Last week - <from_lastweek> - <to_lastweek>
- This month - <from_thismonth> - <to_thismonth>
- Last month - <from_lastmonth> - <to_lastmonth>
- This quarter - <from_thisquarter> - <to_thisquarter>
- Last quarter - <from_lastquarter> - <to_lastquarter>
- Yesterday - <from_yesterday> - <to_yesterday>
New to ADSelfService Plus?
Related Articles
Query to show tickets first assigned today irrespective of created date
MSSQL: SELECT wo.WORKORDERID "Request ID", max(aau.FIRST_NAME) "Requester", max(dpt.DEPTNAME) "Department", max(wo.TITLE) "Subject", max(qd.QUEUENAME)"Group", max(ti.FIRST_NAME) "Technician", max(std.STATUSNAME) "Request Status", ...Query to show first assigned and re-assigned ticket irrespective of the created date ( MSSQL)
Tested in build MSSQL (14306) MSSQL: SELECT wo.WORKORDERID "Request ID", max(aau.FIRST_NAME) "Requester", max(dpt.DEPTNAME) "Department", max(wo.TITLE) "Subject", max(qd.QUEUENAME)"Group", max(ti.FIRST_NAME) "Technician", max(std.STATUSNAME) "Request ...Query to show unassigned time of a request and when its first assigned (MSSQL)
Tested in Build MSSQL (14306) Go to Reports-New Query Report and execute this query. MSSQL: SELECT wo.WORKORDERID "Request ID", aau.FIRST_NAME "Requester", LONGTODATE(wo.createdtime) "Created Time", LONGTODATE(wo.COMPLETEDTIME) "Completed Time", ...Query to get complete SLA details. (PGSQL)
Tested in build PGSQL (14300) To get the complete list of SLA configured in the application. PGSQL: SELECT sdo.name "Site Name" , sla.slaname "SLA", MAX(sla.duebydays) "SLA Days", MAX(sla.duebyhours) "SLA Hours", MAX(sla.duebyminutes) "SLA Minutes" , ...SLA details
This report is used to get the complete list of SLA configured in the application. PGSQL SELECT sdo.name "Site Name" , sla.slaname "SLA", MAX(sla.duebydays) "SLA Days", MAX(sla.duebyhours) "SLA Hours", MAX(sla.duebyminutes) "SLA Minutes" , ...