Forum Discussion
How to have a time chart show zero for missing/null data.
Hi,
Please check out the make-series function to achieve this. For example, instead of saying:
Heartbeat| where TimeGenerated > ago(1d)| summarize count() by Computer, bin(TimeGenerated, 1h)You can say:
Heartbeat| make-series count() default=0 on TimeGenerated in range(ago(1d), now(), 1h) by ComputerThe output is a bit different for make-series (you get an array for datetimes and an array for the count for each computer rather than a row combination for each), so if you want the data in the same format that summarize produces, you can do so via mvexpand:Heartbeat| make-series count() default=0 on TimeGenerated in range(ago(1d), now(), 1h) by Computer| mvexpand count_, TimeGenerated
MicrosoftHi,
Please check out the make-series function to achieve this. For example, instead of saying:
You can say:
make-series with mvexpand doesn't work in a predictable way for me.
Take for example:
Perf | where CounterName == "Thread Count" | where InstanceName == "AgentService" | make-series avg(CounterValue) on TimeGenerated in range(ago(1d), now(), 30m) by Computer | mvexpand TimeGenerated , avg_CounterValue | project TimeGenerated=todatetime(TimeGenerated)-8h, Computer, Hits = toint(avg_CounterValue)
It will return 0 for all days when it should return 6.
If I run just up to the make-series part and examine one of the Computer series, the data looks partially correct. The last 24 hours for a given computer show 6, everything else is 0.
If I reduce the range from 7d to 1d, the data looks correct.
Is there something wrong with my query? Is there some limitation or bug I am running into?
- Evgeny Ternovsky
Without seeing actual data, a couple of possibilities come to mind that might affect this:
- mvexpand has a default length limit of 128 rows that can be overridden (details in docs).
- the range limits specified in make-series do not today override the timepicker next to the run button. It's possible that the time picker is set to a shorter time range than that in your make-series, resulting in missing data. A guaranteed way to verify this is by parameterizing your range:
let span = 7d; Perf | where TimeGenerated > ago(span) | where CounterName == "% Processor Time" | make-series avg(CounterValue) on TimeGenerated in range(ago(span), now(), 30m) by Computer | mvexpand TimeGenerated , avg_CounterValue | project TimeGenerated=todatetime(TimeGenerated)-8h, Computer, Hits = toint(avg_CounterValue)
Adding the extra | where TimeGenerated... clause overrides the time picker.
Please give these two a shot, and if still not working, let me know and we can dig deeper.
You just saved my life! Both issues were at play and now the data looks correct. Thank you so much Evgeny :)