Splunk Search

How to search the average number of transactions by hour by day?

Contributor

Am doing the following trying to get the average number of transactions by hour by day:

| bucket _time span=1h 
| stats avg(count) AS "Average Transaction Count" by date_hour, date_wday

By leaving out "avg", I can get the total count of transactions by hour by day, but I would just like to get the average of them.

1 Solution

SplunkTrust
SplunkTrust

OK I understand.

you don't want to use bucket btw. If for some reason there are hours with zero events, bucket will completely ignore those hours and so those zeros affect your average at all (and you need them to). Instead you want timechart. Timechart will make a full set of buckets for all the hours even if some hours had no events in them.

And using datehour and datewday would be tricky. Fortunately we don't need them. We can easily make our own with the strftime function in eval, just using the time values in the timechart output.

So run this search over the last 30 days or something.

| timechart span=1h count 
| eval weekday=strftime(_time,"%A") 
| eval hour=strftime(_time,"%H") 
| stats avg(count) by hour weekday

timechart will get the hourly counts for every hour in the timeperiod. Then eval will make two little fields on each row, "weekday" and "hour", with the appropriate values. Then finally stats just calculates the average count for each combination of hour and day.

View solution in original post

SplunkTrust
SplunkTrust

OK I understand.

you don't want to use bucket btw. If for some reason there are hours with zero events, bucket will completely ignore those hours and so those zeros affect your average at all (and you need them to). Instead you want timechart. Timechart will make a full set of buckets for all the hours even if some hours had no events in them.

And using datehour and datewday would be tricky. Fortunately we don't need them. We can easily make our own with the strftime function in eval, just using the time values in the timechart output.

So run this search over the last 30 days or something.

| timechart span=1h count 
| eval weekday=strftime(_time,"%A") 
| eval hour=strftime(_time,"%H") 
| stats avg(count) by hour weekday

timechart will get the hourly counts for every hour in the timeperiod. Then eval will make two little fields on each row, "weekday" and "hour", with the appropriate values. Then finally stats just calculates the average count for each combination of hour and day.

View solution in original post

Contributor

Ok, did the following:

| timechart span=1h count
| eval weekday=strftime(time,"%A")
| eval hour=strftime(
time,"%H")
| chart avg(count) by weekday, hour

I prefer the output that chart provided, and reversed the hour and weekday. Thanks for your help!

0 Karma

Splunk Employee
Splunk Employee

Have you seen the timechart command's function per_hour() ?

... | timechart per_hour(field) span=1d
0 Karma

Contributor

For the time period selected, I would like the average number of transactions per hour per day of the week. So I would be able to see what the average number of transactions was for Wednesdays at 2:00 PM, or Fridays at 5:00 PM, etc.

0 Karma

Motivator

hi
try this

 | bucket _time span=1h 
 | stats count  AS "Transaction Count" by date_hour, date_wday
 | stats avg(Transaction Count) AS "Average Transaction Count" 
0 Karma

Contributor

This just gives me one number, not a number by hour by weekday.

0 Karma

SplunkTrust
SplunkTrust

Can you clarify what you mean by the "average of them"? It doesn't really make any sense so I think you're missing some context. DO you want to end up wiht a single number representing average count per hour? Or one row per day, listing the hourly averages for each day?