Splunk Search

Is there a way to count the series of consecutive identical events that are interrupted by another event?

harald_leitl
Path Finder

Hello,

Is there a way to count the series of consecutive identical events that are interrupted by another event?

Something like that:
data:
seq#;Event
1;A
2;A

3;B
4;B
5;B
6;A
7;B
8;B
9;A
10;A
11;B
the result should look like:
consecutive Event | count
A | 2
B | 3
A | 1
B | 2
A | 2
B | 1

thx

1 Solution

woodcock
Esteemed Legend

This will do it:

For mockup:

|noop| stats count AS Event | eval Event="A,A,B,B,B,A,B,B,A,A,B" | makemv delim="," Event | mvexpand Event

For the work

| autoregress Event | eval sameAsNext=if(Event=Event_p1,1,0) | streamstats current=t count(eval(sameAsNext=0)) AS sessionID | eventstats count AS inArowCount BY sessionID | search sameAsNext=0 | fields Event inArowCount

You may need to change Event to _raw, depending how your "events" are created.

View solution in original post

javiergn
SplunkTrust
SplunkTrust

Hi, did any of the comments below help you on this?
If yes, can you mark it as answered?
If not, is there any else we can do to help?
Unanswered questions make me sad 😞

0 Karma

harald_leitl
Path Finder

Sorry for the late answer.
Here is the solution for version 6.2.4:
|autoregress Event | eval sameAsNext=if(Event=Event_p1,1,0) | streamstats current=t count(eval(sameAsNext=0)) AS sessionID | eventstats count AS inArowCount BY sessionID | search sameAsNext=0 | fields Event inArowCount

0 Karma

woodcock
Esteemed Legend

This will do it:

For mockup:

|noop| stats count AS Event | eval Event="A,A,B,B,B,A,B,B,A,A,B" | makemv delim="," Event | mvexpand Event

For the work

| autoregress Event | eval sameAsNext=if(Event=Event_p1,1,0) | streamstats current=t count(eval(sameAsNext=0)) AS sessionID | eventstats count AS inArowCount BY sessionID | search sameAsNext=0 | fields Event inArowCount

You may need to change Event to _raw, depending how your "events" are created.

javiergn
SplunkTrust
SplunkTrust

Hi,

Assuming your fields are named "seq" and "Event" and look like the first table you mentioned below, this should work:

 your base search here
| sort seq
| streamstats current=f window=1 max(Event) as Previous_Event
| eval isConsecutive = if (Event == Previous_Event, 1, 0)
| streamstats count as count by Event reset_before=(isConsecutive==0)
| streamstats count(eval(isConsecutive==0)) as transaction_id
| stats max(count) as Consecutive by Event, transaction_id
| sort transaction_id
| fields - transaction_id

harald_leitl
Path Finder

I'm using 6.2.4, unfortunately the reset_before available in that version.

0 Karma

ranjith_kumar
Path Finder

Hi harald_leitl

if you want to count the series of consecutive identical events then you can use the below query to get the result.

index="myindex" sourcetype=access_combined | stats count by _raw
0 Karma

ranjith_kumar
Path Finder

Hi harald_leitl,

If you want to count a particular keyword or word from the events then you can use the below query :

 index=myindexname | rex field=_raw max_match=0 "(?<fieldTag>your keyword to search)" 
 | stats count by fieldTag
0 Karma

harald_leitl
Path Finder

as I said I don't want to count the events like:
count(A)|count(B)
5 | 6

what I want is to count the series of identical events that are interrupted by an other event. as described above.
I want a result like:
consecutive Event | count
A | 2
B | 3
A | 1
B | 2
A | 2
B | 1

0 Karma

ranjith_kumar
Path Finder

please replace with sourcetype=mysourcetypename over there. thank you

0 Karma

harald_leitl
Path Finder

i'm sorry, but that's not what i'm looking for. I want a result like that:
consecutive Event | count
A | 2 --> first series A seq#1+seq#2 = 2
B | 3 --> first series B seq#3+seq#4+seq#5 = 3
A | 1 --> 2nd series A seq#6 = 1
B | 2 --> 2nd series B seq#7+seq#8 = 2
A | 2 --> 3rd series A seq#9+seq#10=2
B | 1 --> 3rd series B seq#11 = 1

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...