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 list the custom and query reports and the technician created (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Use case The query displays the custom reports and query reports saved by technicians. Query SELECT custrepdet.report_name "Report Name", au.first_name"Created By" from customreportquery custrep ...Query to show total number of calls received and resolved per month (MSSQL & PGSQL )
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL & MSSQL: SELECT count(wo.workorderid) "Total Created", count(case when wo.completedtime >= <from_thismonth> AND wo.completedtime <= <to_thismonth> THEN 1 ELSE NULL END) "Closed" FROM WorkOrder wo ...Query to show total number of conversations, incoming and outgoing conversations (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) Total Number of Conversations: SELECT wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", pd.PRIORITYNAME AS "Priority", aau.FIRST_NAME AS "Requester", ad.ORG_NAME AS "Account", std.STATUSNAME AS ...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 ...