Splunk Search

SPLUNK search for only 3 continuous resulst

xiro
New Member

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

0 Karma
1 Solution

javiergn
SplunkTrust
SplunkTrust

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:

alt text

Thanks,
J

View solution in original post

0 Karma

to4kawa
Ultra Champion

use streamstats count with reset_on_change=t and where

0 Karma

javiergn
SplunkTrust
SplunkTrust

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:

alt text

Thanks,
J

0 Karma

xiro
New Member

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 🙂

0 Karma

javiergn
SplunkTrust
SplunkTrust

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.

0 Karma

xiro
New Member

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

0 Karma

javiergn
SplunkTrust
SplunkTrust

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

xiro
New Member

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

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...