KQL – 最新条目上的聚合

huangapple
go评论71阅读模式
英文:

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

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

  1. 你提供的输出与任何输入记录都不匹配 - 因此我怀疑你输错了
  2. 你想要使用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:

  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

huangapple
  • 本文由 发表于 2023年8月4日 06:37:07
  • 转载请务必保留本文链接：https://go.coder-hub.com/76831973.html
  • azure
  • azure-data-explorer
  • kql
  • sentinel
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定