Time taken to First Response
First Response Time is the number of minutes, hours, or days between when a customer submits a support ticket and when a customer support representative provides an initial response. It indicates how long a customer has to wait before being helped.
To make any changes to a query, refer to the KB article below.
MSSQL
SELECT wo.WORKORDERID "Request ID", wo.TITLE "Subject", aau.FIRST_NAME "Requester", ti.FIRST_NAME "Technician", LONGTODATE(wo.CREATEDTIME) "Created Time", LONGTODATE(wo.RESPONDEDTIME) "Responded Date", convert(varchar(10), ((wo.RESPONDEDTIME)-(wo.createdtime))/1000/3600)+':'+convert(varchar(10), (((wo.RESPONDEDTIME)-(wo.createdtime))/1000)%3600/60)+':'+convert(varchar(10),((((wo.RESPONDEDTIME)-(wo.createdtime)))/1000%60)) "Created Date - Responded Date" 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 WHERE (wo.ISPARENT='1') AND RESPONDEDTIME !='0' AND wo.CREATEDTIME >= <from_thisweek> AND wo.CREATEDTIME <= <to_thisweek>
PGSQL
SELECT wo.WORKORDERID "Request ID", wo.TITLE "Subject", aau.FIRST_NAME "Requester", ti.FIRST_NAME "Technician", LONGTODATE(wo.CREATEDTIME) "Created Time", LONGTODATE(wo.RESPONDEDTIME) "Responded Date", TO_CHAR(((wo.respondedtime-wo.createdtime)/1000 || ' second')::interval, 'HH24:MI:SS') "Created Date - Responded Date" 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 WHERE (wo.ISPARENT='1') AND RESPONDEDTIME !='0' AND wo.CREATEDTIME >= <from_thisweek> AND wo.CREATEDTIME <= <to_thisweek>
Note : Login to ServiceDesk Plus, go to Reports tab > New Query Report > Copy the query to the query editor and run the report.
New to ADSelfService Plus?
Related Articles
Queries to show time stamp and time taken to first response, first tech assign, first group assign and first status set time ( MSSQL )
Tested in MSSQL build (14306) MSSQL: First responded time: SELECT wo.WORKORDERID "Request ID", wo.TITLE "Subject", aau.FIRST_NAME "Requester", ti.FIRST_NAME "Technician", LONGTODATE(wo.CREATEDTIME) "Created Time", LONGTODATE(wo.RESPONDEDTIME) ...Query to retrieve response time in minutes
To return the ticket response time in minutes along with other request details. TESTED IN: Builds 14700 (Postgres) QUERY: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", ...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 ...Query to show the time taken between created time and first technician assigned time (PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Go to Reports-New Query Report and execute this query. PGSQL: SELECT wo.WORKORDERID "Request ID", aau.FIRST_NAME "Requester", LONGTODATE(wo.createdtime) "Created Time", LONGTODATE(wo.COMPLETEDTIME) ...Query to show total time taken to resolve and total time onhold ( MSSQL )
Tested in build MSSQL (14306) SELECT "wo"."WORKORDERID" AS "Request ID", "mdd"."MODENAME" AS "Request Mode", "qd"."QUEUENAME" AS "Group", "aau"."FIRST_NAME" AS "Requester", "cd"."CATEGORYNAME" AS "Category", "scd"."NAME" AS "Subcategory", ...