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
Average Resolution Time based on Group
This report is used for the customers that support groups provide a consistent level of support. It can be confusing and frustrating for customers to have some support groups resolve quickly, while other support groups takes days to resolve the ...Average Resolution Time based on Technicians
This report is used for the customers that Technicians provide a consistent level of support. It can be confusing and frustrating for customers to have some Technicians resolve quickly, while other Technicians takes days to resolve the issues and ...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", ...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 ...