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

                    • Close the request upon adding a note.

                      Kindly follow the steps mentioned below: 1) Go to Admin >> Developer Space >> Custom Function >> Global Functions >> Copy & Paste the attached GF_Content.txt and save it with a name. Update the URL and technician key as per your instance. Refer to ...

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

                    • Query to show tickets created based on shift time ( PGSQL )

                      Tested in build PGSQL (14300) Filter mentioned by converting into minutes. PGSQL: 7.30 AM - 7.30 PM SELECT wo.WORKORDERID AS "Request ID", pd.PRIORITYNAME AS "Priority", std.STATUSNAME AS "Request Status", LONGTODATE(wo.CREATEDTIME) AS "Created ...

                      Related Products