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

[RVDowning]

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.

[somesoni2]

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 *

[RVDowning]

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")

[aljohnson_splun]

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?

[RVDowning]

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?

[RVDowning]

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.

[RVDowning]

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.