KQL查询在高级狩猎正常，转为检测规则时失败求解决

问题描述 #

编写KQL查询用于检测5分钟内对80端口的GET请求次数超过5次的行为，该查询在高级狩猎中运行正常，但配置为检测规则时触发错误：

No events match the given event identifiers (a combination of ReportId, AlertId, BehaviorId, or DeviceId and Timestamp). Edit the query's aggregation expressions for these columns and try again.

原查询代码：

DeviceNetworkEvents
| extend parsed = parse_json(AdditionalFields)
| where parsed.method has "get"
| where RemotePort == 80
| where not (parsed.host endswith ".goog" or parsed.host startswith "10.32.")
| where not (parsed.uri endswith "windowsupdate.com")
| where not (parsed.host has_any ("intranet", "gvt1.com", "usertrust.com", "entrust.net", "wpad", "169.254.169.254", "adobe.com", "msftconnecttest.com", "narrabay", "windowsupdate.com", "microsoft.com", "digicert.com", "lencr.org", "amazontrust.com"))
| summarize (Timestamp, ReportId)=arg_max(Timestamp, ReportId), count() by DeviceId, bin(Timestamp, 5m)
| where count_ > 5

问题原因 #

检测规则要求输出的每条记录必须包含能关联到原始事件的有效标识符组合（如ReportId+DeviceId+Timestamp）。原查询使用arg_max(Timestamp, ReportId)仅保留了每个5分钟时间桶内最新一条事件的标识符，无法覆盖所有符合条件的原始事件，导致系统无法匹配到对应的事件记录，从而报错。

修正后的查询 #

采用"先统计可疑窗口，再关联原始事件"的方式，确保每条输出记录都包含完整有效的事件标识符：

// 第一步：筛选并统计5分钟窗口内请求数超5次的设备和时间范围
let suspicious_windows = DeviceNetworkEvents
| extend parsed = parse_json(AdditionalFields)
| where parsed.method has "get"
| where RemotePort == 80
| where not (parsed.host endswith ".goog" or parsed.host startswith "10.32.")
| where not (parsed.uri endswith "windowsupdate.com")
| where not (parsed.host has_any ("intranet", "gvt1.com", "usertrust.com", "entrust.net", "wpad", "169.254.169.254", "adobe.com", "msftconnecttest.com", "narrabay", "windowsupdate.com", "microsoft.com", "digicert.com", "lencr.org", "amazontrust.com"))
| summarize request_count = count() by DeviceId, bin(Timestamp, 5m)
| where request_count > 5;

// 第二步：关联回所有符合条件的原始事件，保留完整标识符
DeviceNetworkEvents
| extend parsed = parse_json(AdditionalFields)
| where parsed.method has "get"
| where RemotePort == 80
| where not (parsed.host endswith ".goog" or parsed.host startswith "10.32.")
| where not (parsed.uri endswith "windowsupdate.com")
| where not (parsed.host has_any ("intranet", "gvt1.com", "usertrust.com", "entrust.net", "wpad", "169.254.169.254", "adobe.com", "msftconnecttest.com", "narrabay", "windowsupdate.com", "microsoft.com", "digicert.com", "lencr.org", "amazontrust.com"))
| join kind=inner suspicious_windows on DeviceId, bin(Timestamp, 5m)
| project Timestamp, ReportId, DeviceId, RemotePort, RequestMethod = parsed.method, RequestHost = parsed.host, RequestUri = parsed.uri, TotalRequestsInWindow = request_count

说明 #

• 该查询先筛选出满足条件的设备和5分钟时间窗口，再通过join关联回所有符合条件的原始事件，确保每条输出记录都包含ReportId、DeviceId和Timestamp这三个核心标识符，完全符合检测规则的要求。
• 输出结果还额外包含了请求方法、主机、URI等信息，便于后续分析。

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