get row value when the last 3 value is the same

[adcom26]

hello 

i have a table like this 

ID	ACTION	USER
1	success	Admin
2	success	user2
3	Fail	user2
4	Fail	user2
5	Fail	user2
6	success	user2
7	Fail	Admin
8	Fail	Admin
9	Fail	user2
10	Fail	user2
11	Fail	Admin
12	Fail	user2
13	Fail	user2

if I want to show by users all the action (success) if the last 3 previous action = fail 

the result shoud show :

--the raw with (id = 1 ==> because the admin in her previous status have 3 fail (id=7, id=8, id=11) 
--the raw with (id = 2 ==> because the  user2 in her previous status have 3 fail (id= 3, id =4, id=5)
--the raw with (id = 6 ==> because the  user2 in her previous status have 3 fail (id= 9, id=10, id=12)

and if I want to show by users all the action (fail) if the last 3 previous action = fail 

the result shoud show :

--the raw with (id = 9 ==> because the  user2 in her previous status have 3 fail (id=10, id=12, id= 13 )

[rnowitzki · Accepted solution]

Hi @adcom26 ,

This took some cpu time from my brain :).
Not sure if is possible with less SPL, but it should work with:

| makeresults
| eval ID = "1 2 3 4 5 6 7 8 9 10 11 12 13" 
| makemv ID
| mvexpand ID
| eval action = case(ID=1,"success",ID=2,"success",ID=3,"fail",ID=4,"fail",ID=5,"fail",ID=6,"success",ID=7,"fail",ID=8,"fail",ID=9,"fail",ID=10,"fail",ID=11,"fail",ID=12,"fail",ID=13,"fail")
| eval user = case(ID=1,"admin",ID=2,"user2",ID=3,"user2",ID=4,"user2",ID=5,"user2",ID=6,"user2",ID=7,"admin",ID=8,"admin",ID=9,"user2",ID=10,"user2",ID=11,"admin",ID=12,"user2",ID=13,"user2")

| sort - user, ID
| streamstats count BY action, user reset_after="("match(action,\"success\")")"
| streamstats current=false window=1 first(action) as prev_action first(count) as prev_count first(user) as prev_user
| eval success=if(action="success" AND prev_action="fail" AND prev_count>=3 AND user=prev_user,"true","false")
| eval fail=if(action="fail" AND prev_action="fail" AND prev_count>=3 AND user=prev_user,"true","false")
| where success="true" OR fail="true"
| fields - prev* fail success
| sort ID

You only need the lines starting with the first sort command. The lines above that are just to create a table with your sample data.

I think there is one problem with the "failed" ones. The SPL would output all lines after one user "collected" 3 fails, as I don't reset the "fails" after the first 3 where found. So what I mean is, that it would show the 4th "fail" for a user, but also the 5th and the 6th etc., if there is no "success" in between....If that is wrong, the "reset after" clause would have to be somehow extended.

Hope it helps.
BR
Ralph
--
Karma and/or Solution tagging appreciated.

[adcom26]

thank you sir. it work.  you are a legend