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

[dekidekide]

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! 

[asamay]

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 

[marco_irmer]

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}

[dekidekide]

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.  

[marco_irmer]

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

1. Are 1 and 0 the only possible values for count() in your timeseries?
2. 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?