KQL Search

Query Details

HomeAI ToolsDevice Query

SLA Time To Respond

Query

# SLA Time To Respond

## Query Information

#### Description
The query below can be used to validate if the agreed SLA for time to respond is met by your analysts. The query used a datatable *SLA_Variables* that you can adjust to your time to responde (minutes).

This query is a basis and will return all closed incident and if they met the agreed SLA. Based on this query additional aggregation or the rendering of charts can be performed.

#### References
- https://github.com/Bert-JanP/Sentinel-Automation/tree/main/SLA%20Reporting%20Mail%20Report
- https://github.com/Bert-JanP/Sentinel-Automation/tree/main/Workbooks/SLA%20Reporting

## Sentinel
```KQL
// Prepare Data (Additonal Context)
let SLA_Variables = datatable (Severity: string, TimeToRespond: int, TimeToContainmentMinutes: int, UpdateIntervalMinutes: int)
[
    "High", 60, 1440, 60,
    "Medium", 120, 2880, 120,
    "Low", 240, 4320, 240,
    "Informational", 480, 10080, 480
];
// Collect Data
SecurityIncident
| where CreatedTime > ago(7d)
| where Status == "Closed"
// Prepare Data (Parsing)
| extend AssignedAnalyst = Owner.userPrincipalName
// Prepare Data (Standardize)
| extend StandardizedAssignedAnalyst = tolower(Owner.userPrincipalName)
| extend LocalIncidentCreationTime = datetime_local_to_utc(CreatedTime, 'Europe/Copenhagen')
// Prepare Data (Aggregation & Statistics)
| summarize arg_max(TimeGenerated, *) by IncidentNumber
// Prepare Data (Additonal Context)
| join kind=inner SLA_Variables on Severity
| extend TimeToRespondToIncident = datetime_diff('minute', FirstModifiedTime, CreatedTime)
| extend MetSLA = TimeToRespondToIncident <= TimeToRespond
| where isnotempty(MetSLA)
// Present Data
| project IncidentNumber, Title, Severity, StandardizedAssignedAnalyst, TimeToRespondToIncident, MetSLA
```

Explanation

This query is designed to check if the response time for handling security incidents meets the agreed Service Level Agreement (SLA). Here's a simplified breakdown of what the query does:

  1. Define SLA Variables: A table called SLA_Variables is created to set the response time limits for different severity levels of incidents. For example, a "High" severity incident should be responded to within 60 minutes.

  2. Filter Incidents: The query looks at security incidents that were created in the last 7 days and are now closed.

  3. Extract and Standardize Data: It extracts the analyst assigned to each incident and standardizes their username to lowercase. It also converts the incident creation time to a specific timezone (Europe/Copenhagen).

  4. Summarize Incidents: It ensures that only the most recent data for each incident is considered by summarizing based on the incident number.

  5. Join SLA Data: The query joins the incident data with the SLA_Variables table to associate each incident with its corresponding SLA response time based on severity.

  6. Calculate Response Time: It calculates the actual time taken to respond to each incident and checks if this time is within the SLA limits.

  7. Filter and Present Results: Finally, it filters out incidents where the SLA status is empty and presents a list of incidents with their number, title, severity, assigned analyst, response time, and whether the SLA was met.

This query can be used as a foundation for further analysis or visualization, such as creating charts or additional aggregations.

Details

Bert-Jan Pals profile picture

Bert-Jan Pals

Released: March 3, 2025

Tables

SecurityIncident

Keywords

SecurityIncidentOwnerUserPrincipalNameIncidentNumberTitleSeverityCreatedTimeFirstModifiedTimeTimeGeneratedEuropeCopenhagen

Operators

datatableletagowhereextendtolowerdatetime_local_to_utcsummarizearg_maxjoindatetime_diffisnotemptyproject

Actions