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 "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 "Technician changed from",
cur.First_Name As "Technician 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.
New to ADSelfService Plus?
Related Articles
Query to show priority changes in the ticket (MSSQL)
Tested in MSSQL build (14306) This query shows which tickets were downgraded from P1. MSSQL: SELECT wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", aau.FIRST_NAME AS "Requester", ti.FIRST_NAME AS "Technician", LONGTODATE(wo.DUEBYTIME) "DueBy ...Query to show ticket first assign/pick up time _ (PGSQL )
Tested in Build PGSQL (14300) or MSSQL (14306) 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 ...Script to automatically assign a technician to an unassigned request based on the first response
When a technician responds to an unassigned request, the corresponding ticket will be automatically assigned to the specific technician. Tested in: Build 14306 (Postgres) Steps: Download the attached scripts. Under Admin > Developer Space > Global ...Query to show technician hop count (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) 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", ...Query to show total requests and changes with its total worklog hours of a technician- MSSQL
Working ON Builds: 14500 MSSQL: Requests: SELECT rctd.first_name "Technician", count(wo.workorderid) "Sum of tickets handled by him", ...