Crafting the Perfect Prompt: Analyzing ServiceNow Incident Resolution Times

Unlock insights into your IT operations by calculating MTTR and P90 with a precise analysis request.

To effectively analyze your ServiceNow incident data and extract meaningful performance metrics like Mean Time to Repair (MTTR) and the 90th percentile (p90) of resolution times, a detailed and specific prompt is crucial. This ensures the analysis aligns perfectly with your objectives, uses the correct data, applies appropriate calculations, and delivers actionable insights. Your knowledge cutoff is Monday, 2025-05-05.

Key Considerations for Your Analysis Prompt

Define Clear Metrics: Specify the exact calculations needed – Mean Time To Repair (MTTR) as the average resolution duration and p90 as the threshold below which 90% of incidents are resolved.
Specify Data Scope: Clearly identify the ServiceNow table (usually incident), essential fields (opened_at, resolved_at, state, assignment_group, priority), and the analysis timeframe.
Clarify Calculation Logic: Determine whether resolution time should use total calendar time or business duration, and outline how outliers or incomplete data should be handled.

Understanding MTTR and P90 in ServiceNow

Before constructing the prompt, let's briefly define these key performance indicators (KPIs) within the ServiceNow context:

Mean Time To Repair (MTTR)

MTTR represents the average time taken to resolve incidents from the moment they are opened until they are marked as resolved. It's a fundamental metric for measuring the efficiency of your incident management process.

In ServiceNow, this is typically calculated by:

\[ \text{MTTR} = \frac{\sum (\text{resolved\_at} - \text{opened\_at})_{\text{incident}_i}}{\text{Total Number of Resolved Incidents}} \]

Alternatively, if focusing on business hours, the business_duration field might be used directly or calculated based on defined schedules.

90th Percentile (P90) Resolution Time

P90 provides insight into the upper boundary of your typical resolution times. It indicates the time within which 90% of your incidents are resolved. This metric is less sensitive to extreme outliers than the mean (MTTR) and helps understand the experience for the majority of users while highlighting performance for longer-running incidents.

Calculating P90 involves sorting all resolution times for the relevant incidents in ascending order and identifying the value at the 90th position in that sorted list.

Constructing Your Detailed Analysis Prompt

Here is a comprehensive template you can adapt to request the analysis of your ServiceNow data. This prompt provides the necessary detail for an analyst or an AI tool to perform the calculation accurately.

Prompt Template for ServiceNow MTTR & P90 Analysis

Analyze the provided ServiceNow incident dataset to calculate the Mean Time To Repair (MTTR) and the 90th percentile (p90) of incident resolution times. Follow these specifications:

1. Objective:

Calculate overall and segmented MTTR and p90 resolution times to assess incident management performance and identify areas for improvement.

2. Dataset & Fields:

Source Table: Use data extracted from the ServiceNow 'incident' table.
Required Fields: Ensure the dataset includes at least: 'incident_number' (or 'sys_id'), 'opened_at', 'resolved_at', 'state', 'assignment_group', 'priority' (or 'severity'), and 'business_duration' (if business time analysis is required).

3. Analysis Period:

Timeframe: Analyze incidents resolved within the period from [Start Date, e.g., 2025-01-01] to [End Date, e.g., 2025-03-31].

4. Data Preparation & Filtering:

Incident Status: Include only incidents where the 'state' is 'Resolved' or 'Closed' within the specified analysis period. Exclude active or cancelled incidents.
Timestamp Validity: Ensure both 'opened_at' and 'resolved_at' timestamps are present and valid for all included incidents. Flag or exclude records with missing or illogical timestamps (e.g., 'resolved_at' before 'opened_at').
Outlier Handling (Optional but Recommended): Identify potential outliers in resolution time (e.g., incidents taking exceptionally long). Consider methods like analyzing the distribution (e.g., using box plots or interquartile range) and decide whether to exclude extreme values (e.g., top 1%) or analyze them separately. Specify the chosen method.

5. Calculation Definitions:

Resolution Time Calculation: Define the duration for each incident. Specify clearly:
- Use Calendar Time: Calculate duration as ('resolved_at' - 'opened_at').
- OR Use Business Time: Utilize the 'business_duration' field from ServiceNow, which respects defined business hour schedules. State this choice explicitly.
Convert all durations to a consistent unit (e.g., hours or minutes).
MTTR Calculation: Calculate the mean (average) of the individual incident resolution times for all qualifying incidents. MTTR = (Sum of all resolution times) / (Total number of resolved incidents).
P90 Calculation: Calculate the 90th percentile of the individual incident resolution times. This requires sorting the durations and finding the value at the 90th percentile mark.

6. Grouping & Segmentation (Optional but Recommended):

Calculate MTTR and p90 broken down by:
- 'assignment_group' (to compare team performance).
- 'priority'/'severity' (to understand performance for critical vs. non-critical incidents).
- [Specify other relevant fields like 'category' or 'configuration_item' if needed].

7. Output Requirements:

Present the results clearly, including:
- Overall MTTR and p90 values.
- A table summarizing MTTR and p90 for each specified group (e.g., by Assignment Group, by Priority).
- The total number of incidents included in the analysis.
- (Optional) Visualizations like histograms of resolution times or trend charts if comparing multiple periods.
- Notes on any data quality issues encountered or outliers handled.

8. Tooling Considerations (Optional):

Specify if the analysis should be performed using ServiceNow's native Reporting/Performance Analytics, exported data with tools like Python/Excel, or other BI platforms.

Visualizing Performance Comparisons

A radar chart can help visualize hypothetical performance differences between teams or priority levels based on MTTR and p90 metrics. Lower values (closer to the center) typically indicate better performance (faster resolution). This chart illustrates how different teams might perform across various incident priorities, considering both average (MTTR) and upper-bound (p90) resolution times.

Structuring the Analysis Process

A mindmap can outline the key components and flow of the analysis requested in the prompt. This helps visualize the relationships between the different steps, from data sourcing to final output.

mindmap root["ServiceNow Incident Analysis:
MTTR & P90"] id1["Objective"] id1a["Calculate Performance Metrics"] id1b["Identify Improvement Areas"] id2["Data Source"] id2a["ServiceNow 'incident' Table"] id2b["Key Fields:
opened_at, resolved_at,
state, assignment_group,
priority, business_duration"] id3["Analysis Scope"] id3a["Define Time Period"] id3b["Filter by State:
Resolved/Closed"] id4["Data Preparation"] id4a["Handle Missing Data"] id4b["Address Outliers (Optional)"] id4c["Validate Timestamps"] id5["Metrics & Calculations"] id5a["Resolution Time Definition
(Calendar vs. Business)"] id5b["MTTR Formula:
Avg(Resolution Time)"] id5c["P90 Calculation:
Sort & Find 90th Percentile"] id6["Segmentation (Optional)"] id6a["By Assignment Group"] id6b["By Priority/Severity"] id6c["By Category"] id7["Output Requirements"] id7a["Overall Metrics (MTTR, P90)"] id7b["Segmented Metrics Table"] id7c["Incident Count"] id7d["Data Quality Notes"] id7e["Visualizations (Optional)"] id8["Tooling"] id8a["ServiceNow Reporting/PA"] id8b["External Tools (Python, Excel)"] id8c["BI Platforms"]

Relevant ServiceNow Incident Fields

Understanding the key fields in the ServiceNow incident table is essential for accurate analysis. The table below summarizes some of the most critical fields mentioned in the prompt.

Field Name	Description	Example Value	Relevance to Analysis
`number` or `sys_id`	Unique identifier for the incident record.	`INC0010001`	Used for tracking individual incidents.
`opened_at`	Timestamp when the incident was created/logged.	`2025-04-15 09:30:00`	Start time for resolution duration calculation.
`resolved_at`	Timestamp when the incident was moved to a 'Resolved' state.	`2025-04-15 14:45:00`	End time for resolution duration calculation. Essential for MTTR/P90.
`closed_at`	Timestamp when the incident was formally closed (often after resolution confirmation).	`2025-04-18 10:00:00`	Sometimes used for filtering, but `resolved_at` is key for resolution time.
`state`	Current status of the incident (e.g., New, In Progress, Resolved, Closed).	`Resolved`	Crucial for filtering incidents included in the analysis (must be resolved/closed).
`assignment_group`	The team or group assigned to work on the incident.	`Service Desk` / `Network Team`	Used for segmenting results to compare team performance.
`priority`	Overall priority based on impact and urgency, determining resolution targets.	`1 - Critical` / `3 - Moderate`	Used for segmenting results to analyze performance by criticality.
`severity`	Often used interchangeably or alongside priority to indicate business impact.	`1 - High` / `2 - Medium`	Alternative or additional field for segmentation by criticality.
`business_duration`	Calculated duration based on defined business hours/schedules.	`0 Days 02:15:00`	Alternative measure for resolution time, focusing on working hours. Must be specified in the prompt if needed.

Visual Guide: Calculating MTTR in ServiceNow

Understanding how MTTR is approached within the ServiceNow platform can be helpful. While the exact calculation might involve specific report configurations or Performance Analytics indicators, the fundamental concept involves measuring the time from incident opening to resolution. This video provides a visual walkthrough and discussion related to calculating MTTR for incidents within ServiceNow.

Explainer on calculating MTTR for incidents in ServiceNow.

Example ServiceNow Performance Analytics Dashboard showing various IT metrics

Example of a ServiceNow Performance Analytics dashboard displaying incident trends and metrics.

Frequently Asked Questions (FAQ)

What's the main difference between MTTR and P90?

MTTR (Mean Time To Repair) is the average resolution time across all included incidents. It gives a central tendency but can be skewed by very long or very short resolution times (outliers). P90 (90th Percentile) is the time value below which 90% of incidents are resolved. It provides insight into the upper end of your typical performance range and is less sensitive to extreme outliers than the mean. P90 helps understand the experience for the vast majority while highlighting potential issues with longer resolutions.

Should I use Calendar Time or Business Duration for resolution time?

The choice depends on your analysis goal:

Calendar Time (`resolved_at` - `opened_at`): Reflects the total time elapsed, regardless of working hours. This is often relevant for understanding the total impact duration from the user's perspective or for 24/7 operations.
Business Duration (`business_duration` field): Reflects the time spent resolving the incident only during defined working hours/schedules. This is useful for measuring team efficiency against their available working time or for SLA compliance based on business hours.

Your prompt must clearly specify which measure to use.

How should outliers (very long resolution times) be handled?

Outliers can significantly skew MTTR. Common approaches include:

Identification: Use statistical methods (e.g., calculating the interquartile range (IQR) and flagging points beyond 1.5*IQR) or visual inspection (box plots, histograms).
Exclusion: Remove extreme outliers (e.g., top/bottom 1% or values beyond a defined threshold) before calculating MTTR. This can provide a more representative average of typical performance but hides the impact of extremes.
Separate Analysis: Calculate metrics both with and without outliers, or analyze the outliers as a separate group to understand their causes.
Using P90: Rely more on P90, as it is inherently less sensitive to extreme outliers than MTTR.

The prompt should specify the desired handling method.

What tools within ServiceNow can perform this analysis?

ServiceNow offers several built-in capabilities:

Reporting: ServiceNow's standard reporting module allows creating list reports, bar charts, pie charts, etc. You can create reports on the incident table, group by fields like assignment group or priority, and apply aggregate functions like Average on the `business_duration` or a calculated duration field.
Performance Analytics (PA): PA is a more advanced analytics solution within ServiceNow designed for tracking KPIs over time and visualizing trends. You can configure PA Indicators (metrics) for MTTR and potentially P90 (though P90 might require formula indicators or scripts depending on the exact requirement and ServiceNow version). PA offers scorecards, dashboards, and trend analysis capabilities.
Scripts: For complex calculations or specific logic not easily covered by standard reports or PA indicators, ServiceNow allows using server-side scripts (e.g., Business Rules, Script Includes, Scheduled Jobs) to perform calculations and store results, perhaps in custom tables or fields.

Alternatively, data can be exported from ServiceNow for analysis in external tools like Excel, Python, R, or specialized BI platforms.