Time elapsed analysis

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 M365 Manager Plus?
        New to M365 Manager Plus?
          New to RecoveryManager Plus?
          New to RecoveryManager Plus?
            New to Exchange Reporter Plus?
            New to Exchange Reporter Plus?
              New to SharePoint Manager Plus?
              New to SharePoint Manager Plus?
                  See all integrations
                  New to ADManager Plus?

                    New to ADSelfService Plus?

                    Start your free trial

                      • 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 ...

                      • Project Time spent

                        MSSQL SELECT pr.ProjectID "Project ID", pr.TITLE "Project Title", taskdet.TaskID "TaskID", taskdet.TITLE "Title", tkd.description "Task Description", taskowner.FIRST_NAME "Owner", dateadd(s, datediff(s, GETUTCDATE(), getdate()) + ...

                      • Problem time spent

                        SELECT prob.PROBLEMID "Problem ID", prob.TITLE "Title", catadef.CATEGORYNAME "Category", LONGTODATE(prob.REPORTEDTIME) "Reported Date", ownaaa.FIRST_NAME "Technician", priodef.PRIORITYNAME "Priority", statdef.STATUSNAME "Status", ...

                      • Request Time Analysis Reports (Status, Group, Technician Fields changes and its timespent)

                        This report contains the time analysis for each status/group/technician changes in a request. For builds lower than 11122. Status Changes: SELECT wo.WORKORDERID AS "Request ID", sisd.STATUSNAME as "Changed From",sinsd.STATUSNAME as "Changed ...

                      • Average Resolution Time based on Group

                        This report is used for the customers that support groups provide a consistent level of support. It can be confusing and frustrating for customers to have some support groups resolve quickly, while other support groups takes days to resolve the ...

                        Related Products