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

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 To",longtodate(si.ENDTIME) as "Changed Time",si.TIMESPENT as "TimeSpent in Previous Status(min)" FROM WorkOrder wo
   LEFT JOIN WO_Assessment woas ON wo.WORKORDERID = woas.WORKORDERID
   LEFT JOIN WO_Status_Info si ON woas.ASSESSMENTID = si.ASSESSMENTID
   LEFT JOIN StatusDefinition sisd ON si.STATUSID = sisd.STATUSID
   LEFT JOIN StatusDefinition sinsd ON si.NEXTSTATUSID = sinsd.STATUSID
WHERE
   (
      wo.ISPARENT = '1'
      and si.NEXTSTATUSID IS Not Null
   )
ORDER BY
   1,
   woas.ASSESSMENTID asc

Group Changes:

SELECT wo.WORKORDERID AS "Request ID", q.queuename as "Changed From",qq.queuename as "Changed To",longtodate(woq.ENDTIME) as "Changed Time",woq.TIMESPENT as "TimeSpent by Previous Group(min)" FROM WorkOrder wo
   LEFT JOIN WO_Assessment woas ON wo.WORKORDERID = woas.WORKORDERID
   LEFT JOIN WO_group_Info woq ON woas.ASSESSMENTID = woq.ASSESSMENTID
   LEFT JOIN queuedefinition q ON woq.GROUPID = q.QUEUEID
   LEFT JOIN queuedefinition qq ON woq.NEXTGROUPID = qq.QUEUEID
WHERE
   (
      wo.ISPARENT = '1'
      and woq.NEXTGROUPID IS Not Null
   )
ORDER BY
   1,
   woas.ASSESSMENTID asc

Technician Changes:

SELECT wo.WORKORDERID AS "Request ID", a.first_name as "Changed From",aa.first_name as "Changed To",longtodate(wot.ENDTIME) as "Changed Time",wot.TIMESPENT as "TimeSpent by Previous Technician(min)" FROM WorkOrder wo
   LEFT JOIN WO_Assessment woas ON wo.WORKORDERID = woas.WORKORDERID
   LEFT JOIN WO_Tech_Info wot ON woas.ASSESSMENTID = wot.ASSESSMENTID
   LEFT JOIN AAAUser a ON wot.TECHNICIANID = a.USER_ID
   LEFT JOIN AAAUser aa ON wot.NEXTTECHNICIANID = aa.USER_ID
WHERE
   (
      wo.ISPARENT = '1'
      and wot.NEXTTECHNICIANID IS Not Null
   )
ORDER BY
   1,
   woas.ASSESSMENTID asc


For build 11122 and above.

PGSQL


Status Changes:

SELECT wo.WORKORDERID AS "Request ID", sisd.STATUSNAME as "Status Changed From",sinsd.STATUSNAME as "Status Changed To",longtodate(si.ENDTIME) as "Status Changed Time",
(concat( floor((si.TIMESPENT/1000)/60/60/24) , ' : ' ,  floor((si.TIMESPENT/1000)/60/60%24) , ' : ' , (floor((si.TIMESPENT/1000)/60)::numeric%60) , ' : ' , floor(si.TIMESPENT/1000)::numeric%60)) as "TimeSpent in Previous Status in Days:HH:MM:SS" FROM WorkOrder wo
   LEFT JOIN WO_Assessment woas ON wo.WORKORDERID = woas.WORKORDERID
   LEFT JOIN WO_Status_Info si ON woas.ASSESSMENTID = si.ASSESSMENTID
   LEFT JOIN StatusDefinition sisd ON si.STATUSID = sisd.STATUSID
   LEFT JOIN StatusDefinition sinsd ON si.NEXTSTATUSID = sinsd.STATUSID
WHERE
   (
      wo.ISPARENT = '1'
      and si.NEXTSTATUSID IS Not Null
   )
ORDER BY
   1,
   woas.ASSESSMENTID asc

Group Changes:

SELECT wo.WORKORDERID AS "Request ID", q.queuename as "Group Changed From",qq.queuename as "Group Changed To",longtodate(gi.ENDTIME) as "Group Changed Time", (concat( floor((gi.TIMESPENT/1000)/60/60/24) , ' : ' ,  floor((gi.TIMESPENT/1000)/60/60%24) , ' : ' , (floor((gi.TIMESPENT/1000)/60)::numeric%60) , ' : ' , floor(gi.TIMESPENT/1000)::numeric%60)) as "TimeSpent in Previous Group in Days:HH:MM:SS" FROM WorkOrder wo
   LEFT JOIN WO_Assessment woas ON wo.WORKORDERID = woas.WORKORDERID
   LEFT JOIN WO_group_Info gi ON woas.ASSESSMENTID = gi.ASSESSMENTID
   LEFT JOIN queuedefinition q ON gi.GROUPID = q.QUEUEID
   LEFT JOIN queuedefinition qq ON gi.NEXTGROUPID = qq.QUEUEID
WHERE
   (
      wo.ISPARENT = '1'
      and gi.NEXTGROUPID IS Not Null
   )
ORDER BY
   1,
   woas.ASSESSMENTID asc

Technician Changes:

SELECT wo.WORKORDERID AS "Request ID", a.first_name as "Technician Changed From",aa.first_name as "Technician Changed To",longtodate(ti.ENDTIME) as "Technician Changed Time",(concat( floor((ti.TIMESPENT/1000)/60/60/24) , ' : ' ,  floor((ti.TIMESPENT/1000)/60/60%24) , ' : ' , (floor((ti.TIMESPENT/1000)/60)::numeric%60) , ' : ' , floor(ti.TIMESPENT/1000)::numeric%60)) as "TimeSpent in Previous Technician in Days:HH:MM:SS" FROM WorkOrder wo
   LEFT JOIN WO_Assessment woas ON wo.WORKORDERID = woas.WORKORDERID
   LEFT JOIN WO_Tech_Info ti ON woas.ASSESSMENTID = ti.ASSESSMENTID
   LEFT JOIN AAAUser a ON ti.TECHNICIANID = a.USER_ID
   LEFT JOIN AAAUser aa ON ti.NEXTTECHNICIANID = aa.USER_ID
WHERE
   (
      wo.ISPARENT = '1'
      and ti.NEXTTECHNICIANID IS Not Null
   )
ORDER BY
   1,
   woas.ASSESSMENTID asc

MSSQL 


Status Changes:

SELECT wo.WORKORDERID AS "Request ID", sisd.STATUSNAME as "Status Changed From",sinsd.STATUSNAME as "Status Changed To",longtodate(si.ENDTIME) as "Status Changed Time",
(CONVERT(varchar, (si.TIMESPENT/1000)/86400) + ':' + CONVERT(varchar, DATEADD(ms, ((si.TIMESPENT/1000)%86400)*1000,0),108)) as "TimeSpent in Previous Status in Days:HH:MM:SS" FROM WorkOrder wo
   LEFT JOIN WO_Assessment woas ON wo.WORKORDERID = woas.WORKORDERID
   LEFT JOIN WO_Status_Info si ON woas.ASSESSMENTID = si.ASSESSMENTID
   LEFT JOIN StatusDefinition sisd ON si.STATUSID = sisd.STATUSID
   LEFT JOIN StatusDefinition sinsd ON si.NEXTSTATUSID = sinsd.STATUSID
WHERE
   (
      wo.ISPARENT = '1'
      and si.NEXTSTATUSID IS Not Null
   )
ORDER BY
   1,
   woas.ASSESSMENTID asc

Group Changes:

SELECT wo.WORKORDERID AS "Request ID", q.queuename as "Group Changed From",qq.queuename as "Group Changed To",longtodate(gi.ENDTIME) as "Group Changed Time", 
(CONVERT(varchar, (gi.TIMESPENT/1000)/86400) + ':' + CONVERT(varchar, DATEADD(ms, ((gi.TIMESPENT/1000)%86400)*1000,0),108)) as "TimeSpent in Previous Group in Days:HH:MM:SS" FROM WorkOrder wo
   LEFT JOIN WO_Assessment woas ON wo.WORKORDERID = woas.WORKORDERID
   LEFT JOIN WO_group_Info gi ON woas.ASSESSMENTID = gi.ASSESSMENTID
   LEFT JOIN queuedefinition q ON gi.GROUPID = q.QUEUEID
   LEFT JOIN queuedefinition qq ON gi.NEXTGROUPID = qq.QUEUEID
WHERE
   (
      wo.ISPARENT = '1'
      and gi.NEXTGROUPID IS Not Null
   )
ORDER BY
   1,
   woas.ASSESSMENTID asc

Technician Changes:

SELECT wo.WORKORDERID AS "Request ID", a.first_name as "Technician Changed From",aa.first_name as "Technician Changed To",longtodate(ti.ENDTIME) as "Technician Changed Time",
(CONVERT(varchar, (ti.TIMESPENT/1000)/86400) + ':' + CONVERT(varchar, DATEADD(ms, ((ti.TIMESPENT/1000)%86400)*1000,0),108)) as "TimeSpent in Previous Technician in Days:HH:MM:SS" FROM WorkOrder wo
   LEFT JOIN WO_Assessment woas ON wo.WORKORDERID = woas.WORKORDERID
   LEFT JOIN WO_Tech_Info ti ON woas.ASSESSMENTID = ti.ASSESSMENTID
   LEFT JOIN AAAUser a ON ti.TECHNICIANID = a.USER_ID
   LEFT JOIN AAAUser aa ON ti.NEXTTECHNICIANID = aa.USER_ID
WHERE
   (
      wo.ISPARENT = '1'
      and ti.NEXTTECHNICIANID IS Not Null
   )
ORDER BY
   1,
   woas.ASSESSMENTID asc



        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

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

                      • Time Spent on each module

                        This Report helps us to know the time spent by technicians on each module.  The Time spent report provides a step-by-step record by which time-related data can be traced to their source and provides a complete history of all Technician activities ...

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

                      • Request violated by technician

                        This report used to find the technician who violated the request. If the request/incident already has a violation and is reassigned to another technician the new technician assumes the violation instead of the technician that the violation occurred.  ...

                      • Technician group

                        This report is used to get the associated groups of the technicians. SELECT AaaUser.FIRST_NAME "FullName", (SDOrganization.NAME) "Site", (qd.queuename) "Group" FROM AaaUser LEFT JOIN UserDepartment ON AaaUser.USER_ID=UserDepartment.USERID LEFT JOIN ...

                        Related Products