topic Re: DQL makeTimeseries how to return Last Known Value from series for time with no data/NULL in DQL

Return Last Known Value in Time-Series Using DQL makeTimeseries

dekidekide — Thu, 18 Dec 2025 12:30:58 GMT

Hi,

I have a data/logs for job run status, which logs records for "Success" or "Fail" each run - job runs anytime base on schedule (e.i. 1-3 times a day and/or can be run manually). i'm tying to use makeTimeseries to capture alerts for Fail runs, but due to the nature of job run, there would be timeseries that won't have data or NULL (e.i. "| makeTimeseries countIf(status=="Fail"), interval: 1m, by: {task_name}" >> count(): 0, null, null, 1, null, null, null, 0, null, null .... ) .. 1 = Fail, 0 = Success, null = no data.

Is there a way in makeTimeseries where if the result of the count is null, set/change null with the "Last Known Value" from the series? so if the timeseries result is like this "count(): 0, null, null, 1, null, null, null, 0, null, null, ..." i would like it to be changes like this "count(): 0, 0, 0, 1, 1, 1, 1, 0, 0, 0, ...". using default:0 won't work in my case as it will also convert all "nulls" after value "1" to zero.

Thanks!

Re: DQL makeTimeseries how to return Last Known Value from series for time with no data/NULL

asamay — Thu, 20 Feb 2025 17:53:06 GMT

Hey,

Would doing a coalesce() funtion work for you in this scenario? Here are a couple of simple examples:

Also here is the documentation on that function: here

Re: DQL makeTimeseries how to return Last Known Value from series for time with no data/NULL

marco_irmer — Fri, 21 Feb 2025 23:10:18 GMT

Have you considered using summarize() command rather than makeTimeSeries()?

You could combine it with the takeLast() function to show the most recent status:

| summarize latestStatus = takeLast(status), by:{task_name}

Alternatively, you could also count up the number of observed log records by task_name and status:

| summarize count(), by: {task_name, status}

Re: DQL makeTimeseries how to return Last Known Value from series for time with no data/NULL

dekidekide — Wed, 26 Feb 2025 01:48:58 GMT

thanks, asamay/marco. i actually gonna need it in time series as we will use davis ai/anomaly detector (which requires time series). i also tried coalesce() function but i can't get the desired result.

Re: DQL makeTimeseries how to return Last Known Value from series for time with no data/NULL

marco_irmer — Thu, 06 Mar 2025 14:43:44 GMT

I think I may have a hack you can use in certain circumstances, but it depends on two things:

Are 1 and 0 the only possible values for count() in your timeseries?
How many consecutive null values occur in the data? Is it relatively few (10 or fewer) or do you have longer strings of null values?

Re: DQL makeTimeseries how to return Last Known Value from series for time with no data/NULL

ADawson — Thu, 21 Aug 2025 05:35:24 GMT

I have a similar requirement and was wondering if a solution was ever found for this thread.

Requirement - Create a Davis detector that can raise/clear problems based on log entries that represent a bad/good states.

eg.

Raise problem when a log entry representing a bad state is seen in the logs.

Keep the problem alive (Bonus points for being able to keep the problem alive if its a long time between the bad log entry and the good log entry - hours/days)

Then clear the problem when a log entry representing a good state is seen in the log.

Re: DQL makeTimeseries how to return Last Known Value from series for time with no data/NULL

IzabelaRokita — Thu, 18 Dec 2025 12:30:30 GMT

Hey @dekidekide @ADawson ,
I just wanted to check in and see if you still need help with this. If so, I’d be happy to look into it for you! 😊
Please let me know what works best for you.

Re: DQL makeTimeseries how to return Last Known Value from series for time with no data/NULL

ADawson — Thu, 18 Dec 2025 22:25:09 GMT

Hi @IzabelaRokita ,

That would be great if you can provide some help.

We have a partial solution which is OK for short duration issues.

However, Im worried if we experience a longer issue such as on a Friday night where there may be no further log entries written for a few days. I would like the related problem to still be open on Monday if there is still an issue.

And I was hoping to avoid added complexities such as Business Events or Workflows as a work-around if possible.

Example of Davis Detector query -

fetch logs, bucket:{"Xlog"}
| filter in(XeventId, {"DISCONNECTED", "CONNECTED"})
| fieldsAdd state = if(XeventId == "DISCONNECTED", 1, else: if(XeventId == "CONNECTED", 0, else: 2 ))
| makeTimeseries count=count(default: 0), by:{dt.entity.host, host.name}, interval:1m