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.

https://pitstop.manageengine.com/support/manageengine/ShowHomePage.do#Solutions/dv/24000633501275

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.