topic Re: Search query for consecutive events in Security

Search query for consecutive events

Stives — Fri, 16 Aug 2024 13:13:35 GMT

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

Re: Search query

PickleRick — Fri, 16 Aug 2024 12:58:43 GMT

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

Re: Search query for consecutive events

Stives — Fri, 16 Aug 2024 14:15:43 GMT

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

Re: Search query for consecutive events

PickleRick — Fri, 16 Aug 2024 18:43:36 GMT

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

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).

Re: Search query for consecutive events

Stives — Mon, 19 Aug 2024 06:40:11 GMT

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