Security

Search query for consecutive events

Stives
Explorer

Dear Splunkers, I would like ask your advice in order to complete following search result. My table checks for consecutive level breaches events in window of 3 counts.

ACC

CR

count

0

0

1

0

0

2

0

0

3

1

1

1

1

0

2

1

0

3

2

1

1

3

1

2

4

1

3

 

 

 

 

If there is a level breach CR column will change to 1 and the ACC column will change to upcoming number. Now I would like to create an alert if 3 consecutive levels breached as shown in bolded example in bold. 
Can you suggest how to complete the query and display only 3 consecutive results so that I can create an Alert?

Thank you

Labels (1)
0 Karma
1 Solution

PickleRick
SplunkTrust
SplunkTrust

You're getting close.

One streamstats is not enough because you can't "pull" events you already passed while processing the stream.

Assuming you want to find when you have at least three consecutive ACC=1, you can do it like this

 

| eval Description=case(RML<104.008, "0", RML>108.425, "1", RML>=104.008, "OK", RML<=108.425, "OK")
| eval Warning=case(Description==0, "LevelBreach", Description==1, "LevelBreach")
| table LWL UWL RML
| eval CR=if(RML<UWL,"0",if(RML>LWL,"1","0"))
| streamstats window=3 sum(ACC) as running_count

 

This will mark the last of three consecutive ACC=1 with running_count=3.

So we're on the right track so far we've found where our streak ends. Now we have to do a little trick since we can't pull events "from behind", we need to

| reverse

So that we're looking at our events in the other order. Now we know that event with running_count=3 will be starting our 3-event streak. So now we have to mark our 3 events looking forward

| streamstats current=t window=3 max(running_count) as mark_count

This will give us a value of markcount=3 for all events for which any of the last three events had  running_count of 3 (which means that we're no further than 3 events from the _last_ event of our 3 event streak).  Now all we have to do is find all those events we marked

| where mark_count=3

And now we can just tidy up after ourseves

| fields - running_count markcount
| reverse

And there you have it.

Unfortunately since it uses the reverse command it can be quite memory consuming (and might even have some limits I'm not aware of at this time).

View solution in original post

0 Karma

Stives
Explorer

Thank you PickleRick, I already got streamstats in my search query but got more results not only consecutive and I want isolate 3 consecutive results (see end of queary):

| eval Description=case(RML<104.008, "0", RML>108.425, "1", RML>=104.008, "OK", RML<=108.425, "OK")
| eval Warning=case(Description==0, "LevelBreach", Description==1, "LevelBreach")
| table LWL UWL RML
| eval CR=if(RML<UWL,"0",if(RML>LWL,"1","0"))
| accum CR AS ACC | streamstats window=3 reset_after="count=3" count 
0 Karma

PickleRick
SplunkTrust
SplunkTrust

You're getting close.

One streamstats is not enough because you can't "pull" events you already passed while processing the stream.

Assuming you want to find when you have at least three consecutive ACC=1, you can do it like this

 

| eval Description=case(RML<104.008, "0", RML>108.425, "1", RML>=104.008, "OK", RML<=108.425, "OK")
| eval Warning=case(Description==0, "LevelBreach", Description==1, "LevelBreach")
| table LWL UWL RML
| eval CR=if(RML<UWL,"0",if(RML>LWL,"1","0"))
| streamstats window=3 sum(ACC) as running_count

 

This will mark the last of three consecutive ACC=1 with running_count=3.

So we're on the right track so far we've found where our streak ends. Now we have to do a little trick since we can't pull events "from behind", we need to

| reverse

So that we're looking at our events in the other order. Now we know that event with running_count=3 will be starting our 3-event streak. So now we have to mark our 3 events looking forward

| streamstats current=t window=3 max(running_count) as mark_count

This will give us a value of markcount=3 for all events for which any of the last three events had  running_count of 3 (which means that we're no further than 3 events from the _last_ event of our 3 event streak).  Now all we have to do is find all those events we marked

| where mark_count=3

And now we can just tidy up after ourseves

| fields - running_count markcount
| reverse

And there you have it.

Unfortunately since it uses the reverse command it can be quite memory consuming (and might even have some limits I'm not aware of at this time).

0 Karma

Stives
Explorer

Thank you so much PickleRick. It works for me well. I was able isolate 3 consecutive results. I appreciate

0 Karma

PickleRick
SplunkTrust
SplunkTrust

Your explanation is a bit unclear but it looks like a job for the streamstats command with a window size of 3.

0 Karma
Get Updates on the Splunk Community!

Exporting Splunk Apps

Join us on Monday, October 21 at 11 am PT | 2 pm ET!With the app export functionality, app developers and ...

Cisco Use Cases, ITSI Best Practices, and More New Articles from Splunk Lantern

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Build Your First SPL2 App!

Watch the recording now!.Do you want to SPL™, too? SPL2, Splunk's next-generation data search and preparation ...