Azure Sentinel高优先级未关闭事件聚合KQL查询优化

问题描述 #

在Azure Sentinel中聚合高优先级未关闭事件时，由于事件每次变更都会生成新日志条目，同一事件可能同时存在"New"和"Closed"状态的记录。直接使用| where Status != "Closed"会保留事件历史中曾为"New"的记录，无法过滤掉最新状态已关闭的事件。现有查询及数据集如下：

现有KQL查询 #

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"

需求：仅保留最新状态为"New"的高优先级事件（如94945），过滤掉最新状态为"Closed"的事件（如94944）。

正确KQL查询 #

SecurityIncident
| where Severity == "High"
// 按事件编号聚合，获取每个事件的最新完整记录
| summarize arg_max(TimeGenerated, *) by IncidentNumber
// 过滤出最新状态非Closed的事件
| where Status != "Closed"
// 选择输出字段并按时间降序排序
| project Title, TimeGenerated, IncidentNumber, Severity, Status, IncidentUrl
| order by TimeGenerated desc

关键修改说明 #

• 原查询的summarize子句将Status等字段加入分组键，导致同一事件的不同状态记录被分别聚合，无法获取事件的最新状态。
• 修改后仅以IncidentNumber作为分组键，通过arg_max(TimeGenerated, *)获取每个事件的最新完整记录，再过滤掉状态为Closed的记录，即可得到符合需求的结果。

内容的提问来源于stack exchange，提问作者floopsandRoot