Query Details
Make Series For Gaps
Query
// Example to perform a aggregation by period where they may be no data for a given period.
let startdate = todatetime("2016-11-01");
let enddate = todatetime("2018-11-15");
OfficeActivity
| where TimeGenerated between (startdate .. enddate)
| make-series count(Operation) default=0
on TimeGenerated in range(startdate, enddate, 1d)
by OfficeWorkload
| mvexpand TimeGenerated to typeof(datetime),
count_Operation to typeof(double)
// Same as above however using a numbers table approach similar to the method used in SQL
let startdate = todatetime("2018-11-01");
let startdate2 = todatetime("2018-11-07");
let enddate = todatetime("2018-11-15");
range Day from startdate to enddate step 1d
| extend CountOfSomething = 0
| join kind=fullouter
(range Day from startdate2 to enddate step 1d
| extend CountOfActual = 1
) on Day
| project Day,Value=iff(isnull(CountOfActual), CountOfSomething, CountOfActual)
Explanation
The query is performing an aggregation by period, where there may be no data for a given period. It uses the OfficeActivity table and filters the data based on a specified time range. It then creates a series of counts for each operation, with a default value of 0 if there is no data. The query also includes a second approach using a numbers table, similar to SQL. It creates a range of days and extends it with a count of something. It then performs a full outer join with another range of days and extends it with a count of actual. Finally, it projects the day and a value based on whether the count of actual is null or not.
Details
Rod Trent
Released: March 26, 2020
Tables
OfficeActivity
Keywords
Keywords:OfficeActivity,TimeGenerated,OfficeWorkload,count,make-series,mvexpand,typeof,range,step,extend,join,kind,fullouter,project,iff,isnull
Operators
wherebetweenmake-seriesonmvexpandtypeofrangeextendjoinkindprojectiffisnull