How do I construct a search for the average per day of the week, with my day of the week starting on Sunday?

[christopheryu]

I am trying to chart the average per day of the week (mon, tue, wed, etc) but unable to do it with the days arranged in sequence i.e., Sun, Mon, Tue, etc.

I have the following search with the days of the week in order but it shows the total per day of week:

search | eval date_wday=strftime(_time,"%w-%A") | chart count by date_wday

I found this search for average per day of the week but can't make it to work with the above for the days to be in sequence:

search | bin span=1d _time | stats count dc(_time) as days by date_wday | eval average_count = count / days

Any help would be highly appreciated!

[somesoni2]

This should do it

search | eval date_wday=strftime(_time,"%w-%A") | chart count by date_wday | eventstats count as days | eval average_count = count / days

[sundareshr · Accepted solution]

Try this

search | eval date_wday=strftime(_time,"%w-%A") | bin span=1h _time | stats count by date_wday _time | stats avg(count) as avg_by_day by date_wday

[christopheryu]

Perfect, this works, thank you very much sir! Just needed to change bin span from 1h to 1d. I'm pretty sure a lot of splunk users will be able to use your answer as it can be applied to basically any search with the same requirement as mine.