SPLUNK search for only 3 continuous resulst

[xiro]

Hello,
I have a table:

time        available
------      -----------
09:00         OK
09:05         time_out
09:10         time_out
09:15         OK
09:20         OK
09:25         OK
09:30         timeout
09:35         OK
09:40         OK
09:45         time_out
09:50         time_out
09:55         time_out
10:00         OK

What I need is to select only records, where at least 3 continuos records have the availability == time_out. So in this case the correct output from Select would be:

09:45
09:50
09:55

Any idea how to perform this kind of search?

thank you very much

[to4kawa]

use streamstats count with reset_on_change=t and where

[javiergn · Accepted solution]

Hi @xiro,

Let me know if the this helps. I have replicated your sample on a csv and tested with the following SPL.

NOTE: Keep in mind your time only displays hours and minutes and therefore duplicates will occur when it spans more than 24 hours. Please ensure your time values are unique for the defined time span and also double check they can be sorted accordingly (use strptime function for instance). If you find any issues please post a more detailed sample perhaps.

| inputcsv test.csv
| sort limit=0 time
| streamstats reset_on_change=t count(eval(available="time_out")) as time_out_count  by available
| sort limit=0 - time
| streamstats window=3 reset_on_change=t max(time_out_count) as time_out_max  by available
| where time_out_max >= 3
| table time

Output:

Thanks,
J

[xiro]

Many thanks guys so far. You are the best 🙂

ok basicaly this is my complete thing:

inputcsv test.csv
| stats
count(eval(STATUS="ACTIVE")) as success_count
count(eval(STATUS!="ACTIVE")) as failure_count
by DATABASE_NAME| eval percentage=(success_count/(success_count+failure_count))*100 

I'm checking the STATUS field. If it's == ACTIVE, then count it as success,
if it's other then ACTIVE it counts as fail.
At the end I'm converting it to percentage.
The only thing I need is to count it as fail ONLY after 3 continuous fails after each other

Many thanks for the suggestions 🙂

[javiergn]

Then you could do something like:

 | inputcsv test.csv
 | sort limit=0 time
 | streamstats reset_on_change=t count(eval(available="time_out")) as time_out_count  by available
 | sort limit=0 - time
 | streamstats window=3 reset_on_change=t max(time_out_count) as time_out_max  by available
 | eval isFailure = if(time_out_max >= 3, 1, 0)
 | stats
 count as total_count
 count(eval(isFailure=1)) as failure_count
 by DATABASE_NAME
| eval success_count = total_count - failure_count
| eval percentage=(success_count/(success_count+failure_count))*100  

Or whichever filter expression you want to include within the stats eval.

[xiro]

@javiergn thanks will definetly try this one out...

[javiergn]

Sure, good luck.
By the way, do not forget accept the answer if it solved your problem so that others can benefit from it in the future.

If not, please post what your remaining issue is and we'll try to help.

Regards,
J

[gcusello]

Hi @xiro,
you have a log every five minuts with the status (OK or time_out), is it correct?

So you could run a search every five minutes with a time frame of 15 minutes anche check if the count is=3, something like this.

index=your_index earliest=-15m@m latest=now Status="time_out"
| stats count BY Status
| where count=3

Ciao.
Giuseppe

[xiro]

hello @Giuseppe,

well I need to perform such search in the time frame of last 30 days.
So what I need is to have listed all records, where at least 3 continuos availabilities are time_out for the last 30 days

thank you