Query to know the technician reassignment history

Query to know the technician reassignment history


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 "Subject", 
wotodesc.FULLDESCRIPTION AS "Description", 
requester.First_Name As "Requester", 
srep.First_Name As "Suprep",  
sdf.StatusName As "Request Status",
qd.QUEUENAME AS "Group",
LONGTODATE(wo.createdtime) AS "CREATED Time",
LONGTODATE(wo.duebytime) AS "Due By time",
LONGTODATE(wo.completedtime) As "Completed time",
operationOwner.First_Name As "performedby",
LONGTODATE(wh.operationtime) As "operationtime",
prev.First_Name As "Suprep changed from", 
cur.First_Name As "Suprep changed to" ,
serdef.NAME AS "Service Category", 
pd.PRIORITYNAME AS "Priority", 
ad.ORG_NAME AS "Account", 
sdo.NAME AS "Site", 
rrs.RESOLUTION AS "Resolution" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID 
Left Join AAAUser requester ON wo.RequesterID=requester.User_Id 
Left Join AAAUser srep ON wos.OwnerID=srep.User_ID
Left Join StatusDefinition sdf ON wos.StatusID=sdf.StatusID 
LEFT JOIN WorkOrderToDescription wotodesc ON wo.WORKORDERID=wotodesc.WORKORDERID 
LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID 
LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID 
LEFT JOIN ServiceDefinition serdef ON wo.SERVICEID=serdef.SERVICEID 
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
 LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID 
 Left Join WorkorderHistory wh ON wo.WorkorderId=wh.WorkorderId 
 Left Join WorkorderHistoryDiff whd ON wh.HistoryID=whd.HistoryId
 Left Join AAAUser operationOwner ON wh.OperationOwnerid=operationOwner.User_Id
 Left Join AAAUser prev ON cast(cast(whd.prev_value as varchar) as int)=prev.User_Id
 Left Join AAAUser cur ON cast(cast(whd.current_value as varchar) as int)=cur.User_Id 
 LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID
LEFT JOIN RequestResolver rrr ON wo.WORKORDERID=rrr.REQUESTID 
LEFT JOIN RequestResolution rrs ON rrr.REQUESTID=rrs.REQUESTID 
LEFT JOIN AccountSiteMapping asm ON wo.SITEID=asm.SITEID 
LEFT JOIN AccountDefinition ad ON asm.ACCOUNTID=ad.ORG_ID 
where whd.ColumnName='OWNERID' AND  "wo"."CREATEDTIME" >= datetolong('2021-04-20')
  AND  "wo"."CREATEDTIME" <= datetolong('2021-04-21') 
AND cur.First_Name = 'administrator'

PS : change where criteria for technicians and dates.
          • Related Articles

          • Query to track technician activity on tickets

            Use case: This query will help you find what are all activities that the technicians have done on requests other than the assigned ones SELECT au.first_name "Technician" ,wo.WORKORDERID "Request ID",aau.FIRST_NAME "Requester",wo.TITLE ...
          • Technician Auto-Assign vs. Business Rule

            If you have selected the option All requests under Tech Auto-assign then even if the Business rule sets a technician, tech auto-assign will reset the technician value. However, when Tech Auto-assign is set as an Unassigned request, Technician ...
          • Query to show Technician leave details

            select au.first_name "Technician", ltd.name "Leave type", longtodate(du.leavedate) "Leave date", ta.comments "Leave Comments" from techunavailability ta left join aaauser au on au.user_id=ta.technicianid left join leavetypedefinition ltd on ...
          • Query to show ticket first assign/pick up time _ PGSQL

            PGSQL: SELECT wo.WORKORDERID "Request ID", wo.title "Subject", longtodate(wo.CREATEDTIME) "Request Created Time", longtodate(woh.operationtime) "First Pickup time", qd.QUEUENAME "Group",ti.FIRST_NAME "Technician",ad.org_name "Account", ...
          • Query to show technician hop count

            MSSQL: 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", ti1.FIRST_NAME "From technician", ...