Pie chart from Log Analytics aggregation

Question

I am trying to write a simple aggregation query in Kusto that would show me how many machines are at the moment alive, based on heartbeat.

I can get the alive status with the following query:

Heartbeat 
| summarize LastHeartbeat=max(TimeGenerated) by Computer

This yields output similar to:

Computer LastHeartbeat [UTC]

VM1 5/4/2020, 3:23:40.227 PM
VM2 5/4/2020, 2:59:46.780 PM

But then I want to add a column that would be max(TimeGenerated) not by Computer, but in total. I was trying to do aggregation by multiple terms, but apparently this is not possible:

Heartbeat 
| summarize LastHeartbeat=max(TimeGenerated) by Computer, Latest=max(TimeGenerated)
| extend isAlive = (LastHeartbeat == Latest)

My question is - how can I use the data to render a pie chart showing alive/dead machines? How can I use this data to render a piechart showing ratio of OFF/ON machines?

Yoni L. Yoni L. · Accepted Answer · 2020-05-04T16:00:58

you could try something like this:

datatable(vm_id:string, dt:datetime)
[   
    'VM1', datetime(5/3/2020, 3:23:40.227 PM),
    'VM2', datetime(5/4/2020, 2:59:46.780 PM),
    'VM3', datetime(5/4/2020, 2:59:46.780 PM),
]
| as T // this is your summary table, where each vm_id has a single entry with its max datetime
| extend status = case(dt == toscalar(T | summarize max(dt)), "ON", "OFF") 
| summarize count() by status
| render piechart

Pie chart from Log Analytics aggregation

1 Answers