Forum Discussion
Reporting Security alerts
All, I need to create a report with the following information alert id alert description assign to first activity (creation time of alert) AND the assignment time (Alert was assigned to) ...
'Assigned to' is done in the SecurityIncident table
SecurityIncident | extend owner = tostring(Owner.assignedTo) | where isnotempty( owner) | summarize IncidentCount = count(), arg_min(LastModifiedTime,*) by IncidentNumber, Title, owner | extend Alerts = extract("\\[(.*?)\\]", 1, tostring(AlertIds)) | order by LastModifiedTime asc | mv-expand todynamic(AlertIds) to typeof(string)You'd then have to JOIN back to the SecurityAlert Table (this is an example but you need to play with it as it gets the last updated time of the Alert, which maybe by another "assigned to" person)
| join ( SecurityAlert | summarize AlertCount = count() by AlertSeverity, SystemAlertId, AlertName ) on $left.AlertIds == $right.SystemAlertId | summarize sum(AlertCount), make_set(AlertName) by IncidentNumber, Title, owner, LastModifiedTime, TimeGenerated
'Assigned to' is done in the SecurityIncident table
SecurityIncident
| extend owner = tostring(Owner.assignedTo)
| where isnotempty( owner)
| summarize IncidentCount = count(), arg_min(LastModifiedTime,*) by IncidentNumber, Title, owner
| extend Alerts = extract("\\[(.*?)\\]", 1, tostring(AlertIds))
| order by LastModifiedTime asc
| mv-expand todynamic(AlertIds) to typeof(string)You'd then have to JOIN back to the SecurityAlert Table (this is an example but you need to play with it as it gets the last updated time of the Alert, which maybe by another "assigned to" person)
| join
(
SecurityAlert
| summarize AlertCount = count() by AlertSeverity, SystemAlertId, AlertName
) on $left.AlertIds == $right.SystemAlertId
| summarize sum(AlertCount), make_set(AlertName) by IncidentNumber, Title, owner, LastModifiedTime, TimeGenerated
@Clive Watson, Many thanks!!
