Approvals
Query to get the MTTR (Mean Time To Respond & Mean Time To Resolve) reports
MTT Response Report is calculated as below, >> Response Time: Ticket Responded Time - Ticket Created Time >> MTT Response: Average total response time within a given time frame = Total Response Time within a given time frame (Divided by) Number of ...
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 get list of technicians who haven't logged in to the application for last x days.(PGSQL)
Compatible from builds above 10600 Last Tested in builds PGSQL (14300) QUERY: SELECT AaaAccount.ACCOUNT_ID as "Login Account ID", AaaUser.FIRST_NAME "Technician Name ( First Name )", max(AaaLogin.NAME) "Login Name", ...
Query report to get the successfully scanned assets (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Query report to get the successfully scanned assets with audit status, state , Serial no and site. SELECT max(resource.resourcename) "Asset Name", max(resource.SERIALNO) AS "Org Serial Number", ...
Query to fetch the fields in the templates (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) The query fetches all the fields with display name used across Request templates PQSQL: select Distinct (CASE when fc.field_name like 'udf_%' then ca.aliasname else fc.field_name END) "Fields", ...
Query to get approval status per request with approvers by account (MSSQL)
Tested in build MSSQL (14306) SELECT wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", wo.DESCRIPTION As "DESCRIPTION", aau.FIRST_NAME AS "Requester", dpt1.DEPTNAME AS " Requester Department", srep.First_Name As "Suprep", qd.QUEUENAME AS ...
Query to retrieve pending approvals from a technician account specific (MSSQL)
Tested in build MSSQL (14306) SELECT wo.WORKORDERID AS "Request ID", ad.org_name as "Account", aaa .first_name as "Technician", ApprovalStatusDefinition.STATUSNAME AS "Approval Status" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON ...
Query to show on hold status reason/comments
Working on Builds: 14500 and above Databases: PGSQL & MSSQL: SELECT wo.WORKORDERID AS "Request ID", CASE WHEN (wo.is_catalog_template) = 'false' THEN 'Incident' ELSE 'Service Request' END "Request Type",dpt.DEPTNAME AS "Department",pd.PRIORITYNAME AS ...
Query to show Approver details, sent, acted on time along with time taken to approve. ( PGSQL )
Tested in build PGSQL (14300) PGSQL: SELECT wo.WORKORDERID "Request ID", wo.TITLE "Subject", ti.FIRST_NAME "Technician", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", qd.QUEUENAME AS "Group", std.STATUSNAME "Request Status", ...
Query to show tickets first assigned today irrespective of created date
MSSQL: SELECT wo.WORKORDERID "Request ID", max(aau.FIRST_NAME) "Requester", max(dpt.DEPTNAME) "Department", max(wo.TITLE) "Subject", max(qd.QUEUENAME)"Group", max(ti.FIRST_NAME) "Technician", max(std.STATUSNAME) "Request Status", ...
Query report to get requestid,username ,email with created date ( MSSQL )
Tested in build MSSQL (14306) SELECT wo.WORKORDERID AS "Request ID", aaau.FIRST_NAME AS "Requester", aaaci.emailid,wo."TITLE" AS "Subject", "cd"."CATEGORYNAME" AS "Category", longtodate(wo.CREATEDTIME) AS "created time" FROM "WorkOrder" "wo" Left ...
Query reports to know the Ticket wise Approval status (MSSQL)
Tested in build MSSQL (14306) 1. Login to SDP MSP as administrator 2. Execute this from SDP MSP application -> Reports -> New Query report SELECT wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", srep.First_Name As "Suprep", qd.QUEUENAME AS ...
Query to show First response actioned by (MSSQL)
Tested in build MSSQL (14306) SELECT wo.WORKORDERID "Request ID", wo.TITLE "Subject", aau.FIRST_NAME "Requester", ti.FIRST_NAME "Technician", LONGTODATE(wo.CREATEDTIME) "Created Time", LONGTODATE(wo.RESPONDEDTIME) "Responded Date", sdu2.firstname ...
Query to show Approved tickets per Approver (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) PGSQL & MSSQL: SELECT wo.WORKORDERID "Request ID", wo.TITLE "Subject", std.STATUSNAME "Request Status", ti.FIRST_NAME "Technician", longtodate(wo.CREATEDTIME) "Created Time", ApprovalDetails.EMAIL ...
Time elapsed by each group in request ( MSSQL )
Tested in Build MSSQL (14306) Go to Reports-New Query Reports and execute the below query. SELECT wo.WORKORDERID AS "Request ID", mdd.MODENAME AS "Request Mode", qd.QUEUENAME AS "Current Group", aau.FIRST_NAME AS "Requester", ti.FIRST_NAME AS ...
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 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 ...