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
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 ...