Azure Workbook中基于Kusto实现带趋势线的死信消息Timechart及Hover问题解决

我有一个每日运行的函数，会抓取每个topic/subscription的死信消息总数（列名为messageCount），并在traces表中生成一条新记录。需求如下：

• 在Workbook中渲染timechart，展示过去7天内每个topic/subscription的死信消息趋势变化，尽可能添加趋势线
• 实现基于messageCount列的hover效果——该效果在App Insights Logs中能正常显示数值，但在Workbook中无法实现

已尝试的Kusto查询：

traces
| where timestamp > ago(7d)
| extend
    TopicName = tostring(customDimensions["prop__TopicName"]), 
    SubscriptionName = tostring(customDimensions["prop__SubscriptionName"]),
    MessageCount = toint(customDimensions["prop__DlqCount"]),
    ["Topic/subscription"] = strcat(tostring(customDimensions["prop__TopicName"]), '/', tostring(customDimensions["prop__SubscriptionName"]))
| where cloud_RoleName startswith "myCloudName" and message has "customString"
| make-series take_any(MessageCount) on timestamp in range (ago(7d), now(), 1d) by SubscriptionName
| render timechart 

该查询在Insights日志中运行正常，但Workbook表现不符合预期。

未使用make-series的查询返回示例：

timestamp  | topicName | subscriptionName | messageCount
--------------------------------------------------------
10/31/2022 | myTopic1  | mysubscription1  |   915
10/31/2022 | myTopic2  | mysubscription2  |   1023
10/31/2022 | myTopic3  | mysubscription3  |   602
10/30/2022 | myTopic1  | mysubscription1  |   927
10/30/2022 | myTopic2  | mysubscription2  |   1029
10/30/2022 | myTopic3  | mysubscription3  |   608

Logs与Workbook的hover效果对比：

• App Insights Logs：[hover正常显示messageCount数值的截图]
• Workbook：[hover未正常显示messageCount数值的截图]

1. 调整查询适配Workbook渲染 #

原查询用take_any(MessageCount)易导致Workbook数据映射异常，改用max（因每日单条记录，max/sum结果一致），同时用更直观的TopicSubscription作为分组维度：

traces
| where timestamp > ago(7d)
| extend
    TopicName = tostring(customDimensions["prop__TopicName"]), 
    SubscriptionName = tostring(customDimensions["prop__SubscriptionName"]),
    MessageCount = toint(customDimensions["prop__DlqCount"]),
    TopicSubscription = strcat(TopicName, "/", SubscriptionName)
| where cloud_RoleName startswith "myCloudName" and message has "customString"
// 按天聚合，确保每个Topic/subscription每天一条数据
| summarize MessageCount = max(MessageCount) by bin(timestamp, 1d), TopicSubscription
// 补全缺失日期（无数据时填充0）
| make-series MessageCount = sum(MessageCount) on timestamp from ago(7d) to now() step 1d by TopicSubscription
| render timechart with (ycolumn=MessageCount)

2. 修复Workbook hover显示问题 #

Workbook的hover依赖明确的字段映射，可通过以下方式解决：

• 将make-series生成的数组展开为扁平化数据，让Workbook直接识别原始数值：

  // 在render前添加展开步骤
  | mv-expand timestamp, MessageCount
  | render timechart
  

• 进入Workbook可视化的Advanced Settings，手动指定Y Column为MessageCount，而非自动匹配

3. 添加趋势线 #

Workbook内置趋势线功能，操作步骤：

1. 运行调整后的查询后，点击可视化区域的Edit按钮
2. 在Chart Settings中找到Trendlines选项，勾选启用
3. 可选择线性、指数等趋势线类型，自定义颜色、透明度

简洁替代方案 #

如果每日仅生成一条记录，可省略make-series，直接用summarize后渲染，Workbook会自动处理时间轴，且hover显示更稳定：

traces
| where timestamp > ago(7d)
| extend
    TopicName = tostring(customDimensions["prop__TopicName"]), 
    SubscriptionName = tostring(customDimensions["prop__SubscriptionName"]),
    MessageCount = toint(customDimensions["prop__DlqCount"]),
    TopicSubscription = strcat(TopicName, "/", SubscriptionName)
| where cloud_RoleName startswith "myCloudName" and message has "customString"
| summarize MessageCount = max(MessageCount) by bin(timestamp, 1d), TopicSubscription
| render timechart with (trendlines=linear)

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