Forum Discussion

Deleted

Nov 06, 2017

Solved

How to have a time chart show zero for missing/null data.

Hi, I have a data set that when I use the summarize/bin over a 1 min interval has gaps in the data (hours) and when the timechart renders the graph the line goes directly from the last value in o...

Azure Log Analytics

azure monitor

Evgeny Ternovsky
Nov 07, 2017
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 Computer

The 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

Burton Johnsey

Copper Contributor

Feb 21, 2018

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

Microsoft

Feb 21, 2018

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.

Burton Johnsey
Copper Contributor
Feb 21, 2018
You just saved my life! Both issues were at play and now the data looks correct. Thank you so much Evgeny :)