Query to track technician activity on tickets

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 "Subject",std.STATUSNAME "Request Status",Longtodate(wo.CREATEDTIME) "Created Time",Longtodate(wo.COMPLETEDTIME) "Completed Time",
ti.FIRST_NAME "Assigned Technician",au.first_name "Operation performed Technician",
(woh.Operation) "Operation",
wohd.columnname "Column Name",
LONGTODATE(OPERATIONTIME) "OPERATIONTIME"
  FROM WorkOrder wo 
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID 
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID 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 workorderhistory woh on wo.workorderid=woh.workorderid LEFT JOIN WorkOrderHistoryDiff wohd ON woh.HISTORYID = wohd.HISTORYID  left join aaauser au on woh.operationownerid=au.user_id where  (wo.ISPARENT='1') AND au.first_name='administrator'  AND woh.OPERATIONTIME>=<from_thismonth>and woh.OPERATIONTIME<=<to_thismonth>  order by 1

NOTE: Date filter highlighted in the query can be modified and below parameters can be used instead.

It is not advised to show more than a month's data and data for only one technician at the same time as it impacts application performance since it involves request history table.

How to compare date column with auto filled date templates?
  1. Here is the example for getting this week data - CREATEDTIME >= <from_thisweek> AND CREATEDTIME <= <to_thisweek>
    • <from_thisweek> - Starting date of this week
    • <to_thisweek> - Ending date of this week
  2. Available Date Templates
    • Today - <from_today> - <to_today>
    • This week - <from_thisweek> - <to_thisweek>
    • Last week - <from_lastweek> - <to_lastweek>
    • This month - <from_thismonth> - <to_thismonth>
    • Last month - <from_lastmonth> - <to_lastmonth>
    • This quarter - <from_thisquarter> - <to_thisquarter>
    • Last quarter - <from_lastquarter> - <to_lastquarter>
    • Yesterday - <from_yesterday> - <to_yesterday>

          • Related Articles

          • Query to show active and archived tickets

            Below is only for MSSQL. For PGSQL query, refer comments section SELECT wo.WORKORDERID "Request ID",qd.QUEUENAME "Group",ti.FIRST_NAME "Technician",serdef.NAME "Service Category",wo.TITLE "Subject",sdo.NAME "Site",accountdefinition.org_name ...
          • Query to show Approved tickets per Approver

            PGSQL & MSSQL: SELECT wo.WORKORDERID "Request ID", wo.TITLE "Subject", std.STATUSNAME "Request Status", ti.FIRST_NAME "Technician", longtodate(wo.CREATEDTIME) "Created Time", ApprovalDetails.EMAIL "Approver Email", asd.stagename "Stage", ...
          • Query to show shared requests

            Shared to Tech: SELECT wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", qd.QUEUENAME AS "Group", ti.FIRST_NAME AS "Technician", LONGTODATE(wo.CREATEDTIME) AS "Created Time",  LONGTODATE(wo.COMPLETEDTIME) AS "Completed Time",  ...
          • 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 problem time spent along with last activity on a problem

            MSSQL: SELECT "prob"."PROBLEMID" AS "Problem ID",  "prob"."TITLE" AS "Title", "statdef"."STATUSNAME" AS "Status",  "orgaaa"."FIRST_NAME" AS "Reported by", Cast((((sum(ct.timespent))/1000)/3600) as varchar(20)) +'Hrs ...