如何在Application Insights中使用KQL创建递归查询？

要实现跨requests和dependencies表的递归层级查询，核心是先统一两个表的字段结构，再利用KQL的递归关联能力遍历整个调用链，最终输出适配Workbook树形视图的结构化数据。

1. 统一表结构 #

requests和dependencies表的核心字段命名、含义存在差异，先通过union将它们合并为标准化视图，保留追踪所需关键信息：

let unified_tracing = union
    (requests
     | project
         itemType = "Request",
         itemId = id,
         parentId = operation_ParentId,
         name = name,
         duration = duration,
         timestamp = timestamp,
         operation_Id = operation_Id
    ),
    (dependencies
     | project
         itemType = "Dependency",
         itemId = id,
         parentId = operation_ParentId,
         name = name,
         duration = duration,
         timestamp = timestamp,
         operation_Id = operation_Id
    );

2. 编写递归查询 #

使用KQL的递归union结合materialize遍历层级关系，以下示例以指定根请求ID为例（替换为你需要追踪的目标ID）：

let unified_tracing = union
    (requests
     | project
         itemType = "Request",
         itemId = id,
         parentId = operation_ParentId,
         name = name,
         duration = duration,
         timestamp = timestamp,
         operation_Id = operation_Id
    ),
    (dependencies
     | project
         itemType = "Dependency",
         itemId = id,
         parentId = operation_ParentId,
         name = name,
         duration = duration,
         timestamp = timestamp,
         operation_Id = operation_Id
    );
// 定义根节点，替换为你的目标追踪ID
let root_traces = materialize(unified_tracing | where itemId == "req_1");
// 递归遍历所有子节点
let recursive_traces = materialize(
    root_traces
    | union kind=recursive
        (unified_tracing
         | join kind=inner (recursive_traces) on $left.parentId == $right.itemId
         | project-away itemId1, parentId1, operation_Id1, timestamp1, duration1, name1, itemType1
        )
);
// 整理输出并计算层级深度，方便树形视图缩进
recursive_traces
| order by timestamp asc
| extend depth = toint(path_length(itemId, parentId, "->"))
| project itemType, itemId, parentId, name, duration, timestamp, depth

3. 在Workbook中配置树形视图 #

1. 将上述查询结果导入Workbook的查询组件
2. 切换可视化类型为树形视图
3. 在配置面板中：
   • 设置父列为parentId
   • 设置ID列为itemId
   • 添加需要展示的字段（如itemType、name、duration等）
4. 可根据itemType设置不同样式颜色，优化可视化效果

注意事项 #

• 若需追踪整个操作链，可将根节点筛选条件改为where operation_Id == "你的操作ID"，遍历该操作下所有关联节点
• 递归查询性能受数据量影响，建议添加时间范围筛选（如| where timestamp between (ago(1h) .. now())）缩小数据集

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