英文:
KQL - Aggregate on latest entry
问题
期望输出:
标题 | TimeGenerated | IncidentNumber | 严重性 | 状态 | 事件URL |
---|---|---|---|---|---|
Microsoft Defender Threat Intelligence Analytics | 2023年8月2日,上午7:38:01.313 | 94945 | 高 | 新 | 链接 |
因为事件 94944 的最新条目状态为 '已关闭',所以它会被排除。
英文:
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'
答案1
得分: 1
如果我正确理解你的问题:
- 你提供的输出与任何输入记录都不匹配 - 因此我怀疑你输错了
- 你想要使用
arg_max()
聚合函数进行聚合,以获取每个事件ID的最新记录。
然后,你想要筛选出状态不是"Closed"的记录。
即:
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 |
英文:
If I understood your question correctly:
- the output you've provided doesn't match any of the input records - thus I suspect you mistyped it
- 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 |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论