Request not updated for 24 hours

Request not updated for 24 hours

This report is used to find the pending request which are not updated for more than 24 hours. This report helps to find the reason why the request is pending and find out and analyze the root cause of the process and define actions to improve the service.


MSSQL

SELECT wo.WORKORDERID"Request ID",
       qd.QUEUENAME "Group",
       aau.FIRST_NAME "Requester",
       wo.TITLE "Subject",
       ti.FIRST_NAME "Technician",
       sdo.NAME "Site",
       LONGTODATE(wo.CREATEDTIME) "Created Time" FROM WorkOrder wo
LEFT JOIN ModeDefinition mdd ON wo.MODEID=mdd.MODEID
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID
LEFT JOIN WorkOrderToDescription wotodesc ON wo.WORKORDERID=wotodesc.WORKORDERID
LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID
LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
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 StatusDefinition std ON wos.STATUSID=std.STATUSID
WHERE (std.ispending = '1')
  AND wo.ISPARENT='1'
  AND DATEDIFF(DAY, dateadd(s, datediff(s, GETUTCDATE(), getdate()) +(wos.last_tech_update/1000), '1970-01-01 00:00:00'), GETDATE()) > 1
ORDER BY 1

PGSQL

SELECT wo.WORKORDERID"Request ID",
       qd.QUEUENAME "Group",
       aau.FIRST_NAME "Requester",
       wo.TITLE "Subject",
       ti.FIRST_NAME "Technician",
       sdo.NAME "Site",
       LONGTODATE(wo.CREATEDTIME) "Created Time" FROM WorkOrder wo
LEFT JOIN ModeDefinition mdd ON wo.MODEID=mdd.MODEID
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID
LEFT JOIN WorkOrderToDescription wotodesc ON wo.WORKORDERID=wotodesc.WORKORDERID
LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID
LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
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 StatusDefinition std ON wos.STATUSID=std.STATUSID
WHERE (std.ispending = '1')
  AND wo.ISPARENT='1'
  AND extract(epoch
              FROM(now()::TIMESTAMP - to_timestamp(wo.last_tech_update/1000)::TIMESTAMP))/3600/24 > 1
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 ADManager Plus?

                  New to ADSelfService Plus?

                    • Related Articles

                    • Request created in out of business hours

                      This report is used to find the request created out of business hours. Based on this report, resources can be allocated to manage the load. To make any changes to a query, refer to the KB article below. ...
                    • Request First Assigned time

                      First Assigned Time is the number of minutes, hours, or days between when a customer submits a support ticket and when was a support representative assigned to the Request. It indicates how long it is in an unassigned state.  To make any changes to a ...
                    • 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 ...
                    • Set due by time in a request with value from a date time additional field - Deluge

                      This custom function script is used to set the due by date in the request based on the value that is set in a date additional field.  This is performed using APIs for updating a request. UseCase:  Request due by time will be set based on Incident / ...
                    • Update schedule start and end time for a change request

                      Requirement: When a Change request is submitted, the schedule start date must be set to current date and completion date to be set 10days after the current date  This must be reflected in the created change request. Steps to follow: Goto Admin > ...