topic Re: How to combine continuous values within 5 minutes? in Splunk Search

How to combine continuous values within 5 minutes?

syokota_splunk — Mon, 13 Nov 2017 10:19:59 GMT

Hi Experts,
I'd like to filter the record when the "delta_value" has the same value within 15 seconds (or repeats 3 times).

The sample value is below.

No, _time, value1, delta_value
1, 05/26/2017 06:32:50, 5.949602127, 0
2, 05/26/2017 06:32:55, 15.949602127, 10
3, 05/26/2017 06:33:00, 16.949602127, 1
4, 05/26/2017 06:33:05, 17.949602127, 1
5, 05/26/2017 06:33:10, 18.949602127, 1
6, 05/26/2017 06:33:15, 17.949602127, 1
7, 05/26/2017 06:33:20, 17.949602127, 0

And I wish to finalize below search outputs.

Ideal Final Result pattern A.

No, _time, value1, delta_value
1, 05/26/2017 06:32:50, 5.949602127, 0
2, 05/26/2017 06:32:55, 15.949602127, 10
3, 05/26/2017 06:33:00, 16.949602127, 1
7, 05/26/2017 06:33:20, 17.949602127, 0

Ideal Final Result pattern B

No, _time, value1, delta_value
1, 05/26/2017 06:32:50, 5.949602127, 0
2, 05/26/2017 06:32:55, 15.949602127, 10
7, 05/26/2017 06:33:20, 17.949602127, 0

How do you do it? Any comments and any idea welcome!

Re: How to combine continuous values within 5 minutes?

gcusello — Mon, 13 Nov 2017 10:56:05 GMT

Hi syokota [Splunk],
please detail more you answer first Final Result or second one?
in the first case

your_search
| stats values(No) As No earliest(_time) AS _time values(delta_value) AS delta_value BY value1 
| table No  _time value1 delta_value

In the second case

your_search
| stats values(No) As No earliest(_time) AS _time values(delta_value) AS delta_value count BY value1 
| where count=1
| table No  _time value1 delta_value

Bye.
Giuseppe

Re: How to combine continuous values within 5 minutes?

syokota_splunk — Mon, 13 Nov 2017 11:06:23 GMT

Sorry to bothering you.
The fields of “No” is not existing in both actual raw log and ideal final results.

Please ignore the No column.

Re: How to combine continuous values within 5 minutes?

gcusello — Mon, 13 Nov 2017 11:18:19 GMT

without the No column in table command and values(No) As No in stats command, does it run?
Bye.
Giuseppe

Re: How to combine continuous values within 5 minutes?

niketn — Tue, 29 Sep 2020 16:46:36 GMT

@syokota_splunk, I was able to create query to generate sample pattern A, where same delta_value if repeated for more than 2 times will all be merged as one row. Please try out run anywhere search below, which is based on streamstats which requires events to be sorted with time i.e. ascending or descending (your sample data seemed sorted chronologically which suffices the need)
PS: Commands till | table generate mock data as per the question. Only continuous delta_values increase counter, if there is a different value the counter resets.

| makeresults 
| eval data="1,05/26/2017 06:32:50,5.949602127,0;2,05/26/2017 06:32:55,15.949602127,10;3,05/26/2017 06:33:00,16.949602127,1;4,05/26/2017 06:33:05,17.949602127,1;5,05/26/2017 06:33:10,18.949602127,1;6,05/26/2017 06:33:15,17.949602127,1;7,05/26/2017 06:33:20,17.949602127,0;8,05/26/2017 06:33:40,13.889602127,0" 
| makemv data delim=";" 
| mvexpand data 
| eval data=split(data,",") 
| eval No=mvindex(data,0), _time=mvindex(data,1), value1=mvindex(data,2), delta_value=mvindex(data,3) 
| table No _time value1 delta_value 
| eval _time=strptime(_time,"%m/%d/%Y %H:%M:%S") 
| streamstats count as sameCount by delta_value reset_on_change=true
| eventstats max(sameCount) as maxCounter by delta_value
| eval maxCounter=case(sameCount=1 AND maxCounter>2,1,true(),maxCounter)
| search maxCounter=1 OR maxCounter=2

Your second pattern is a bit complicated. I will give that a try, but hopefully someone will be able to solve before that 🙂

Re: How to combine continuous values within 5 minutes?

syokota_splunk — Tue, 29 Sep 2020 16:46:52 GMT

Actually the "No" column is not existing. Please refer below raw data.

_time, value1, delta_value
05/26/2017 06:32:50, 5.949602127, 0
05/26/2017 06:32:55, 15.949602127, 10
05/26/2017 06:33:00, 16.949602127, 1
05/26/2017 06:33:05, 17.949602127, 1
05/26/2017 06:33:10, 18.949602127, 1
05/26/2017 06:33:15, 17.949602127, 1
05/26/2017 06:33:20, 17.949602127, 0

Re: How to combine continuous values within 5 minutes?

syokota_splunk — Mon, 13 Nov 2017 22:00:49 GMT

Thank you niketnilay!
But I'm sorry, the "No" column is not existing in raw log that's my fault..

And "_time" is dynamically changing I mean not only sample data also new data will come, so you mentioned SPL of no.2 raw is difficult to do it.

Re: How to combine continuous values within 5 minutes?

skoelpin — Mon, 13 Nov 2017 22:05:16 GMT

You could try something like this

index=xxx sourcetype=xxx | bin delta_value span=15s | timechart cont=true count

You could also look into the | makecontineous command

http://docs.splunk.com/Documentation/Splunk/7.0.0/SearchReference/ListOfSearchCommands
"Makes a field that is supposed to be the x-axis continuous (invoked by chart/timechart)"

Re: How to combine continuous values within 5 minutes?

niketn — Tue, 14 Nov 2017 04:42:29 GMT

@syokota, I have extracted No field but have not used it in any logic.

And what I meant by events sorted by time (in chornologica or reverse), is that you would need to use sort or reverse command before using streamstats as it generates the stats on streaming manner and unsorted events may give incorrect stats.

My commands till | table just created the dummy table as per the data provided in the question. You would need to create your base query in a way that. Your output is sorted by _time (even if it does not have No field that would be fine).

If your default output table is in reverse chronological order then streamstats might need adjustment (not sure). But will perform better because one additional | reverse command can be avoided before streamstats. Please do try out and confirm. If it does not work give the sample output from your base search which displays _time, value1, delta_value, in the same order.

Re: How to combine continuous values within 5 minutes?

skoelpin — Mon, 27 Nov 2017 14:57:27 GMT

Did this work for you?

Re: How to combine continuous values within 5 minutes?

syokota_splunk — Mon, 27 Nov 2017 20:19:43 GMT

I've done with below SPL.

index="sensor"
| sort + _time
| streamstats current=f window=1 last(value1) as last_value1
| eval diff=abs(value1-last_value1),diff=if(diff>180,abs(value1+last_value1-360),diff)
| streamstats current=t window=50 values(diff) as last_diff_50
| eval countmv=mvcount(last_diff_50)
| search countmv!=1
| table _time  value1