KQL中mv-apply后合并结果行获取正确计数的方法

要解决这个问题，核心是先筛选出完全符合所有条件的VM记录，再进行分组统计，避免因mv-apply展开数组导致的重复计数问题。

实现代码 #

let MyTable = datatable (VMID:int, ID:string, Type:string, features:dynamic, scanner:dynamic)
[
    1, "ID-1", "Windows", dynamic([
        {"name": "name1", "value": true},
        {"name": "name2", "value": false},
        {"name": "name3", "value": true}
    ]), dynamic([
        {"name": "s1", "expiry": false},
        {"name": "s2", "expiry": true},
        {"name": "s3", "expiry": true},
        {"name": "s4", "expiry": false}
    ]),
    2, "ID-1", "Windows", dynamic([
        {"name": "name1", "value": true},
        {"name": "name2", "value": false},
        {"name": "name3", "value": true}
    ]), dynamic([
        {"name": "s1", "expiry": false},
        {"name": "s2", "expiry": true},
        {"name": "s3", "expiry": false},
        {"name": "s4", "expiry": true}
    ]),
    3, "ID-1", "Linux", dynamic([
        {"name": "name1", "value": true},
        {"name": "name2", "value": false},
        {"name": "name3", "value": true}
    ]), dynamic([
        {"name": "s1", "expiry": false},
        {"name": "s2", "expiry": false},
        {"name": "s3", "expiry": true},
        {"name": "s4", "expiry": false}
    ]),
    4, "ID-2", "Windows", dynamic([
        {"name": "name1", "value": true},
        {"name": "name2", "value": false},
        {"name": "name3", "value": true}
    ]), dynamic([
        {"name": "s1", "expiry": false},
        {"name": "s2", "expiry": true},
        {"name": "s3", "expiry": false},
        {"name": "s4", "expiry": true}
    ]),
    5, "ID-2", "Windows", dynamic([
        {"name": "name1", "value": true},
        {"name": "name2", "value": false},
        {"name": "name3", "value": true}
    ]), dynamic([
        {"name": "s1", "expiry": false},
        {"name": "s2", "expiry": true},
        {"name": "s3", "expiry": true},
        {"name": "s4", "expiry": true}
    ])
];
MyTable
| where array_exists(features, f => f.name == "name1" and f.value == true)
    and array_exists(features, f => f.name == "name2" and f.value == false)
    and array_exists(scanner, s => s.name == "s1" and s.expiry == false)
    and array_exists(scanner, s => s.name == "s3" and s.expiry == true)
| summarize VMCount = count(VMID), VMIDList = make_list(VMID) by Type

代码说明 #

1. 条件筛选：使用array_exists函数直接检查每条VM记录的features和scanner数组是否包含符合要求的元素，无需展开数组，保证每条VM记录只保留一行。
2. 分组统计：通过summarize按Type分组，用count(VMID)统计符合条件的VM数量，用make_list(VMID)生成对应Type下的VMID列表。

执行结果 #

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