SLA Time To Respond
Query
// 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, MetSLAAbout this 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
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:
-
Define SLA Variables: A table called
SLA_Variablesis 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. -
Filter Incidents: The query looks at security incidents that were created in the last 7 days and are now closed.
-
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).
-
Summarize Incidents: It ensures that only the most recent data for each incident is considered by summarizing based on the incident number.
-
Join SLA Data: The query joins the incident data with the
SLA_Variablestable to associate each incident with its corresponding SLA response time based on severity. -
Calculate Response Time: It calculates the actual time taken to respond to each incident and checks if this time is within the SLA limits.
-
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
Released: March 3, 2025
Tables
Keywords
Operators