How to get the Weekly Stats based on the Username

Shashank_87 — Wed, 30 Sep 2020 00:26:56 GMT

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.

Re: How to get the Weekly Stats based on the Username

dmarling — Wed, 08 May 2019 14:08:46 GMT

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

topic How to get the Weekly Stats based on the Username in Splunk Search

How to get the Weekly Stats based on the Username

Re: How to get the Weekly Stats based on the Username