Request violated by technician
This report used to find the technician who violated the request. If the request/incident already has a violation and is reassigned to another technician the new technician assumes the violation instead of the technician that the violation occurred.
To make any changes to a query, refer to the KB article below.
SELECT wo.WORKORDERID "Request ID", max(wo.TITLE) "Subject", max(ti.FIRST_NAME) "Current Technician", max(std.STATUSNAME) "Request Status", CASE WHEN max(tech.first_name) IS NOT NULL THEN max(tech.first_name) ELSE max(ti.FIRST_NAME) END "Due by Technician" 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 StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID LEFT JOIN (SELECT woh1.workorderid, ti1.first_name FROM workorderhistory woh1 LEFT JOIN workorderhistorydiff wohd1 ON woh1.historyid=wohd1.historyid LEFT JOIN aaauser ti1 ON cast(cast(wohd1.prev_value AS varchar) AS int)=ti1.user_id LEFT JOIN workorder wo1 ON wo1.workorderid=woh1.workorderid WHERE (wohd1.COLUMNNAME) IN ('OWNERID') AND woh1.operationtime > wo1.duebytime) tech ON wo.workorderid=tech.workorderid WHERE wos.isoverdue='1' AND wo.CREATEDTIME >= <from_thisweek> AND wo.CREATEDTIME <= <to_thisweek> GROUP BY wo.WORKORDERID
Note : Login to ServiceDesk Plus, go to Reports tab > New Query Report > Copy the query to the query editor and run the report.
New to ADSelfService Plus?
Related Articles
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 ...Request violated by Group
This report used to find the group who violated the request. If the request/incident already has a violation and is reassigned to another group the new group assumes the violation instead of the group that the violation occurred. To make any changes ...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 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 know the technician changes in a ticket
This will show the output only if the technician is assigned/updated in a ticket 1. Login to SDP MSP as administrator 2. Execute this from SDP MSP application -> Reports -> New Query report SELECT wo.WORKORDERID AS "Request ID", wo.TITLE AS ...