Request created in out of business hours

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.


MSSQL

SELECT wo.WORKORDERID "Request ID",
       aau.FIRST_NAME "Requester",
       cd.CATEGORYNAME "Category",
       wo.TITLE "Subject",
       qd.QUEUENAME "Group",
       sdo.NAME "Site",
       LONGTODATE(wo.CREATEDTIME) "Created Time",
       datepart(dw, dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo.createdtime/1000), '1970-01-01 00:00:00')) "Day" FROM WorkOrder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
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 CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID
WHERE wo.ISPARENT='1'
  AND ((HOUR(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo.CREATEDTIME/1000), '1970-01-01 00:00:00')) < 9
        OR (HOUR(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo.CREATEDTIME/1000), '1970-01-01 00:00:00')) > 17
            AND datepart(dw, dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo.createdtime/1000), '1970-01-01 00:00:00')) IN (2,3,4,5,6)))
       OR datepart(dw, dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo.createdtime/1000), '1970-01-01 00:00:00')) IN (1,7))

PGSQL


SELECT wo.workorderid "Request ID",
       aau.FIRST_NAME "Requester",
       cd.CATEGORYNAME "Category",
       wo.TITLE "Subject",
       qd.QUEUENAME "Group",
       sdo.NAME "Site",
       Longtodate(wo.CREATEDTIME) "Created Time" FROM WorkOrder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
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 CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID
WHERE wo.ISPARENT='1'
  AND ((extract(HOUR
                FROM to_timestamp(wo.createdtime/1000)::TIMESTAMP) < 9
        OR extract(HOUR
                   FROM to_timestamp(wo.createdtime/1000)::TIMESTAMP) > 18)
       AND extract(dow
                   FROM to_timestamp(wo.createdtime/1000)::TIMESTAMP) IN (1,2,3,4,5))
  OR extract(dow
             FROM to_timestamp(wo.createdtime/1000)::TIMESTAMP) IN (6,7)


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 Products