DQL - Time interval recording without data

[gbaudart]

Hi there,

I'm currently working on BizEvent processing.
I would like to get the number of occurrences of the selected event at 1 minute intervals.

Here is my request:

fetch bizevents
| fieldsAdd event.type == "TEST"
| filter isNotNull(responsebodyfull)
| fieldsAdd Timestamp = timestamp
| parse responsebodyfull, "JSON(strict=false):json"
| expand TestServiceResponses=json[TestServiceResponses]
| parse TestServiceResponses, """LD '"correlationId":' STRING:correlationId LD '"montantEncaisse":' INTEGER:MontantEncaisse LD '"nR":' INTEGER:n LD '"n":' INTEGER:nC LD '"offre":' STRING:offre LD '"formule":' STRING:formule"""
| filterOut isNull(correlationId)
| fields Timestamp, correlationId, nR, nC, offre, montantEncaisse
| summarize count=count(), by: {`1m interval` = bin(Timestamp, 1m)}

I get what I'm looking for for all the intervals where I have events. The problem is that the intervals where I don't have any data don't have the value "0". They're just not recorded...
But I need them, because then I'd like to average the number of events per minute. To do this, I need to count all the minutes, not just the ones with data.

Here's my current result, I'm missing all the data for the intervals between 5am and 5:36am:

[PacoPorro]

What about using a metric extraction and then using the default(0) aggregator with the metric?

[gbaudart]

I would like to get the information I want without creating additional metrics (which would increase our DDU consumption).
Isn't there a way to add the equivalent of a "default(0)" to Grail/DQL?

[gbaudart · Accepted solution]

I found it possible to do this with the MakeTimeSeries function:

| makeTimeseries {NbCount=count(default:0)}, time:Timestamp, interval:1min
| expand NbCount
| summarize avg(NbCount)

You can even use "Expand" to do additional processing (SUM, AVG, etc.).

[gbaudart]

However, this does not seem to work for time intervals of less than a minute.