Time elapsed analysis
This Report gives a clear picture of, how long the request was handled by each technician, stayed in each group and stayed in different statuses. Capturing each action performed on the request can be helpful for the request management team to assess any group/ technician's efficiency.
Frequent incident reassignment indicates that the assignment group members are lacking of knowledge or expertise, or inexplicit definition of responsibilities between the assignment groups. This information can be used by the IT Management team and the Service Management process managers to reallocate resources, organize additional training, and improve record transfer decisions to achieve more efficient record routing.
To make any changes to a query, refer to the KB article below.
PGSQL
Status
SELECT wo.WORKORDERID "Request ID", wo.TITLE "Subject", cd.CATEGORYNAME "Category", scd.NAME "Subcategory", icd.NAME "Item", ti.FIRST_NAME "Technician", qd.QUEUENAME "Group", CASE WHEN wos.ISOVERDUE='1' THEN 'Yes' ELSE 'No' END "Overdue Status", sd1.statusname "From status", sd2.statusname "To status", (wsi.TIMESPENT/60)||':'||((wsi.TIMESPENT))%60 "Time Spent" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN ItemDefinition icd ON wos.ITEMID=icd.ITEMID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID LEFT JOIN wo_assessment woa ON wo.workorderid=woa.workorderid INNER JOIN wo_status_info wsi ON woa.assessmentid=wsi.assessmentid LEFT JOIN statusdefinition sd1 ON wsi.statusid=sd1.statusid LEFT JOIN statusdefinition sd2 ON wsi.nextstatusid=sd2.statusid WHERE (wo.ISPARENT='1') AND wo.CREATEDTIME >= <from_thisweek> AND wo.CREATEDTIME <= <to_thisweek> ORDER BY 1
Technician
SELECT wo.WORKORDERID "Request ID", wo.TITLE "Subject", cd.CATEGORYNAME "Category", scd.NAME "Subcategory", icd.NAME "Item", ti.FIRST_NAME "Technician", qd.QUEUENAME "Group", CASE WHEN wos.ISOVERDUE='1' THEN 'Yes' ELSE 'No' END "Overdue Status", ti1.FIRST_NAME "From technician", ti2.FIRST_NAME "To technician", (wti.TIMESPENT/60)||':'||((wti.TIMESPENT))%60 "Time Spent" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN ItemDefinition icd ON wos.ITEMID=icd.ITEMID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID LEFT JOIN wo_assessment woa ON wo.workorderid=woa.workorderid INNER JOIN WO_TECH_INFO wti ON woa.assessmentid=wti.assessmentid LEFT JOIN AaaUser ti1 ON wti.technicianid=ti1.USER_ID LEFT JOIN AaaUser ti2 ON wti.nexttechnicianid=ti2.USER_ID WHERE (wo.ISPARENT='1') AND wo.CREATEDTIME >= <from_thisweek> AND wo.CREATEDTIME <= <to_thisweek> ORDER BY 1
Group
SELECT wo.WORKORDERID "Request ID", wo.TITLE "Subject", cd.CATEGORYNAME "Category", scd.NAME "Subcategory", icd.NAME "Item", ti.FIRST_NAME "Technician", qd.QUEUENAME "Group", CASE WHEN wos.ISOVERDUE='1' THEN 'Yes' ELSE 'No' END "Overdue Status", qd1.QUEUENAME "From Group", qd2.QUEUENAME "To Group", (wog.TIMESPENT/60)||':'||((wog.TIMESPENT))%60 "Time Spent" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN ItemDefinition icd ON wos.ITEMID=icd.ITEMID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID LEFT JOIN wo_assessment woa ON wo.workorderid=woa.workorderid INNER JOIN WO_GROUP_INFO wog ON woa.assessmentid=wog.assessmentid LEFT JOIN QueueDefinition qd1 ON wog.groupid=qd1.QUEUEID LEFT JOIN QueueDefinition qd2 ON wog.nextgroupid=qd2.QUEUEID WHERE (wo.ISPARENT='1') AND wo.CREATEDTIME >= <from_thisweek> AND wo.CREATEDTIME <= <to_thisweek> ORDER BY 1
MSSQL
Status
SELECT wo.WORKORDERID "Request ID", wo.TITLE "Subject", cd.CATEGORYNAME "Category", scd.NAME "Subcategory", icd.NAME "Item", ti.FIRST_NAME "Technician", qd.QUEUENAME "Group", CASE WHEN wos.ISOVERDUE='1' THEN 'Yes' ELSE 'No' END "Overdue Status", sd1.statusname "From status", sd2.statusname "To status", cast((wsi.timespent/60) as varchar(20)) +':'+cast((wsi.timespent % 60) as varchar(20)) "Time Spent" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN ItemDefinition icd ON wos.ITEMID=icd.ITEMID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID LEFT JOIN wo_assessment woa ON wo.workorderid=woa.workorderid INNER JOIN wo_status_info wsi ON woa.assessmentid=wsi.assessmentid LEFT JOIN statusdefinition sd1 ON wsi.statusid=sd1.statusid LEFT JOIN statusdefinition sd2 ON wsi.nextstatusid=sd2.statusid WHERE (wo.ISPARENT='1') AND wo.CREATEDTIME >= <from_thisweek> AND wo.CREATEDTIME <= <to_thisweek> ORDER BY 1
Technician
SELECT wo.WORKORDERID "Request ID", wo.TITLE "Subject", cd.CATEGORYNAME "Category", scd.NAME "Subcategory", icd.NAME "Item", ti.FIRST_NAME "Technician", qd.QUEUENAME "Group", CASE WHEN wos.ISOVERDUE='1' THEN 'Yes' ELSE 'No' END "Overdue Status", ti1.FIRST_NAME "From technician", ti2.FIRST_NAME "To technician", cast((wti.timespent/60) as varchar(20)) +':'+cast((wti.timespent % 60) as varchar(20)) "Time Spent" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN ItemDefinition icd ON wos.ITEMID=icd.ITEMID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID LEFT JOIN wo_assessment woa ON wo.workorderid=woa.workorderid INNER JOIN WO_TECH_INFO wti ON woa.assessmentid=wti.assessmentid LEFT JOIN AaaUser ti1 ON wti.technicianid=ti1.USER_ID LEFT JOIN AaaUser ti2 ON wti.nexttechnicianid=ti2.USER_ID WHERE (wo.ISPARENT='1') AND wo.CREATEDTIME >= <from_thisweek> AND wo.CREATEDTIME <= <to_thisweek> ORDER BY 1
Group
SELECT wo.WORKORDERID "Request ID", wo.TITLE "Subject", cd.CATEGORYNAME "Category", scd.NAME "Subcategory", icd.NAME "Item", ti.FIRST_NAME "Technician", qd.QUEUENAME "Group", CASE WHEN wos.ISOVERDUE='1' THEN 'Yes' ELSE 'No' END "Overdue Status", qd1.QUEUENAME "From Group", qd2.QUEUENAME "To Group", cast((wog.timespent/60) as varchar(20)) +':'+cast((wog.timespent % 60) as varchar(20)) "Time Spent" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN ItemDefinition icd ON wos.ITEMID=icd.ITEMID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID LEFT JOIN wo_assessment woa ON wo.workorderid=woa.workorderid INNER JOIN WO_GROUP_INFO wog ON woa.assessmentid=wog.assessmentid LEFT JOIN QueueDefinition qd1 ON wog.groupid=qd1.QUEUEID LEFT JOIN QueueDefinition qd2 ON wog.nextgroupid=qd2.QUEUEID WHERE (wo.ISPARENT='1') AND wo.CREATEDTIME >= <from_thisweek> AND wo.CREATEDTIME <= <to_thisweek> ORDER BY 1
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
Keep track of your requests with Time Elapsed Analysis in ServiceDesk Plus.
Time-elapsed analysis provides information on how long any request was handled by a technician and remained in a group or status. Select any request from the Request List View and click the clock icon next to the History tab in the Request ...Time elapsed by each group in requests. PGSQL
Tested in Build PGSQL (14300) Go to Reports-New Query Report and execute the below query. SELECT wo.WORKORDERID AS "Request ID", mdd.MODENAME AS "Request Mode", qd.QUEUENAME AS "Current Group", aau.FIRST_NAME AS "Requester", ti.FIRST_NAME AS ...How Time Elapsed is calculated?
What is Time Elapsed? Time Elapsed is the calculation of the time taken for the completion of the request created with a proposed solution. How Time Elapsed is calculated? ** This calculation depends upon Sites,Operational Hours,Holidays,Request ...Query to show time elapsed in a ticket with SLA associated (PGSQL)
Tested in PGSQL build (14300) PGSQL: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", ti.FIRST_NAME AS "Technician", cd.CATEGORYNAME AS "Category", std.STATUSNAME AS "Request Status", LONGTODATE(wo.CREATEDTIME) "Created Time", ...Request Time Analysis Reports (Status, Group, Technician Fields changes and its timespent) (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) This report contains the time analysis for each status/group/technician changes in a request, kindly execute this under Reports->New Query Report. Status Changes: SELECT wo.WORKORDERID AS "Request ID", ...