As a member of a Security Operations Center (SOC), one of the key responsibilities is to generate regular reports on incidents. These reports provide valuable insights into the security landscape and help in analyzing trends and patterns. In this blog, we will explore sample queries that can be used to count incidents closed on the same day, same week, and same month, allowing SOC teams to efficiently track and report on incident resolution.
List of the incidents generated in Sentinel
______________________________________ SecurityIncident
| summarize arg_max(TimeGenerated, Status, Severity, Owner, AdditionalData,CreatedTime, Title) by IncidentNumber
| extend Tactics = todynamic(AdditionalData.tactics)
| extend Owner = todynamic(Owner.assignedTo)
| extend Product = todynamic((parse_json(tostring(AdditionalData.alertProductNames))[0]))
| project Status, Title, IncidentNumber, Severity, TimeGenerated, CreatedTime, Tactics
Incident count based on the status
______________________________________
SecurityIncident
| summarize arg_max(TimeGenerated, Status, Severity, Owner, AdditionalData,CreatedTime, Title) by IncidentNumber
| extend Tactics = todynamic(AdditionalData.tactics)
| extend Owner = todynamic(Owner.assignedTo)
| extend Product = todynamic((parse_json(tostring(AdditionalData.alertProductNames))[0]))
| summarize count() by Status
Incident Closed on Same Day ______________________________________ SecurityIncident
| where Status == "Closed"
| extend create_day = startofday(CreatedTime)
| extend closed_day = startofday(ClosedTime)
| where create_day == closed_day
| extend Incident_Closed_On_SameDay = iif(create_day == closed_day, "This Incident is closed on Same day", "Incident is not closed on same day" )
Incident Closed within same week
______________________________________
SecurityIncident
| where Status == "Closed"
| extend create_week_day = startofweek(CreatedTime)
| extend closed_week_day = startofweek(ClosedTime)
| where create_week_day == closed_week_day
| extend Incident_Closed_In_Same_Week = iif( create_week_day == closed_week_day , "This Incident is closed in Same Week", "Incident is not closed in same week" )
| project-away create_week_day, closed_week_day
Incident Closed within same month ______________________________________ SecurityIncident
| where Status == "Closed"
| extend create_month = startofmonth(CreatedTime)
| extend closed_month = startofmonth(ClosedTime)
| where create_month == closed_month
| extend Incident_Closed_Duration = iif( create_month == closed_month , "This Incident is closed in Same Month", "Incident is not closed in same month" )
| project-away create_month, closed_month
Incident Closed within 7 days or within 30 days ______________________________________
SecurityIncident
| where Status == "Closed"
| extend create_day = startofday(CreatedTime)
| extend closed_day = startofday(ClosedTime)
| extend Timedifference_In_Days = datetime_diff('day', closed_day, create_day)
| extend Incident_Life = iif(Timedifference_In_Days <= 7, "This Incident is closed within 7 Days", "Incident took more than 7 days for closure" )
| extend Incident_Life_Month = iif(Timedifference_In_Days >= 7 and Timedifference_In_Days <= 30, "This Incident is closed within 30 Days", "" )
Incident Closed by engineers. ______________________________________
SecurityIncident | where Status == 'Closed' | extend AssignedTo = tostring(Owner.assignedTo) | summarize count() by AssignedTo
Incident Closed by Tactics ________________________________
SecurityIncident | where Status == 'Closed' | extend Tactics = tostring(parse_json(tostring(AdditionalData.tactics))[0]) | summarize count() by Tactics
Incident Closed _______________________
SecurityIncident | where Status == 'Closed' | summarize arg_max(LastModifiedTime, *) by IncidentName
Consolidated Query _______________________
SecurityIncident
| where Status contains "closed"
| extend create_day = startofday(CreatedTime)
| extend closed_day = startofday(ClosedTime)
| extend create_week_day = startofweek(CreatedTime)
| extend closed_week_day = startofweek(ClosedTime)
| extend create_month = startofmonth(CreatedTime)
| extend closed_month = startofmonth(ClosedTime)
| extend Incident_Closed_On_SameDay = iif(create_day == closed_day, "This Incident is closed on Same day", "" )
| extend Incident_Closed_In_Same_Week = iif( create_week_day == closed_week_day , "This Incident is closed in Same Week", "" )
| extend Incident_Closed_In_Same_Month = iif( create_month == closed_month , "This Incident is closed in Same Month", "" )
| project Title, IncidentName, IncidentNumber, Status, CreatedTime, ClosedTime, Incident_Closed_On_SameDay, Incident_Closed_In_Same_Week, Incident_Closed_In_Same_Month
You can also use the below line for further formatting. Just remove the last line that has 'project ' and use the below line instead.
| summarize count() by Incident_Closed_In_Same_Month, Incident_Closed_In_Same_Week, Incident_Closed_On_SameDay
or | summarize arg_max(TimeGenerated, *) by LastModifiedTime to find the unique incidents that are updated recently.
By employing these sample queries, SOC teams can efficiently fetch weekly, monthly reports on incidents. Tracking incidents closed on the same day, same week, and same month allows for better monitoring of incident resolution and provides valuable data for analyzing security trends. These reports enable SOC teams to proactively identify areas of improvement and enhance the overall security posture of an organization.
Comments