Pending Request for more than 10 days
This report provides a quick view of all Pending incidents which are not closed for more than 10 days. When the incidents start backlogging faster than they can be resolved, the tendency of long incidents pending time can be exposed in this report. This information can be used by the IT Management team to monitor and report upon the efficiency and effectiveness of the Pending incidents. Service Management process managers can also use this report to develop and follow procedures to reduce the time to resolve incidents and eliminate backlogged incidents.
To make any changes to a query, refer to the KB article below.
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", DATEDIFF(DAY, dateadd(s, datediff(s, GETUTCDATE(), getdate()) +(wo.createdtime/1000), '1970-01-01 00:00:00'), GETDATE()) "Days open" 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()) +(wo.createdtime/1000), '1970-01-01 00:00:00'), GETDATE()) > 10 ORDER BY 8
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", extract(epoch FROM(now()::TIMESTAMP - to_timestamp(wo.createdtime/1000)::TIMESTAMP))/3600/24 "Days open" 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.createdtime/1000)::TIMESTAMP))/3600/24 > 10 ORDER BY 8
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 ADSelfService Plus?
Related Articles
How to email pending requests list to technician periodically
This post describes the use of a python script to email pending requests to each technician in a periodic interval using Custom Schedules.This showcases the use of a feature called 'Custom Schedules' that was released in the build 9300 of ServiceDesk ...How to send periodic notifications to requester(s) about their pending requests and accordingly update their status.
This post describes the use of a python script to notify requester(s) about their requests through email and move those to a specific status. Use Case: Let us assume a scenario where the technicians reply to a request and put it in a custom status ...Script to Auto close Request when the status is Resolved for a particular number of days
For version 11 and above: -> In version 11, we only have the option to set the request Auto-close for a max of 10 days -> The below script will help you to extend the auto-closure time Execution steps: 1. Go to Reports--> New Query Report and run the ...Close requests with pending approval for more than 10 days
Requirement: Close older requests with pending approvals. UseCase: Close all requests for which approvals have been sent but no action has been taken in more than 10 days. Please follow the below steps. Goto Reports > New Query Report > execute ...Query that lists pending approval of a particular service approver (PGSQL)
Doesnt work in PGSQL (14300) ERROR: relation "workorder_threaded" does not exist SELECT wo.WORKORDERID "Request ID",aau.FIRST_NAME "Requester",wo.TITLE "Subject",ti.FIRST_NAME "Technician", longtodate(wo.CREATEDTIME) "Created Time",appsau.first_name ...