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

                    • Query to get the Request First Assigned time

                      Use case: First Assigned Time is the number of minutes, hours, or days between when a requester submits a request and when was a support representative assigned to the Request. It indicates how long it is in an unassigned state. Working on Build: ...

                    • Query to show last updated field (MSSQL & PGSQL)

                      Tested in Build PGSQL (14300) or MSSQL (14306) Kindly go to Reports-New Query Report and execute the below query. PGSQL: SELECT wo.WORKORDERID AS "Request ID", max(wo.TITLE) AS "Subject", max(ti.FIRST_NAME) AS "Assigned Technician", ...

                    • Query to list the request and its worklogs with the hours spent and its charges (MSSQL)

                      Tested in build MSSQL (14306) Use case The query will list the request details with the timespent, hours consumed and its charge, Sample Table DB : Poatgres and MSSQL Query SELECT ad.ORG_NAME AS "Account", ac.contractname "Contract Name", ...

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

                    • Query to show total time spent for a ticket ( PGSQL)

                      Tested in PGSQL build (14300) Database: PGSQL SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", wo.TITLE AS "Subject", rtdef.NAME AS "Request Type", ti.FIRST_NAME AS ...

                      Related Products