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 )
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.
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.
thank you sir. it work. you are a legend