topic Re: Stats by hour in Splunk Search

Stats by hour

motobeats — Mon, 28 Sep 2020 14:09:49 GMT

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.

Re: Stats by hour

the_wolverine — Mon, 24 Jun 2013 22:39:03 GMT

| timechart span=1h avg(count) ?

Re: Stats by hour

Ayn — Tue, 25 Jun 2013 07:22:37 GMT

What's wrong about this answer?

Re: Stats by hour

motobeats — Tue, 25 Jun 2013 07:47:26 GMT

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

Re: Stats by hour

jwalzerpitt — Thu, 15 Jan 2015 21:16:17 GMT

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

Re: Stats by hour

somesoni2 — Thu, 15 Jan 2015 22:47:21 GMT

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

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

Re: Stats by hour

jwalzerpitt — Fri, 16 Jan 2015 13:44:20 GMT

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

Re: Stats by hour

MTravisVolker — Thu, 11 Apr 2019 21:24:57 GMT

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

Re: Stats by hour

MTravisVolker — Thu, 11 Apr 2019 21:29:20 GMT

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

Re: Stats by hour

mosaicjwb — Wed, 02 Sep 2020 16:06:15 GMT

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