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.
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
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
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
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
What's wrong about this answer?
When I run the | timechart span=1h avg(count) query, no stats are being returned and I can't figure out why
You would need to add some base search something like this (runanywhere query)
index=_internal sourcetype=splunkd | timechart span=1h avg(count)
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
What is it averaging? Count. Why? Why not take count without averaging it?