如何在MS Sentinel的KQL查询中按用户名（UserId）分组聚合结果

问题原因 #

你当前的查询输出同一用户对应多条记录，核心是最后一步summarize的分组参数包含了Site_Url、OfficeObjectId这类单用户下会有多个取值的字段，每个不同的站点地址、文件ID都会生成独立的结果行。

优化后KQL代码 #

修改思路是调整summarize的分组维度，仅保留用户、IP这类核心聚合维度，多值信息用make_set函数聚合为数组，同时保留你原本的过滤和阈值规则：

let threshold = 100;
let szSharePointFileOperation = "SharePointFileOperation";
let szOperations = dynamic(["FileDownloaded"]);
let starttime = 10m;
let endtime = 600m;
let historicalActivity =
OfficeActivity
| where TimeGenerated between(ago(starttime)..ago(endtime))
| where RecordType =~ szSharePointFileOperation
| where Operation in~ (szOperations)
| summarize historicalCount = count() by ClientIP, RecordType, Operation;
let recentActivity = OfficeActivity
| where TimeGenerated > ago(endtime)
| where RecordType =~ szSharePointFileOperation
| where Operation in~ (szOperations)
| summarize min(Start_Time), max(Start_Time), recentCount = count() by ClientIP, RecordType, Operation;
let RareIP = recentActivity | join kind= leftanti ( historicalActivity ) on ClientIP, RecordType, Operation
// More than 100 downloads/uploads from a new IP
| where recentCount > threshold;
OfficeActivity 
| where TimeGenerated >= ago(endtime) 
| where RecordType =~ szSharePointFileOperation
| where Operation in~ (szOperations)
| join kind= inner (RareIP) on ClientIP, RecordType, Operation
| where Start_Time between(min_Start_Time .. max_Start_Time)
| where UserAgent <> "FileZip/1.0"
| where Site_Url contains "https://abc.sharepoint.com/sites/"
// 调整分组维度，仅按核心字段分组，多值字段用make_set聚合
| summarize StartTimeUtc = min(min_Start_Time), 
            EndTimeUtc = max(max_Start_Time),
            TotalDownloadCount = count(),
            AccessSiteList = make_set(Site_Url),
            DownloadFileList = make_set(OfficeObjectId),
            UserAgentList = make_set(UserAgent),
            IPSeenCount = take_any(recentCount)
            by RecordType, Operation, UserType, UserId, ClientIP, OfficeWorkload
| extend timestamp = StartTimeUtc, AccountCustomEntity = UserId, IPCustomEntity = ClientIP
| order by IPSeenCount desc, ClientIP asc, Operation asc, UserId asc

补充说明 #

• 如果你不需要保留下载站点、文件列表这类明细，直接删除对应的make_set聚合项即可
• 若需要按用户+IP之外的维度聚合，调整by后面的字段列表即可
• 提前过滤UserAgent和Site_Url可以减少后续聚合的计算量，因此调整了过滤步骤的顺序

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