Query to generate the report based on the total number of requests actioned by the technicians (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306)
This is a comprehensive report on the total number of requests updated by a technicians. This query provides a holistic view of technician activity like updating resolution process, such as updating or modifying fields, sending replies, adding or modifying work logs or tasks, and more.
SELECT wo.WORKORDERID AS "Request ID", wo.WORKORDERID AS "Request ID", ad.ORG_NAME AS "Account", sdo.NAME AS "Site", longtodate(wh.operationtime) "Updated time", au.first_name "Updated by" FROM WorkOrder wo
LEFT JOIN WorkOrderAccountMapping wam ON wo.WORKORDERID=wam.WORKORDERID
LEFT JOIN PortalAccounts port_acc ON wam.ACCOUNTID=port_acc.ACCOUNTID
LEFT JOIN AccountDefinition ad ON port_acc.ACCOUNTID=ad.ORG_ID
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID
LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID
LEFT JOIN workorderhistory wh on wh.workorderid=wo.workorderid
left join aaauser au on au.user_id=wh.operationownerid
where wh.operation ='update' and au.first_name ='TechnicianName' and wh.OPERATIONTIME >= <from_today> AND wh.OPERATIONTIME <= <to_today> order by 1
NOTE:
Update the technician name to generate the report for the specific technician. Otherwise, remove "and au.first_name ='TechnicianName' " to generate the report for all technicians.
New to ADSelfService Plus?
Related Articles
Query to show KPI report based on technician (PGSQL & MSSQL)
Working on Builds: 14500 Database: MSSQL, PGSQL This report is already available under Frequently asked reports under Reports->New Query Reports page. Below query can be used for date filter and filter by technicians name modifications. SELECT ...Query to track technician activity on tickets (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) 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 ...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 report to extract Active Technician Login and IP Address (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) Go to Reports- New Query Report and execute this query. select DISTINCT ac.ACCOUNT_ID as "Login Account ID", au.FIRST_NAME as "Technician Name ( First Name )", count(acs.SESSION_ID) as "Number of ...Query to show last updated field (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Kindly go to Reports-New Query Report and execute the below query. PGSQL: SELECT wo.WORKORDERID AS "Request ID", max(wo.TITLE) AS "Subject", max(ti.FIRST_NAME) AS "Assigned Technician", ...