Deployment Architecture

How to produce rows for non-existing time buckets?

neilli92
New Member

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.

Tags (1)
0 Karma
1 Solution

mayurr98
Super Champion

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!

View solution in original post

livehybrid
Builder

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:
alt text

After:
alt text

0 Karma

neilli92
New Member

Hi thanks for reply.

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

0 Karma

mayurr98
Super Champion

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!

neilli92
New Member

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?

0 Karma

neilli92
New Member

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

0 Karma

livehybrid
Builder

Excellent - Got there in the end 🙂

0 Karma

livehybrid
Builder

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

0 Karma

mayurr98
Super Champion

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

0 Karma

niketn
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

neilli92
New Member

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.

0 Karma

niketn
Legend

@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
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

deepashri_123
Motivator

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!!

0 Karma

neilli92
New Member

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.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...