Stats by hour

[motobeats]

I would like to create a table of count metrics based on hour of the day. So average hits at 1AM, 2AM, etc.

stats min by date_hour, avg by date_hour, max by date_hour

I can not figure out why this does not work.

Here is the matrix I am trying to return. Assume 30 days of log data so 30 samples per each date_hour

date_hour count min ...
1 (total for 1AM hour) (min for 1AM hour; count for day with lowest hits at 1AM)
2 (total for 2AM hour) (min for 2AM hour; count for day with lowest hits at 2AM)
3
4
...

Would like to do max and percentiles as well to help understand typical and atypical hits at different times of day.

[mosaicjwb]

This was my solution to an hourly count issue. I've sanitized it. But I created this for a dashboard which watches inbound firewall traffic by country ($token_value$) per hour. Both Allowed and Dropped traffic.

index=firewall sourcetype=traffic action=* location=$token_value$ earliest=-1d@d latest=@d

| eval date_hour=strftime(_time, "%H")

| stats count as "Hourly Count" by action, location, date_hour

| sort date_hour by ascending

[MTravisVolker]

For a very similar problem I had I solved it this way:

index="my_Index" host="my:host" sourcetype="my:sourcetype"
| timechart count span=60m

[motobeats · Accepted solution]

This gave me what I was looking for:

bucket _time span=1h|stats count by _time date_hour|stats min(count), p25(count), p50(count), p75(count), max(count) by date_hour

[the_wolverine]

• | timechart span=1h avg(count) ?

[Ayn]

What's wrong about this answer?

[jwalzerpitt]

When I run the | timechart span=1h avg(count) query, no stats are being returned and I can't figure out why

[somesoni2]

You would need to add some base search something like this (runanywhere query)

index=_internal sourcetype=splunkd | timechart span=1h avg(count)

[jwalzerpitt]

Thx for the reply and info. Added various sourcetypes in different queries and sometimes I see no results for the avg count, yet I see events.

For one particular query I see 373k events, yet nothing is returned in the statistics tab even though the the days are being listed for the following query: index=myindex sourcetype=myindex | timechart span=1d avg(count)

Thx

[MTravisVolker]

What is it averaging? Count. Why? Why not take count without averaging it?