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!
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
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
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
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.