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", TO_CHAR((wo.TIMESPENTONREQ / 1000 || ' second') :: interval, 'HH24:MI') AS "Time taken for resolution", pd.PRIORITYNAME AS "Priority", wo.IS_CATALOG_TEMPLATE AS "Is Service Request", mdd.MODENAME AS "Mode", std.STATUSNAME AS "Status", qd.QUEUENAME AS "Group", TO_CHAR((wo.ONHOLDTIME / 1000 || ' second') :: interval, 'HH24:MI') AS "Hold time", CASE WHEN COUNT(wgi.ASSESSMENTID) > 0 THEN COUNT(wgi.ASSESSMENTID) -1 ELSE 0 END AS "No. of Hops" FROM WorkOrder wo LEFT JOIN ModeDefinition mdd ON wo.MODEID = mdd.MODEID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID = wos.WORKORDERID LEFT JOIN StatusDefinition std ON wos.STATUSID = std.STATUSID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID = pd.PRIORITYID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID = woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID = qd.QUEUEID LEFT JOIN WO_Assessment wa ON wo.WORKORDERID = wa.WORKORDERID LEFT JOIN WO_Group_Info wgi ON wa.ASSESSMENTID = wgi.ASSESSMENTID WHERE (wo.ISPARENT = '1') AND TO_TIMESTAMP(wo.CREATEDTIME/ 1000.0) >= ('2024-02-01 00:01'::TIMESTAMP) AND TO_TIMESTAMP(wo.CREATEDTIME/ 1000.0) <= ('2024-02-20 00:01'::TIMESTAMP) GROUP BY wo.WORKORDERID, wo.CREATEDTIME, wo.RESPONDEDTIME, wo.RESOLVEDTIME, wo.TIMESPENTONREQ, pd.PRIORITYNAME, wo.IS_CATALOG_TEMPLATE, mdd.MODENAME, std.STATUSNAME, qd.QUEUENAME, wo.ONHOLDTIME
>> The required timestamp can be selected in the highlighted area. The same highlighted area can be replaced with the following based on the requirement
To retrieve data for the current week: wo.CREATEDTIME >= <from_thisweek> AND wo.CREATEDTIME <= <to_thisweek>
To retrieve data for the current month: wo.CREATEDTIME >= <from_thismonth> AND wo.CREATEDTIME <= <to_thismonth>
>> The query returns the below output:
Where,
>> Time taken for resolution: ticket resolved time - created time.
>> Hold Time: Time when the tickets were in "On Hold" status.
>> No. of Hops: Number of times a request was interchanged between support groups.
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 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 for request attachment details (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Requests with Attachment, its name and path SELECT ad.ORG_NAME AS "Account", wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", ti.FIRST_NAME AS "Technician", sa.ATTACHMENTNAME "Attachment ...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 ...Query to retrieve worklog details
Tested in: 14610 (Postgres) Query: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", sdo.NAME AS "Site", ti.FIRST_NAME AS "Request Technician" ,au1.FIRST_NAME AS "Worklog Technician", TO_CHAR(((sum(ct.TIMESPENT))/1000 || ' ...