Forum Discussion

Rayen's avatar
Rayen
Copper Contributor
Jan 27, 2025

Fetch security events with their underlying log entries

Hello, 

I am trying to extract all the alerts generated by sentinel including the events that triggered that alert. I have the following query:

SecurityIncident
| summarize arg_max(TimeGenerated, *) by IncidentName
| where ClassificationComment !has "Automatically closed, as the incident is not in scope for monitoring."
| where CreatedTime >= startofday(ago(1d)) and CreatedTime < startofday(now())
| sort by CreatedTime
| mv-expand AlertIds
| extend AlertId = tostring(AlertIds)
| join (SecurityAlert | where StartTime >= startofday(ago(30d)) and StartTime < startofday(now()) | summarize arg_max(TimeGenerated, *) by SystemAlertId | project-rename AlertId = SystemAlertId) on AlertId

Is this enough to achieve what I need or is there any changes that needs to be made ?

alerts
analytics
automation
azure
data collection
KQL
Log Data
Reports
siem
Solutions
  • Clive_Watson's avatar
    Clive_Watson
    Bronze Contributor
    Jan 29, 2025

    I tend to use this, which is very similar, you'd have to add you exclusion line into it:

    SecurityIncident
| extend Alerts = extract("\\[(.*?)\\]", 1, tostring(AlertIds))
| mv-expand AlertIds to typeof(string), Labels to typeof(string), Comments to typeof(string), AdditionalData to typeof(string)
| join kind=leftouter
(
    SecurityAlert
    //| where TimeGenerated > ago(10m)   
    ) on $right.SystemAlertId == $left.AlertIds
    | summarize AlertCount=dcount(AlertIds),
                arg_max
                (
                 TimeGenerated, Title, Severity, Status, Owner,
                 ModifiedBy, CreatedTime, FirstModifiedTime, LastModifiedTime,
                 Tags=tostring(parse_json(Labels).labelName),
                 Comments=tostring(parse_json(Comments).message),
                 AdditionalData, Tactics, Techniques, SubTechniques,
                 Classification, ClassificationComment, ClassificationReason,
                 ProviderName, Description, ExtendedProperties
                )
                by IncidentNumber


    or just this if you want all raw columns

    SecurityIncident
| extend Alerts = extract("\\[(.*?)\\]", 1, tostring(AlertIds))
| mv-expand AlertIds to typeof(string), Labels to typeof(string), Comments to typeof(string), AdditionalData to typeof(string)
| join kind=leftouter
(
    SecurityAlert
    //| where TimeGenerated > ago(10m)   
    ) on $right.SystemAlertId == $left.AlertIds
    | summarize AlertCount=dcount(AlertIds),
                arg_max
                (
                 TimeGenerated, *
                )
                by IncidentNumber

     

Resources