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. 



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

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