Azure Workbook中KQL替换TimeGenerated为ingestion_time()无结果求助

核心问题根源 #

直接将TimeGenerated替换为ingestion_time()后无结果，主要有两个关键原因：

1. ingestion_time()是KQL函数，并非Heartbeat表的原生字段，必须先通过extend显式生成计算字段才能使用；
2. 原查询的inner join仅依赖_ResourceId匹配，且时间范围逻辑未统一替换，导致子查询间无匹配数据被过滤。

修改后的查询代码 #

Heartbeat
| extend TimeIngested = ingestion_time()
| where TimeIngested > ago(60m)
| summarize count() by Computer, TimeIngested, _ResourceId
| make-series HourTrend = count() on TimeIngested from ago(1h) to now() step 1m by Computer, _ResourceId
| join kind=inner (
    Heartbeat
    | extend TimeIngested = ingestion_time()
    | where TimeIngested {TimeRange}
    | summarize count() by Computer, TimeIngested, _ResourceId
    | make-series DailyTrend = count() on TimeIngested from {TimeRange:start} to now() step 1d by Computer, _ResourceId
    )
    on _ResourceId, Computer
| join kind=inner (
    Heartbeat 
    | extend TimeIngested = ingestion_time()
    | where TimeIngested {TimeRange}
    | summarize heartbeatPerHour = count() by bin_at(TimeIngested, 1h,{TimeRange:start}), Computer, _ResourceId
    | extend availablePerHour = iff(heartbeatPerHour > 0, true, false)
    | summarize totalAvailableHours = countif(availablePerHour == true) by Computer, _ResourceId
    | extend availabilityRate = iif(datetime_diff('hour',now(),{TimeRange:start}) == 0, 0.0, totalAvailableHours * 100.0 / (datetime_diff('hour',now(),{TimeRange:start})))
    )
    on _ResourceId, Computer
| sort by Computer asc
| project-away _ResourceId1, Computer1, _ResourceId2, Computer2

关键调整说明 #

• 显式生成TimeIngested字段：每个Heartbeat子查询开头都用extend TimeIngested = ingestion_time()生成计算字段，确保后续逻辑能正常引用；
• 强化join匹配条件：将join的匹配键从单一_ResourceId改为_ResourceId, Computer，避免不同服务器因资源ID冲突导致的匹配错误；
• 统一时间逻辑：所有时间过滤、分组、make-series的时间轴全部替换为TimeIngested，确保整个查询的时间维度一致；
• 容错处理：在availabilityRate计算中增加分母为零的判断，避免出现除以零的隐性错误。

排查无结果的额外技巧 #

1. 验证ingestion_time()数据：先用简单查询确认字段有效：

   Heartbeat | extend ti = ingestion_time() | take 10 | project Computer, TimeGenerated, ti
   

2. 临时替换join类型：将inner join改为left join，查看哪个子查询无返回数据，定位问题区间；
3. 检查时间范围变量：确认{TimeRange}的起止时间是否覆盖ingestion_time()的实际数据区间，服务器重启后批量 ingestion 的数据时间可能集中在重启后的时间段。

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