Weekly Security Incident Comparison
Query
//Weekly Security Incident Comparison
union (
SecurityIncident
| where TimeGenerated >= ago(21d) and TimeGenerated < ago(14d)
| summarize ThreeWeeksAgoCount = count() by Title
| project Title, ThreeWeeksAgoCount, TwoWeeksAgoCount = 0, LastWeekCount = 0, CurrentWeekCount = 0, Difference = 0
),
(
SecurityIncident
| where TimeGenerated >= ago(14d) and TimeGenerated < ago(7d)
| summarize TwoWeeksAgoCount = count() by Title
| project Title, ThreeWeeksAgoCount = 0, TwoWeeksAgoCount, LastWeekCount = 0, CurrentWeekCount = 0, Difference = 0
),
(
SecurityIncident
| where TimeGenerated >= ago(7d) and TimeGenerated < now()
| summarize LastWeekCount = count() by Title
| project Title, ThreeWeeksAgoCount = 0, TwoWeeksAgoCount = 0, LastWeekCount, CurrentWeekCount = 0, Difference = 0
),
(
SecurityIncident
| where TimeGenerated >= startofweek(now())
| summarize CurrentWeekCount = count() by Title
| project Title, ThreeWeeksAgoCount = 0, TwoWeeksAgoCount = 0, LastWeekCount = 0, CurrentWeekCount, Difference = 0
)
| project Title, ThreeWeeksAgoCount, TwoWeeksAgoCount, LastWeekCount, CurrentWeekCount, DifferenceWithLastWeek = LastWeekCount - TwoWeeksAgoCount
| sort by LastWeekCount descExplanation
This query compares the number of security incidents for the past four weeks. It counts the incidents for each week and calculates the difference in incidents between the last week and two weeks ago. The results are sorted by the number of incidents in the last week.
Details
Muzammil Mahmood
Released: June 7, 2024
Tables
SecurityIncident
Keywords
SecurityIncidentTimeGeneratedTitleThreeWeeksAgoCountTwoWeeksAgoCountLastWeekCountCurrentWeekCountDifferenceDifferenceWithLastWeek
Operators
unionwheresummarizebyprojectagocountstartofweeknowsortdesc