Reporting Security alerts

Occasional Contributor



I need to create a report with the following information

alert id

alert description

assign to

first activity (creation time of alert)


the assignment time (Alert was assigned to)


However in the tables I do not see the assignment time and date.


Where and how can I retrieve this data?





2 Replies
best response confirmed by Arjan Veen, van (Occasional Contributor)

@Arjan Veen, van 


'Assigned to' is done in the SecurityIncident table

| 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 
    | 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!!