Average Failed Logins by Day of Week For Last 90 Days

[SplunkLunk]

Greetings,

The search I am using currently is giving me the total number of failed logins by day of the week for the last 90 days:

| base search event="login_fail"
| bin _time as date span=1d
| eval weekday=strftime(_time, "%A")
| stats count by weekday
| stats avg(count) by weekday

The problem is I want to find the daily average number of failed logins for the last 90 days.  For example, in the search above is shows a total of 625 failed logins for Fridays.  The last line of the search isn't providing any value so it's not needed.  Is there a way to modify my search to get that average number instead of the total count for Fridays?  For example, since there are ~12 Fridays over the last 90 days that would come out to ~52 failed logins on Fridays (12 * 52 is where I get the 625).    Thanks for any help.

[danielansell]

Try this:

| base search event="login_fail"
| eval weekday=strftime(_time,"%A"), date=strftime(_time,"%x")
| stats count by date, weekday
| stats avg(count) by weekday

[SplunkLunk]

I want to do what the person in this old post tried to do but the solution there isn't working for me:

https://community.splunk.com/t5/Splunk-Search/day-of-the-week-average/m-p/142904

[richgalloway]

Try this variation of your query.

base search
| bin _time as date span=1d
| eval weekday=strftime(_time, "%A")
| stats count, dc(date_mday) as days by weekday
| eval avg=count/days
| table weekday avg

[SplunkLunk]

Thanks for the response.  But that search doesn't produce any results in the "avg" column.  It's blank.

[isoutamo]

Hi

can you check your base search as this is working for me

index=_internal earliest=-1mon
| bin _time as date span=1d
| eval weekday=strftime(_time, "%A")
| stats count, dc(date_mday) as days by weekday
| eval avg=count/days
| table weekday avg