Reporting Security alerts

[Arjan Veen, van]

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)

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

Where and how can I retrieve this data?

Regards

Arjan

[#2 · Accepted solution]

@Arjan Veen, van 

'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

[#3]

@Clive Watson, Many thanks!!