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?
                See all integrations
                New to ADManager Plus?

                  New to ADSelfService Plus?

                  Start your free trial
                  Start your free trial

                    • Related Articles

                    • Tickets created outside operational hours( MSSQL)

                      Tested in Build MSSQL (14306) MSSQL: SELECT "wo"."WORKORDERID" AS "Request ID", "mdd"."MODENAME" AS "Request Mode", "aau"."FIRST_NAME" AS "Requester", "dpt"."DEPTNAME" AS "Department", "cd"."CATEGORYNAME" AS "Category", "scd"."NAME" AS "Subcategory", ...

                    • Operational Hours and Differential Hours while adding a worklog

                      Under Self-Service portal settings, when you have the option "Show in work log differential hours based on SLA configuration" set to "Yes", the application will look for the SLA configuration and show the operational hours accordingly while adding a ...

                    • Notification in Business rules using Zoho Deluge script - > 10.6

                      In 10.6, we don't have a separate option to notify technicians under the Business rules option. However, we have introduced something called custom action where more actions could be accomplished. As for notifying a technician/somebody when a ...

                    • Business Rule is not working

                      There are a few configurations that need to be checked for business rules.  1. Account/Site Check the request Account & Site, Now, go to Admin>Business Rules>Filter the Account & Site and make sure the Business Rules is created for the same  2. Rule ...

                    • How to create service requests through email using Business Rule

                      Note: Template can be modified directly using BR from build 11.3 This post describes the use of a python script to create a service request through email based on keywords in the subject. When a request is created with specific keywords in the ...

                      Related Products