Hi, I am looking for some help related to one of the issues. So what i want is weekly view of users in last 90 days with their authentication status. For example in a particular week if a user has "failed login" and "success login" both and suppose in the next week, he just got success, then I want it to be reported in a table like this -
FirstDayOfTheWeek User AuthenticationStatus
01-04-2019 test1@gmail.com Failed Success
08-04-2019 test1@gmail.com Success
08-04-2019 test2@gmail.com Failed
15-04-2019 test1@gmail.com Failed Success
There could be n number of users and I want to report all of them. Is there any way to do this?I am finding it a bit tricky. This is my query where I have appended success logins events with failed login events.
index="test" sourcetype=test_events "loginSuccess"
| dedup currentUsername
| eval type="Success"
| fields currentUsername type
| append
[ search index="test" sourcetype=test_events "loginFailed" 404
| rename username as currentUsername
| dedup currentUsername
| eval type="Fail"
| fields currentUsername type
]
I'd suggest using a stats to accomplish this instead of an append:
index="test" sourcetype=test_events "loginSuccess" OR ("loginFailed" 404)
| eval AuthenticationStatus=if(match(_raw, "loginSuccess"), "Success", "Failed")
| eval FirstDayOfTheWeek=relative_time(_time, "@w+1d")
| stats values(AuthenticationStatus) as AuthenticationStatus by FirstDayOfTheWeek currentUsername
| rename currentUsername as User
| fieldformat FirstDayOfTheWeek=strftime(FirstDayOfTheWeek, "%d-%m-%Y")
| sort 0 + FirstDayOfTheWeek User
The OR statement on the first line will have you search both of the conditions you want and then the values command on the stats transformation on line 4 will report the different Authentication statuses as a multivalued field. Give that a try and let me know if it works for you.
edited it to account for the currentUsername field instead of User