Building for the Splunk Platform

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

adcom26
Explorer

hello 

i have a table like this 


actionuser_name
successuser1
failuser1
failuser2
failuser1
fail user1
successuser2
failuser2
failuser1
failuser2
failuser2



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

 

Labels (1)
0 Karma
1 Solution

to4kawa
Ultra Champion
| 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

View solution in original post

renjith_nair
SplunkTrust
SplunkTrust

@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

 

 

 

 

Happy Splunking!
0 Karma

adcom26
Explorer

it not working 

IDACTIONUSER
1successAdmin
2successuser2
3Failuser2
4Failuser2
5Failuser2
6successuser2
7FailAdmin
8FailAdmin
9Failuser2
10Failuser2
11FailAdmin
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)

Tags (1)
0 Karma

renjith_nair
SplunkTrust
SplunkTrust

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

Happy Splunking!
0 Karma

adcom26
Explorer

Hello,

 Screenshot_2020-07-07 TEST-LOGIN-FAIL Splunk 8 0 2(3).png 

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

line 1 :

adcom26_3-1594110827520.png

line 3 :

adcom26_4-1594110899076.png

line 7 :

adcom26_5-1594110954039.png

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

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

 
 
 

line 2
4.jpg

line 4
3.jpg

line 5, 

2.jpg

line 6,
1.jpg

 

 

 

 



 

 

 
Tags (1)
0 Karma

bowesmana
SplunkTrust
SplunkTrust

@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

0 Karma

adcom26
Explorer

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 

0 Karma

to4kawa
Ultra Champion
| 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
SplunkTrust
SplunkTrust

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.

 

0 Karma

adcom26
Explorer

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 

Tags (1)
0 Karma

to4kawa
Ultra Champion
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.



0 Karma
Get Updates on the Splunk Community!

Using Machine Learning for Hunting Security Threats

REGISTER NOW Seeing the exponential hike in global cyber threat spectrum, organizations are now striving more ...

Security Highlights | November 2022 Newsletter

 November 2022 2022 Gartner Magic Quadrant for SIEM: Splunk Named a Leader for the 9th Year in a RowSplunk is ...

Platform Highlights | November 2022 Newsletter

 November 2022 Skill Up on Splunk with our New Builder Tech Talk SeriesCan you build it? Yes you can! *play ...