SOLVED

Reporting Security alerts

Brass Contributor

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 Replies
best response confirmed by Arjan Veen, van (Brass Contributor)
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

 

@Clive Watson, Many thanks!!
1 best response

Accepted Solutions
best response confirmed by Arjan Veen, van (Brass Contributor)
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

 

View solution in original post