azurekqlazure-monitor-workbooks

How can I convert TimeGenerated to TimeIngested in this KQL query for an Azure Workbook?

I have been modifying an existing Azure Workbook for giving Windows server performance reports. It generally works, but TimeGenerated for Heartbeat is NOT accurate in Azure longterm. For instance, I brought down two test servers for a few hours, and once they came back online, Azure thought the heartbeats arrived every minute with the TimeGenerated column, making the chart inaccurate and displaying 100% uptime.

Now, I've been trying to get ingestion_time() into this query, because that IS accurate. There's a jump between the times the servers went down and up.

The problem is that merely replacing TimeGenerated with TimeIngested(after defining TimeIngested as a variable which is ingestion_time()) gives an empty query result and no errors.

I've been able to learn and solve problems in KQL because of very descriptive errors. But I have no idea why it doesn't like everything here, and I'm looking for at least a hint on how to make this visualization function using TimeIngested/ingestion_time().

enter image description here

enter image description here

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

Tried to get visualization from query with TimeIngested and received no errors or results.

Solution

  • The error is caused by the unexpected column name "$IngestionTime" after "summarize".

    let TimeIngested = ingestion_time();
Heartbeat
| where TimeGenerated > ago(60m)
| summarize count() by Computer, TimeIngested, _ResourceId
| getschema

    A quick fix is: by Computer, TimeIngested = TimeIngested, _ResourceId