topic Re: DQL - Time interval recording without data in DQL

DQL - Time interval recording without data

gbaudart — Wed, 28 Feb 2024 08:47:27 GMT

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:

Re: DQL - Time interval recording without data

PacoPorro — Wed, 28 Feb 2024 10:27:40 GMT

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

Re: DQL - Time interval recording without data

gbaudart — Wed, 28 Feb 2024 13:20:51 GMT

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?

Re: DQL - Time interval recording without data

gbaudart — Mon, 25 Mar 2024 09:11:48 GMT

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.).

Re: DQL - Time interval recording without data

gbaudart — Mon, 25 Mar 2024 09:18:00 GMT

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