Splunk Search

Get hour count average over days

alextanght
Engager

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.

Tags (3)
1 Solution

alextanght
Engager

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?

View solution in original post

siregensburg
Engager

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

0 Karma

timpopxpop
Engager

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

0 Karma

alextanght
Engager

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?

Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...