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
.
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!
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:
Hi thanks for reply.
It doesn't work, it complains about duplicate _time. Because for different field1+field2
combinations, I may have duplicate _time.
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!
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?
Thanks, this does the magic!!
However it's worth noting that I need to do fillnull value=0
after timechart
before untable
.
Excellent - Got there in the end 🙂
On the timechart
command you can add a limit=1000 useother=f
- That will allow 1000 results.
I think limit=0 would give unlimited results. And yes to get rid of other use useother=f
@neilli92, what is the command you have used to arrive at the table above in the question?
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.
@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
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!!
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.