Forum Discussion

Anonymous's avatar
Anonymous
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's avatar
    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
Evgeny Ternovsky's avatar
Evgeny Ternovsky
Icon for Microsoft rankMicrosoft
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's avatar
Burton Johnsey
Former Employee
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?

Resources