How to get the Weekly Stats based on the Username

[Shashank_87]

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
]

[dmarling]

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