Get hour count average over days

[alextanght]

I got data of each transaction with a customer_id in it

If I want to know the daily average of count per hour, what search command should I use?
e.g. day 1, 23-24hr is 1000 count, day 2 23-24 hr is 1200 count, then the average of these 2 day on 23-24 hr should be 1100 count

I tried sourcetype=”purchase” | stats count(customer_id) AS hit BY date_hour | stats avg(hit) By date_hour

However the result I get is a sum of count per hour over several days instead of an average.
e.g. getting 23-24 hr is 2200 count instead of 1100 count as I want.

[siregensburg]

Something I would like to add to the answer of alextanght is that the command "date_...." is not consistent in most cases. It really depends on how splunk gets the timestamp from the data base you are using. It is best practice to use the "strftime" command to get the timestamp. So the query becomes as following

sourcetype=”purchase” | eval time_hour = strftime(_time, "%H") | eval time_day = strftime(_time, "%D") | stats count(customer_id) AS hit BY time_hour, time_day | chart avg(hit) By time_hour

cheers,just a comment: date_hour and it's variations do not work well. It's more consistent to define for instance the hours of the day using: eval time_hour = strftime(_time, "%H"). You can do something similar for years,days,month,..... So the query will become as follows

sourcetype=”purchase” | eval time_hour = strftime(_time, "%H") | eval time_day = strftime(_time, "%D") | stats count(customer_id) AS hit BY time_hour, time_day | chart avg(hit) By time_hour

[timpopxpop]

just like ur concept but change the parameter into date_wday, my search as follow:

index= | timechart count(date_wday) as count span=1w|join _time [search index=dynadvisor | timechart dc(date_wday) as day_num span=1w]|eval avg=count/day_num

hop it helpful

[alextanght · Accepted solution]

I finally find the answer by try and error. Here is the corret search command

sourcetype=”purchase” | stats count(customer_id) AS hit BY date_hour, date_mday | chart avg(hit) By date_hour

On first stats, I also need to group by days of the month in order to supply data to the chart command.

Is there any other way to improve it?