topic Re: How to produce rows for non-existing time buckets? in Deployment Architecture

How to produce rows for non-existing time buckets?

neilli92 — Tue, 29 Sep 2020 18:18:14 GMT

I have produced a table like this:

+----------+---------+---------+-------+
| _time    | field_1 | field_2 | count |
+----------+---------+---------+-------+
| 08:00:00 | A       | 1       | 2     |
+----------+---------+---------+-------+
| 08:00:00 | B       | 1       | 4     |
+----------+---------+---------+-------+
| 08:00:03 | B       | 3       | 1     |
+----------+---------+---------+-------+
| 08:00:03 | A       | 2       | 3     |
+----------+---------+---------+-------+

I want to know:
what's average & maximum count, per (field_1+field_2) combination, per second.
The problem is the _time is missing some seconds, so the stats count result only give me the aggregated results on existing time buckets.

How can I expand this table to include every missing time seconds, just by filling count=0, for each (field_1+field_2) combination? As long as I can do this, I can get the result simply by stats avg(count) max(count) by field_1 field_2.

Re: How to produce rows for non-existing time buckets?

deepashri_123 — Tue, 29 Sep 2020 18:14:19 GMT

hey neilli92,

You can try the query below:
...|stats avg(count) max(count) by field_1,field_2,_time | fillnull value=0
Let me know if this helps!!

Re: How to produce rows for non-existing time buckets?

neilli92 — Mon, 05 Mar 2018 13:24:45 GMT

thanks for reply, unfortunately it doesn't seem to solve my problem. Because there's no null value in the table, it's just I want to append new rows to semantically fill the missing time gap.

Re: How to produce rows for non-existing time buckets?

niketn — Mon, 05 Mar 2018 13:32:13 GMT

@neilli92, what is the command you have used to arrive at the table above in the question?

Re: How to produce rows for non-existing time buckets?

neilli92 — Mon, 05 Mar 2018 13:44:48 GMT

Hi, quite a huge query, but I did use bucket _time span=1s to buketize the time. Is there any way I can fill the missing time slots with this command?

I did try with timechart, but I couldn't make it work with BY two fields. I also tried to concatenate field_1 & field_2 to a single string field, but I have so many combinations, that timechart just gave up after showing a few, and put everything else in a other field.

Re: How to produce rows for non-existing time buckets?

niketn — Mon, 05 Mar 2018 13:50:40 GMT

@neilli92, can you try the following?

<yourBaseSearch> field1=* field2=*
| eval key=field_1." - ".field2
| timechart span=1s count by key limit=0 useother=f usenull=f

Re: How to produce rows for non-existing time buckets?

mayurr98 — Mon, 05 Mar 2018 14:01:52 GMT

I do not know if this will work or not but you can give it a try

<your base search> 
| eval field= field_1 + "|" + field_2 
| timechart limit span=1s count by field 
| untable _time field count 
| eval temp = split(field,"|") 
| eval field_1 = mvindex(temp,0) 
| eval field_2 = mvindex(temp,1) 
| stats sum(count) as count by field_1 field_2 _time | sort- _time

let me know if this helps!

Re: How to produce rows for non-existing time buckets?

livehybrid — Mon, 05 Mar 2018 14:05:28 GMT

Hi,
I think what you might be after is makecontinuous?
Have a look at http://docs.splunk.com/Documentation/Splunk/7.0.2/SearchReference/Makecontinuous

Here are two examples below of before/after using makecontinuous (and also using fillnull to fill the gaps).

Before:

After:

Re: How to produce rows for non-existing time buckets?

neilli92 — Tue, 29 Sep 2020 18:18:31 GMT

It almost worked! But the only problem is I have too many combinations of field_1 & field_2, it seems timechart can't handle so many, so it gave up after showing a few, and put everything else in a field OTHER. So after the untable command, I only get 19 useful, and one OTHER row that includes everything else...

How can I prevent timechart from truncating my results?

Re: How to produce rows for non-existing time buckets?

neilli92 — Mon, 05 Mar 2018 14:58:44 GMT

Hi thanks for reply.

It doesn't work, it complains about duplicate _time. Because for different field1+field2 combinations, I may have duplicate _time.

Re: How to produce rows for non-existing time buckets?

livehybrid — Mon, 05 Mar 2018 15:00:06 GMT

On the timechart command you can add a limit=1000 useother=f - That will allow 1000 results.

Re: How to produce rows for non-existing time buckets?

mayurr98 — Mon, 05 Mar 2018 15:29:44 GMT

I think limit=0 would give unlimited results. And yes to get rid of other use useother=f

Re: How to produce rows for non-existing time buckets?

neilli92 — Mon, 05 Mar 2018 16:07:49 GMT

Thanks, this does the magic!!
However it's worth noting that I need to do fillnull value=0 after timechart before untable.

Re: How to produce rows for non-existing time buckets?

livehybrid — Mon, 05 Mar 2018 16:09:35 GMT

Excellent - Got there in the end 🙂