如何一次性将Kusto函数应用于多个objectId值

问题背景 #

现有如下Kusto函数，用于判断指定objectId是否已被删除：

let Deleted = (o:string){
    toscalar(cluster('cA').database('dA').Table1
    | where objectId == o
    | where step in ("Completed", "CleanedUp")
    | count) > 0
};

同时有一组需要检查的objectId，示例用dynamic数组定义：

let objectIds = dynamic([
  "123",
  "159",
  "876",
  ...
  "982"
]);

需要实现对每个objectId批量应用该函数，可通过修改函数或编写查询实现。

方案1：修改函数使其接收列表 #

将函数改为接收dynamic数组，通过一次查询批量处理所有ID，性能远优于单个调用：

let DeletedList = (ids:dynamic){
    cluster('cA').database('dA').Table1
    | where objectId in (ids)
    | where step in ("Completed", "CleanedUp")
    | summarize hasCompletedOrCleanedUp = count() > 0 by objectId
    | right join (
        print objectId = ids
        | mv-expand objectId to typeof(string)
    ) on objectId
    | project objectId, isDeleted = coalesce(hasCompletedOrCleanedUp, false)
};

// 使用示例
let objectIds = dynamic(["123", "159", "876", "982"]);
DeletedList(objectIds)

该方案会返回每个objectId对应的删除状态，未匹配到记录的ID会被标记为false。

方案2：不修改原函数，用查询实现批量应用 #

如果不想改动原有函数，可将dynamic数组展开后逐个调用函数：

let Deleted = (o:string){
    toscalar(cluster('cA').database('dA').Table1
    | where objectId == o
    | where step in ("Completed", "CleanedUp")
    | count) > 0
};

let objectIds = dynamic(["123", "159", "876", "982"]);
print objectId = objectIds
| mv-expand objectId to typeof(string)
| extend isDeleted = Deleted(objectId)
| project objectId, isDeleted

注意：此方式会对每个ID单独发起查询，当ID数量较多时性能较差，优先推荐方案1。

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