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
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.
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 😞
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
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.
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
I'm using 6.2.4, unfortunately the reset_before available in that version.
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
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
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
please replace with sourcetype=mysourcetypename over there. thank you
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