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

[Deleted]

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 one set to the first value in the next set (so it looks like there is some data there). 

Is there a way to have the summarize/bin function or the timechart to use zero (or some default value) for the buckets that I don't have data for?

-thanks

[#2 · Accepted solution]

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

[#3]

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?

[#4]

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.

[#5]

You just saved my life! Both issues were at play and now the data looks correct. Thank you so much Evgeny :)

[#6]

How to make "make-series" work with time range on portal instead of hard coding the value/specifying the relative time range.

range(ago(1d), now(), 1h)

If I change the time range from portal to say last 7 days, how to make it work with "make-series" without editing the query ?

[#7]

@Evgeny Ternovsky 

Found this super helpful as well when I was creating a time-series graph of some data that had several days with zero values.  Much appreciated.