https://community.splunk.com/t5/Splunk-Search/Get-hour-count-average-over-days/m-p/73539#M18434 <P>I finally find the answer by try and error. Here is the corret search command</P> <P>sourcetype=”purchase” | stats count(customer_id) AS hit BY date_hour, date_mday | chart avg(hit) By date_hour</P> <P>On first stats, I also need to group by days of the month in order to supply data to the chart command.</P> <P>Is there any other way to improve it?</P> Mon, 28 Sep 2020 11:24:58 GMT alextanght 2020-09-28T11:24:58Z https://community.splunk.com/t5/Splunk-Search/Get-hour-count-average-over-days/m-p/73538#M18433 <P>I got data of each transaction with a customer_id in it</P> <P>If I want to know the daily average of count per hour, what search command should I use?<BR /> 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</P> <P>I tried sourcetype=”purchase” | stats count(customer_id) AS hit BY date_hour | stats avg(hit) By date_hour</P> <P>However the result I get is a sum of count per hour over several days instead of an average.<BR /> e.g. getting 23-24 hr is 2200 count instead of 1100 count as I want.</P> Mon, 28 Sep 2020 11:24:53 GMT https://community.splunk.com/t5/Splunk-Search/Get-hour-count-average-over-days/m-p/73538#M18433 alextanght 2020-09-28T11:24:53Z https://community.splunk.com/t5/Splunk-Search/Get-hour-count-average-over-days/m-p/73539#M18434 <P>I finally find the answer by try and error. Here is the corret search command</P> <P>sourcetype=”purchase” | stats count(customer_id) AS hit BY date_hour, date_mday | chart avg(hit) By date_hour</P> <P>On first stats, I also need to group by days of the month in order to supply data to the chart command.</P> <P>Is there any other way to improve it?</P> Mon, 28 Sep 2020 11:24:58 GMT https://community.splunk.com/t5/Splunk-Search/Get-hour-count-average-over-days/m-p/73539#M18434 alextanght 2020-09-28T11:24:58Z https://community.splunk.com/t5/Splunk-Search/Get-hour-count-average-over-days/m-p/73540#M18435 <P>just like ur concept but change the parameter into date_wday, my search as follow:</P> <P>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</P> <P>hop it helpful</P> Tue, 29 Sep 2020 09:44:36 GMT https://community.splunk.com/t5/Splunk-Search/Get-hour-count-average-over-days/m-p/73540#M18435 timpopxpop 2020-09-29T09:44:36Z https://community.splunk.com/t5/Splunk-Search/Get-hour-count-average-over-days/m-p/73541#M18436 <P>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</P> <P>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 </P> <P>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</P> <P>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</P> Tue, 29 Sep 2020 09:52:47 GMT https://community.splunk.com/t5/Splunk-Search/Get-hour-count-average-over-days/m-p/73541#M18436 siregensburg 2020-09-29T09:52:47Z