Request violated by technician

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. 

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. 


          • Related Articles

          • 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 ...
          • Technician change from history

            This report is used to find who changed the Technician and how many technician handled the request.  To make any changes to a query, refer to the KB article below. ...
          • Technician

            This report is used to find the Technician complete details.  SELECT AaaUser.USER_ID, AaaUser.FIRST_NAME "FullName", AaaLogin.NAME "LoginName", AaaLogin.DOMAINNAME "Domain", AaaContactInfo.EMAILID "Email", DepartmentDefinition.DEPTNAME "Department", ...
          • How to Auto Share Request Upon Creation to All/Selected Technicians

            NOTE:  This script is supported only for builds below 11.3 This post describes the use of a python script to share requests to technicians specified in a list variable in the script, which can be generated from an SQL query. This script can be ...
          • How to email pending requests list to technician periodically

            This post describes the use of a python script to email pending requests to each technician in a periodic interval using Custom Schedules.This showcases the use of a feature called 'Custom Schedules' that was released in the build 9300 of ServiceDesk ...