如何在KQL中利用数组实现批量!contains排除查询？

你可以通过KQL的contains_any（或has_any）结合not逻辑，搭配可复用数组来替代大量重复的!contains语句，同时用in简化多值!=判断。优化后的脚本如下：

//Email to external domains from staff member
let externalDomains = pack_array(
"bol.com.br",
"terra.com.br",
"ig.com.br",
"itelefonica.com.br",
"r7.com",
"zipmail.com.br",
"globo.com",
"globomail.com",
"oi.com.br");
// 公共排除词（Subject和FileName共享部分）
let commonExcludeTerms = pack_array(
"payslip",
"bill",
"ticket",
"voucher",
"tenancy",
"booking",
"insurance",
"travel",
"hotel",
"tax",
"itinerary");
// Subject专属排除词
let subjectExcludeTerms = array_concat(commonExcludeTerms, pack_array("undeliverable:", "novated"));
// FileName专属排除词
let fileNameExcludeTerms = array_concat(commonExcludeTerms, pack_array("pay slip", "tfn", "recipe", "rental", "personal"));
// 排除的文件类型
let excludeFileTypes = pack_array("png", "jpeg", "gif", "ics;");

EmailEvents
| where RecipientEmailAddress has_any (externalDomains)
// 用not + contains_any替代多个!contains
| where not (Subject contains_any (subjectExcludeTerms))
| where DeliveryAction != "Blocked"
| where SenderFromAddress !contains "payroll"
| where AttachmentCount > 0
| join EmailAttachmentInfo on RecipientEmailAddress
// 用not + in替代多个!=
| where not (FileType in (excludeFileTypes))
// 同样用not + contains_any批量排除FileName关键词
| where not (FileName contains_any (fileNameExcludeTerms))
| project TimeEmail = Timestamp, Subject, SenderFromAddress, RecipientEmailAddress, AccountName = tostring(split(SenderFromAddress, "@")[0]), DeliveryAction, FileName , FileType, FileSize;

核心优化点： #

• 批量反向匹配：not (字段 contains_any (数组))等价于对数组中每个元素执行!contains，完美替代你原来的重复语句，且支持数组参数
• 复用性提升：把公共排除词抽离成单独数组，再通过array_concat合并专属项，后续修改只需维护数组内容，无需重复修改多个where条件
• 简化多值判断：FileType的多个!=用not (FileType in (数组))简化，逻辑更清晰
• 灵活切换匹配逻辑：如果需要精确词匹配（而非子串包含），只需把contains_any换成has_any，用not (字段 has_any (数组))实现批量!has效果

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