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 tickets created within the same time frame.
>> The same applies for the MTT Resolution Report.
QUERY FOR INCIDENT REQUESTS:
SELECT TO_CHAR((SUM(wo.RESPONSE_TIMESPENT)/COUNT(wo.WORKORDERID) / 1000 || ' second') :: interval, 'HH24:MI') AS "MTTR Response Incidents", TO_CHAR((SUM(wo.TIMESPENTONREQ)/COUNT(wo.WORKORDERID) / 1000 || ' second') :: interval, 'HH24:MI') AS "MTTR Resolution Incidents" FROM WorkOrder wo WHERE wo.IS_CATALOG_TEMPLATE ='false' 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)
QUERY FOR SERVICE REQUESTS:
SELECT TO_CHAR((SUM(wo.RESPONSE_TIMESPENT)/COUNT(wo.WORKORDERID) / 1000 || ' second') :: interval, 'HH24:MI') AS "MTTR Response Service Request", TO_CHAR((SUM(wo.TIMESPENTONREQ)/COUNT(wo.WORKORDERID) / 1000 || ' second') :: interval, 'HH24:MI') AS "MTTR Resolution Service Request" FROM WorkOrder wo WHERE wo.IS_CATALOG_TEMPLATE ='true' 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)
>> 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 queries returns the below columns:
New to ADSelfService Plus?
Related Articles
Query to show Average response time for Technicians
PGSQL: SELECT aau.first_name "Technician", TO_CHAR(((avg(wo.respondedtime)-avg(wo.createdtime))/1000 || ' second')::interval, 'HH24:MI:SS') "Avg response Time" FROM workorder wo LEFT JOIN workorderstates wos ON wo.workorderid = wos.workorderid LEFT ...Query to show Average resolution time for Category (PGSQL)
Tested in PGSQL build (14300) Databases: PGSQL SELECT accountdefinition.org_name "Account",cd.categoryname "Category", TO_CHAR(((avg(wo.resolvedtime)-avg(wo.createdtime))/1000 || ' second')::interval, 'HH24:MI:SS') "Avg resolution Time" FROM ...Query to get the Resolution overdue time and Response overdue time for each ticket.
Tested in 14620 Query: SELECT ad.ORG_NAME "Account", wo.WORKORDERID "Ticket ID", rtdef.NAME "Request Type", wo.RESOLVEDTIME "Resolved Time", pd.PRIORITYNAME "Priority", ( CASE WHEN wos.IS_FR_OVERDUE = '1' THEN ( CASE WHEN wo.RESPONDEDTIME = 0 THEN ...Query to show Average response time for Category (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL: SELECT accountdefinition.org_name "Account",cd.categoryname "Category", TO_CHAR(((avg(wo.respondedtime)-avg(wo.createdtime))/1000 || ' second')::interval, 'HH24:MI:SS') "Avg Response Time" FROM ...Query to retrieve response time in minutes
To return the ticket response time in minutes along with other request details. TESTED IN: Builds 14700 (Postgres) QUERY: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", ...