使用KQL
鉴于这个数据集

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}
    ])
];

我想按name1和name2过滤要素，按s1和s3过滤ScanState，然后计算每个Type的VMID的数量，并给予VMID的列表
1.对于www.example.com = name 1，值== truefeature.name

1. www.example.com = name2的value = feature.name false
   1.对于www.example.com = s1，has expiration = falsescanState.name
   1.对于www.example.com = s3，has expiration = truescanState.name
   我的主要问题是当使用mv-apply时，它会将JSON分成多行。执行count（）会导致额外的结果。

MyTable
| mv-apply features, scanner on (where  features.name == "name1" or features.name == "name2" or scanner.name == "s1" or scanner.name == "s3" )
| extend feature1State = tobool(features.name == "name1" and features.value == true)
| extend feature2State = tobool(features.name == "name2" and features.value == false)
| extend scan1State = tobool(scanner.name == "s1" and scanner.expiry == false)
| extend scan2State = tobool(scanner.name == "s3" and scanner.expiry == true)
| summarize vmCount = count(VMID),
    f1count = countif(feature1State == true),
    f2count= countif(feature2State== true),
    scan1count = countif(scan1State == true),
    scan2count = countif(scan2State == true),
    f1FailVm = make_set_if(VMID, feature1State == false and isnotempty( VMID)),
    f2FailVm = make_set_if(VMID, feature2State== false and isnotempty( VMID)),
    scan1FailVm = make_set_if(VMID, scan1State == false and isnotempty( VMID)),
    scan2FailVm = make_set_if(VMID, scan2State == false and isnotempty( VMID))
    by ID, Type

它的输出

请注意，ID-1的VM计数为6，这是不正确的。这是因为mv-apply本质上创建了多行。由于features和Scanner中的条目数不同，所以它选择了它们中的最大值。
有没有更好的办法来解决这个问题。
更新1：使用count_distinct解决VMID，但如何获得正确的失败列表值。使用count_distinct

输出