show list / get value when the last 3 value is the same

[adcom26]

hello 

i have a table like this 

action	user_name
success	user1
fail	user1
fail	user2
fail	user1
fail	user1
success	user2
fail	user2
fail	user1
fail	user2
fail	user2

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

 

[renjith_nair]

@adcom26 ,

Try,

"your search"
| sort user_name,id desc
| streamstats count by user_name,action reset_on_change=true
| streamstats last(action) as prev_action,last(count) as prev_count current=f window=1
| where action=="success" AND prev_action=="fail" AND prev_count>=3
| fields action,user_name

[adcom26]

it not working 

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

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)

[renjith_nair]

@adcom26,
Just add the sort command and it should be working. Sorry I didn't have an instance to test

[adcom26]

Hello,

  

 what i want is : 
if number of previous failure= 3 and the action = success then  the result should be :

line 1 :

line 3 :

line 7 :

----------------------------------

if number of previous failure= 3 and the action = failure then  the result should be :

line 2

line 4

line 5, 

line 6,

[bowesmana]

@adcom26 

Are you sure about the second part. If bilel is in the results for line 4, 5 and 6, then if he has just had success at line 7, why does 1 failure then cause him to show up. It doesn't appear that you want all failures to be listed as Administrator is only shown once

[adcom26]

i'm sorry  You are right . the result should be only the line 2 ( the administrator failure)

but if i don't have success in line 7 .. the result will be correct 

[to4kawa · Accepted solution]

| makeresults
| eval _raw="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"
| multikv forceheader=1
| table ID,ACTION,USER
| streamstats count(eval(ACTION="success")) as session by USER
| stats first(ID) as ID list(ACTION) as ACTION count(ACTION) as flag by session USER
| where flag > 3
| table ID USER

[bowesmana]

Nice @to4kawa I spent a bit of time trying to figure this out.

It doesn't work quite as described though in that if there are 4 fails and no success, it will also show that sequence of failures.

[adcom26]

thank you so much it's working 

but, what should I do if I want to show fail action when the 3 last previous action = fail

according to my table the result should be : 

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


I try to change 

| streamstats count(eval(ACTION="success")) as session by USER

by 

| streamstats count(eval(ACTION="failure")) as session by USER

 but it not working 

[to4kawa]

according to my table the result should be : 

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

Your assumption is incorrect.

| makeresults
| eval _raw="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"
| multikv forceheader=1
| table ID,ACTION,USER
| reverse
| streamstats global=f count(eval(ACTION="Fail")) as count_action  list(ACTION) as listed by USER
| reverse
| where match(listed,"Fail") AND count_action >= 3 AND ACTION="Fail"

check this.