Finding Data Gaps Using Window Functions

My team focuses on processing streaming data. When there are gaps in streams for even a minute, it can cause inaccuracies in our output. Over the years, we’ve written a variety of different queries to find gaps in datasets, but I’ll describe one of our favorites below. If you deal with time series data too, be sure to check out the official docs for a lot more information about time series analysis.

 

For a quick visual inspection, I can use a make-series operator and a timechart:

T

| make-series count() on EventTime in range(ago(7d), now(), 1m)

| render timechart

 

With the chart, getting the exact start and end time of the gap means carefully moving my mouse around on the screen, and that’s prone to errors. The query below shows how I can get an ordered list of all the minutes that have data and then find any gaps bigger than five minutes.

let newSessionLimit = 5m;

let dataFrequency = 1m;

T

| make-series count() on EventTime in range(ago(7d), now(), dataFrequency)

| mv-expand EventTime to typeof(datetime), count_

| where count_ != 0

| order by EventTime asc

| extend prevTime = prev(EventTime)

| where EventTime - prevTime > newSessionLimit

| project GapStart=prevTime+dataFrequency, GapEnd=EventTime

 

 

The query makes use of one of the window functions: prev(). The window functions operate on serialized/ordered data to help you do operations involving nearby rows. The prev() function references a field in the previous row. This lets us compare the EventTime in our current row with the one right before it and determine if there was a gap big enough to interest us.

 

The window functions also give you the ability to add row numbers and do cumulative sums, but be warned that serializing your data can incur a high performance penalty. Employ them with caution, but when used wisely, they can open up a whole world of analysis that isn’t otherwise available.