azurekqlazure-data-explorerazure-sentinel

KQL - Aggregate on latest entry


I am trying to aggregate open high incidents from Azure Sentinel. Since incidents are updated in logs every time a change is made, this means that there is a latest iteration of an incident being Status 'New' and a latest entry for Status 'Closed'. Since I cannot just filter out | where Status != "Closed", since that would leave the latest entry for when it was 'new', what would the aggregation query here be?

What I got:

SecurityIncident
| where Severity == "High"
| summarize arg_max(TimeGenerated, *) by IncidentNumber,Title,Severity, Status, IncidentUrl
| where IncidentNumber == "94944"
| project Title, TimeGenerated,IncidentNumber,Severity, Status, IncidentUrl
| order by TimeGenerated desc 

Data Set

Title,"TimeGenerated [Local Time]",IncidentNumber,Severity,Status,IncidentUrl
"Microsoft Defender Threat Intelligence Analytics","8/2/2023, 10:20:14.928 AM",94945,High,New,"https://portal.azure.com/#asset/Microsoft_Azure_Security_Insights/Incident/subscriptions/000-000-000/resourceGroups/allurbase/providers/Microsoft.OperationalInsights/workspaces/allderbase/providers/Microsoft.SecurityInsights/Incidents/8aaa-9aaa-0aaa-7aax"
"Microsoft Defender Threat Intelligence Analytics","8/2/2023, 7:38:01.313 AM",94944,High,Closed,"https://portal.azure.com/#asset/Microsoft_Azure_Security_Insights/Incident/subscriptions/000-000-000/resourceGroups/allurbase/providers/Microsoft.OperationalInsights/workspaces/allderbase/providers/Microsoft.SecurityInsights/Incidents/1aaa-3aaa-2aaa-5aax"
"Microsoft Defender Threat Intelligence Analytics","8/2/2023, 7:22:30.487 AM",94944,High,New,"https://portal.azure.com/#asset/Microsoft_Azure_Security_Insights/Incident/subscriptions/000-000-000/resourceGroups/allurbase/providers/Microsoft.OperationalInsights/workspaces/allderbase/providers/Microsoft.SecurityInsights/Incidents/1aaa-3aaa-2aaa-5aax"
"Microsoft Defender Threat Intelligence Analytics","8/1/2023, 10:30:14.928 PM",94944,High,New,"https://portal.azure.com/#asset/Microsoft_Azure_Security_Insights/Incident/subscriptions/000-000-000/resourceGroups/allurbase/providers/Microsoft.OperationalInsights/workspaces/allderbase/providers/Microsoft.SecurityInsights/Incidents/1aaa-3aaa-2aaa-5aax"
"Microsoft Defender Threat Intelligence Analytics","8/1/2023, 9:31:51.583 PM",94944,High,New,"https://portal.azure.com/#asset/Microsoft_Azure_Security_Insights/Incident/subscriptions/000-000-000/resourceGroups/allurbase/providers/Microsoft.OperationalInsights/workspaces/allderbase/providers/Microsoft.SecurityInsights/Incidents/1aaa-3aaa-2aaa-5aax"
"Microsoft Defender Threat Intelligence Analytics","8/1/2023, 8:31:42.746 PM",94944,High,New,"https://portal.azure.com/#asset/Microsoft_Azure_Security_Insights/Incident/subscriptions/000-000-000/resourceGroups/allurbase/providers/Microsoft.OperationalInsights/workspaces/allderbase/providers/Microsoft.SecurityInsights/Incidents/1aaa-3aaa-2aaa-5aax"
"Microsoft Defender Threat Intelligence Analytics","8/1/2023, 7:30:03.104 PM",94944,High,New,"https://portal.azure.com/#asset/Microsoft_Azure_Security_Insights/Incident/subscriptions/000-000-000/resourceGroups/allurbase/providers/Microsoft.OperationalInsights/workspaces/allderbase/providers/Microsoft.SecurityInsights/Incidents/1aaa-3aaa-2aaa-5aax"
"Microsoft Defender Threat Intelligence Analytics","8/1/2023, 7:30:02.938 PM",94944,High,New,"https://portal.azure.com/#asset/Microsoft_Azure_Security_Insights/Incident/subscriptions/000-000-000/resourceGroups/allurbase/providers/Microsoft.OperationalInsights/workspaces/allderbase/providers/Microsoft.SecurityInsights/Incidents/1aaa-3aaa-2aaa-5aax"

Expected Output:

Title TimeGenerated IncidentNumber Severity Status IncidentURL
Microsoft Defender Threat Intelligence Analytics 8/2/2023, 7:38:01.313 AM 94945 High New Link

As the incident 94944 would be dropped since its latest entry was status 'closed'


Solution

  • If I understood your question correctly:

    1. the output you've provided doesn't match any of the input records - thus I suspect you mistyped it
    2. you want to aggregate using the arg_max() aggregation function to get the latest records for each incident ID. then, you want to filter only for those records whose status isn't "Closed".

    i.e.:

    datatable
    (
        Title: string,
        ['TimeGenerated [Local Time]']: datetime,
        IncidentNumber: long,
        Severity: string,
        Status: string,
        IncidentUrl: string
    )
    [
        "Microsoft Defender Threat Intelligence Analytics", "8/2/2023, 10:20:14.928 AM", 94945, 'High', 'New', "https://portal.azure.com/#asset/Microsoft_Azure_Security_Insights/Incident/subscriptions/000-000-000/resourceGroups/allurbase/providers/Microsoft.OperationalInsights/workspaces/allderbase/providers/Microsoft.SecurityInsights/Incidents/8aaa-9aaa-0aaa-7aax",
        "Microsoft Defender Threat Intelligence Analytics", "8/2/2023, 7:38:01.313 AM", 94944, 'High', 'Closed', "https://portal.azure.com/#asset/Microsoft_Azure_Security_Insights/Incident/subscriptions/000-000-000/resourceGroups/allurbase/providers/Microsoft.OperationalInsights/workspaces/allderbase/providers/Microsoft.SecurityInsights/Incidents/1aaa-3aaa-2aaa-5aax",
        "Microsoft Defender Threat Intelligence Analytics", "8/2/2023, 7:22:30.487 AM", 94944, 'High', 'New', "https://portal.azure.com/#asset/Microsoft_Azure_Security_Insights/Incident/subscriptions/000-000-000/resourceGroups/allurbase/providers/Microsoft.OperationalInsights/workspaces/allderbase/providers/Microsoft.SecurityInsights/Incidents/1aaa-3aaa-2aaa-5aax",
        "Microsoft Defender Threat Intelligence Analytics", "8/1/2023, 10:30:14.928 PM", 94944, 'High', 'New', "https://portal.azure.com/#asset/Microsoft_Azure_Security_Insights/Incident/subscriptions/000-000-000/resourceGroups/allurbase/providers/Microsoft.OperationalInsights/workspaces/allderbase/providers/Microsoft.SecurityInsights/Incidents/1aaa-3aaa-2aaa-5aax",
        "Microsoft Defender Threat Intelligence Analytics", "8/1/2023, 9:31:51.583 PM", 94944, 'High', 'New', "https://portal.azure.com/#asset/Microsoft_Azure_Security_Insights/Incident/subscriptions/000-000-000/resourceGroups/allurbase/providers/Microsoft.OperationalInsights/workspaces/allderbase/providers/Microsoft.SecurityInsights/Incidents/1aaa-3aaa-2aaa-5aax",
        "Microsoft Defender Threat Intelligence Analytics", "8/1/2023, 8:31:42.746 PM", 94944, 'High', 'New', "https://portal.azure.com/#asset/Microsoft_Azure_Security_Insights/Incident/subscriptions/000-000-000/resourceGroups/allurbase/providers/Microsoft.OperationalInsights/workspaces/allderbase/providers/Microsoft.SecurityInsights/Incidents/1aaa-3aaa-2aaa-5aax",
        "Microsoft Defender Threat Intelligence Analytics", "8/1/2023, 7:30:03.104 PM", 94944, 'High', 'New', "https://portal.azure.com/#asset/Microsoft_Azure_Security_Insights/Incident/subscriptions/000-000-000/resourceGroups/allurbase/providers/Microsoft.OperationalInsights/workspaces/allderbase/providers/Microsoft.SecurityInsights/Incidents/1aaa-3aaa-2aaa-5aax",
        "Microsoft Defender Threat Intelligence Analytics", "8/1/2023, 7:30:02.938 PM", 94944, 'High', 'New', "https://portal.azure.com/#asset/Microsoft_Azure_Security_Insights/Incident/subscriptions/000-000-000/resourceGroups/allurbase/providers/Microsoft.OperationalInsights/workspaces/allderbase/providers/Microsoft.SecurityInsights/Incidents/1aaa-3aaa-2aaa-5aax",
    ]
    | summarize arg_max(['TimeGenerated [Local Time]'],* ) by IncidentNumber
    | where Status != "Closed"
    
    IncidentNumber TimeGenerated [Local Time] Title Severity Status IncidentUrl
    94945 2023-08-02 10:20:14.9280000 Microsoft Defender Threat Intelligence Analytics High New https://portal.azure.com/#asset/Microsoft_Azure_Security_Insights/Incident/subscriptions/000-000-000/resourceGroups/allurbase/providers/Microsoft.OperationalInsights/workspaces/allderbase/providers/Microsoft.SecurityInsights/Incidents/8aaa-9aaa-0aaa-7aax