Average sessions per hour

[alaking]

I am trying to calculate the average number of sessions per hour based on "off hours" 5pm to 9 am. I have the time range and events, I just need to do the math.
This returns an empty result set:
Search here
| stats count(sessionid) as total2
| stats avg(total2) by date_hour

This returns a time chart but it's averaging the values of sessionid (which is a numeric field):
Search here | timechart span=24h per_hour(sessionid) as AvgPerHour

and finally, this returns the count per hour as expected but I'm at a loss as to how to average based on this count:
search here | stats count(sessionid) by date_hour

Thanks in advance for reading.

[woodcock · Accepted solution]

You need to chain 2 stats commands like this:

search here | bucket _time span=1h | stats dc(sessionid) AS sessionIds by _time | stats avg(sessionids)

Run this with a timepicker value that spans many hours.

[alaking]

This works! Thank you for the speedy reply, I'm still trying to grasp the complexity of the search language.