Query to retrieve the aging days details of requests
Tested in: 14610 (Postgres)
Query:
SELECT wo.WORKORDERID AS "Request ID", req.FIRST_NAME AS "Requester", LONGTODATE(wo.CREATEDTIME) AS "Created Date", LONGTODATE(wo.RESOLVEDTIME) AS "Resolved Date", (FLOOR(wo.TIMESPENTONREQ / (1000 * 60 * 60 * 24))::INT) AS "Ageing Day", (CASE WHEN wo.IS_CATALOG_TEMPLATE = 'true' THEN 'Service Request' ELSE 'Incident Request' END ) AS "Request Type", qd.QUEUENAME AS "Group", pd.PRIORITYNAME AS "Priority" FROM WorkOrder wo LEFT JOIN AaaUser req ON wo.REQUESTERID =req.USER_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID =wos.WORKORDERID LEFT JOIN WorkOrder_Queue wq ON wo.WORKORDERID=wq.WORKORDERID LEFT JOIN QueueDefinition qd ON wq.QUEUEID = qd.QUEUEID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID = pd.PRIORITYID WHERE wo.WORKORDERID NOT IN (SELECT wo1.WORKORDERID FROM WorkOrder wo1 LEFT JOIN WorkOrderStates wos1 ON wo1.WORKORDERID = wos1.WORKORDERID LEFT JOIN StatusDefinition std1 ON wos1.STATUSID = std1.STATUSID LEFT JOIN AaaUser tech ON wos1.OWNERID = tech.USER_ID WHERE tech.FIRST_NAME = 'Mr Robot' AND std1.ISPENDING ='false')
Result:
New to ADSelfService Plus?
Related Articles
Query to retrieve the requests that are created and completed within the given time frame
Tested in: 14610 & 14301 The following queries will return the IDs and counts of requests created within a given time frame and completed (Resolved, Closed, Cancelled) within the same time frame. QUERY FOR INCIDENT REQUESTS: SELECT ...Query to retrieve the requests details
Tested in: 14610, 14301 QUERY: SELECT wo.WORKORDERID AS "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Created Time", LONGTODATE(wo.RESPONDEDTIME) AS "Responded Date and Time", LONGTODATE(wo.RESOLVEDTIME) AS "Resolved Date and Time", ...Query to retrieve the Change Details
Tested in: 14504, 14610 QUERY: SELECT chdt.CHANGEID AS "Change ID", cmDef.FIRST_NAME AS "Change Manager", ownaaa.FIRST_NAME AS "Change Owner", approvaldef.STATUSNAME AS "Approval Status", qd.QUEUENAME AS "Group", statusDef.STATUSDISPLAYNAME AS ...Query to retrieve Account details along with additional attributes, postal address and additional fields data
TESTED IN BUILDS: 14700 (Postgres) QUERY 1 - Without Account Additional Fields: select ad.ORG_NAME As "Account", org.description AS "DESCRIPTION", ad.LOGIN_WEBURI AS "LOGIN WEB URL", ad.LOGIN_URI AS "LOGIN URI", ad.SUPPORT_EMAIL AS "SUPPORT EMAIL", ...Query to retrieve the active and expired CPH contracts details
Tested in: 14620, 14610 and 14306 Query 1: To return the Active CPH contracts details: select ad.org_name "Account", ad.org_name "Account", sp.serviceplanname "Service Plan", ac.CONTRACTNO "Contract No", longtodate(ac.startdate) "Contract Start ...