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.
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" 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 ADSelfService Plus?
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", ...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 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", ...Update Requester from Request Description
Use case: When a request is submitted on behalf of a user via email, the request description contains the requester's full name. Script to update the ticket requester accordingly. Tested on builds: 14306,14500. Steps to follow: Go to Admin > Request ...