Kusto查询实现EventsTable与Subcategory表行级匹配标注子分类

现有逻辑正确性判断 #

你写的核心匹配逻辑是正确的，可以准确判断EventsTable的words字段是否完整包含某条Subcategory规则的全部关键词，但存在两个可优化的问题：

• 会生成大量无效行：每个事件行会和所有子分类规则做笛卡尔积匹配，不满足条件的记录也会返回标记为none的结果，需要额外过滤
• 单事件匹配多条规则时会生成重复的事件行，需要额外做去重/聚合处理

更优实现方案 #

推荐使用toscalar()子查询的写法，性能更高，结果更简洁：

let Subcategory = datatable(subcategory:string, keywords:dynamic )
[
    'saturacion', dynamic(["saturation","infrastructure"]),
    'slow disk',dynamic(["low","disk","space"]),
    'saturacion',dynamic(["using","win","use"]),
    'saturacion',dynamic(["used","win","utilization","percentage"]),
    'swap memory',dynamic(["swap","memory","usage"]),
    'disk full',dynamic(["disk","free","size","filesystemspace"]),
    'dataflow',dynamic(["cpu","dataflow","cpupct"])
];
let EventsTable = datatable(ID:string, category:string, words:dynamic )
[
    'mcsc1','cpu',dynamic(["swap","memory","usage"]),
    'mcsc2','cpu',dynamic(["disk","free","size","filesystemspace"]),
    'mcsc3','cpu',dynamic(["cpu","dataflow","cpupct"])
];
EventsTable
| extend subcat = toscalar(
    Subcategory
    | where array_length(set_intersect(words, keywords)) == array_length(keywords)
    | project subcategory
    | take 1
)
| extend subcat = coalesce(subcat, 'none')

优化点说明 #

• 避免了生成笛卡尔积无效行，数据量大的场景下执行效率提升明显
• 每个事件行只会返回1条结果，无需额外做去重处理
• 可灵活调整匹配逻辑：如果需要支持单事件命中多个子分类的场景，把take 1替换为summarize strcat_array(make_list(subcategory), ",")即可将所有匹配的子分类拼接为字符串存储。

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