Splunk Search

How to find the average count of a field per hour per day?

Contributor

Trying to find the average PlanSize per hour per day.

source="*\\myfile.*" Action="OpenPlan" | transaction Guid startswith=("OpenPlanStart") endswith=("OpenPlanEnd") |
eval PlanSize=case(NumPlanRows>0 AND NumPlanRows<=100, "1. Small", NumPlanRows>100 AND NumPlanRows<=200, "2. Medium", NumPlanRows >200, "3. Large") |
eval weekday=strftime(_time,"%A") | eval hour=strftime(_time,"%H") | 

I would like something like
stats avg(count(PlanSize)) by weekday, hour, PlanSize or some such

Namely, by day of the week, and hour of the day, what is the average count of each variety of plan size being opened?

I can't seem to find any syntax that works.

Tags (4)
0 Karma

Revered Legend

Try something like this

 source="*\\myfile.*" Action="OpenPlan" | transaction Guid startswith=("OpenPlanStart") endswith=("OpenPlanEnd") |
 eval PlanSize=case(NumPlanRows>0 AND NumPlanRows<=100, "1. Small", NumPlanRows>100 AND NumPlanRows<=200, "2. Medium", NumPlanRows >200, "3. Large") | bucket span=1h _time | stats count by _time PlanSize | chart avg(count) over _time by PlanSize |
 eval weekday=strftime(_time,"%A") | eval hour=strftime(_time,"%H") | fields - _time | table weekday hour *
0 Karma

Contributor

That still didn't provide an average. So, I just ran the report three times, once for Small, Medium and Large. They were exported and imported into a spreadsheet to provide additional functionality.

I used the following for each size:

timechart span=1h count | eval weekday=strftime(_time,"%A") | eval hour=strftime(_time,"%H") | eval date_wday=strftime(_time,"%w-%A") |
chart eval(round(avg(count),2)) by date_wday, hour limit=12 | eval date_wday=replace(date_wday,"(\d+-)(\w+)","\2")

0 Karma

Splunk Employee
Splunk Employee

You are close, here is one way I'd approach it:

source="*\\myfile.*" Action="OpenPlan"
| transaction Guid startswith=("OpenPlanStart") endswith=("OpenPlanEnd")
|  eval PlanSize=case(
NumPlanRows>0 AND NumPlanRows<=100, "1. Small", 
NumPlanRows>100 AND NumPlanRows<=200, "2. Medium", 
NumPlanRows >200, "3. Large")
| stats count by PlanSize, _time
| timechart span=1d per_hour(count) by PlanSize

The only difference being the last two lines really:

| stats count by PlanSize, _time
| timechart span=1d per_hour(count) by PlanSize

Does that work for you?

0 Karma

Contributor

eval weekday=strftime(_time,"%A") | eval hour=strftime(_time,"%H") |
stats count by weekday, hour, PlanSize

This will give me an absolute count of number of plans by weekday, hour and PlanSize, but how do I get the average when the time span is over several months?

0 Karma

Contributor

Thanks for the attempt, but it doesn't quite provide what I was trying to accomplish.

I wanted the average per hour per day of the week of each plan type.

                           Small    Medium    Large

Mon 0600 25 45 36
Mon 0700 5 10 15
Mon 0800 27 36 5
.
.
.

Tue 0600 5 10 15
Tue 0700 20 25 30
Tue 0800 35 40 45
.
.
.

etc

Or any other convenient-to-read layout.

0 Karma

Contributor

Namely, I am looking to sum size varieties by hour by day of the week. It is hard to believe that it is this difficult.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!