azure - KQL - 最新条目的聚合

Question

我正在尝试聚合来自 Azure Sentinel 的开放性高事件。由于每次进行更改时都会在日志中更新事件，这意味着事件的最新迭代状态为"new"，并且状态为“已关闭”的最新条目。因为我不能过滤掉 | where Status != "Closed" ，因为这会在"new"时留下最新条目，所以这里的聚合查询是什么？

我得到了什么:

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 

数据集

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"

预期输出:

<表类=“s-表”><标题>标题生成时间事件编号严重性状态事件URL <正文>Microsoft Defender 威胁情报分析2023 年 8 月 2 日上午 7:38:01.31394945高新链接

事件 94944 将被删除，因为其最新条目状态为“已关闭”

Answer 1

如果我正确理解你的问题:

1. 您提供的输出与任何输入记录都不匹配 - 因此我怀疑您输入错误
2. 您想要使用 arg_max() 进行聚合聚合函数获取每个事件ID的最新记录。然后，您只想过滤那些状态不是“已关闭”的记录。

即:

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"

<表类=“s-表”><标题>事件编号生成时间[本地时间]标题严重性状态事件Url <正文>949452023-08-02 10:20:14.9280000Microsoft Defender 威胁情报分析高新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