Query to show SLA violated and not violated request count based on technician (MSSQL)
Tested in Build MSSQL (14306)
SELECT ti.FIRST_NAME "Technician",
count(wo.WORKORDERID) "Total Request",
count(case when std.ISPENDING='0' THEN 1 ELSE NULL END) "Completed Requests" ,
count(case when (wos.ISOVERDUE='1') THEN 1 ELSE NULL END) "SLA violated",
count(case when (wos.ISOVERDUE='0') THEN 1 ELSE NULL END) "SLA not violated" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN StatusDefinition std on wos.statusid=std.statusid
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
LEFT JOIN workorder_queue ON workorder_queue.workorderid=wo.workorderid
LEFT JOIN queuedefinition ON workorder_queue.queueid=queuedefinition.queueid
WHERE (wo.ISPARENT='1') and
dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.createdtime/1000),'1970-01-01 00:00:00') >= convert(varchar,'2017-03-01 00:00',21) and dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.createdtime/1000),'1970-01-01 00:00:00') <= convert(varchar,'2018-03-01 23:59',21)
GROUP BY ti.FIRST_NAME
order by 1
New to ADSelfService Plus?
Related Articles
Query to show response, resolution SLA violated count and its percentage ( PGSQL & MSSQL)
Tested in build PGSQL (14300) and MSSQL (14306) SELECT pd.PRIORITYNAME AS "Priority" , count(wo.WORKORDERID) "Total Requests", count(case when ( std.ispending='0') THEN 1 ELSE NULL END) "Closed", count(case when (wos.IS_FR_OVERDUE='1') THEN 1 ELSE ...Query to show KPI report based on technician (PGSQL & MSSQL)
Working on Builds: 14500 Database: MSSQL, PGSQL This report is already available under Frequently asked reports under Reports->New Query Reports page. Below query can be used for date filter and filter by technicians name modifications. SELECT ...Query to show who actually violated the SLA (PGSQL & MSSQL )
Working on Builds 14500 and above. Tested in build PGSQL (14300) and MSSQL (14306) Databases: PGSQL & MSSQL: SELECT wo.WORKORDERID "Request ID", max(wo.TITLE) "Subject", max(ti.FIRST_NAME) "Current Technician", max(std.STATUSNAME) "Request Status", ...Query to show technician hop count (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) SELECT wo.WORKORDERID "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Created Time", qd.QUEUENAME "Current Group",aau.FIRST_NAME AS "Requester", ti.FIRST_NAME "Technician", ad.ORG_NAME AS "Account", ...Send escalation email to the Technician "Reporting to" if SLA is violated (Deluge).
From Builds 10600 and above Follow the below steps and based on the update we need to create a custom trigger based on the action set under the SLA escalation notification and then with the help of that we can execute the custom trigger and the ...