topic Re: Chart average event occurrence per hour of the day for the last 30 day in Splunk Search

Chart average event occurrence per hour of the day for the last 30 day

jpringle03 — Thu, 09 Feb 2017 23:11:46 GMT

I'm trying to get the chart that shows per hour of the day, the average amount of a specific event that occurs per hour per day looking up to 30 days back.

Currently I have the following query:

index=security extracted_eventtype=authentication | stats count as hit BY date_hour | chart avg(hit) as "Avg. Auth Attempts" by date_hour

Which gets me the total per hour but doesn't seem to be getting me the actual average.

Re: Chart average event occurrence per hour of the day for the last 30 day

starcher — Fri, 10 Feb 2017 01:08:29 GMT

Try something like this and see how it looks if I understood what you were asking.

index=security extracted_eventtype=authentication earliest=-30d@d | bucket span=1h _time | stats count by _time, date_hour | stats avg(count) AS hourly_average by date_hour | sort date_hour

Re: Chart average event occurrence per hour of the day for the last 30 day

jkat54 — Tue, 29 Sep 2020 12:49:32 GMT

Maybe this will work?

index=security extracted_eventtype=authentication | timechart span=1h avg(count) AS "whatever" by date_hour

date_hour doesn't consider your time zone though so use it with caution.

Re: Chart average event occurrence per hour of the day for the last 30 day

woodcock — Fri, 10 Feb 2017 05:11:27 GMT

First of all, never use the "free" date_* fileds; they are pre-TZ-normalization artifacts which means that they are almost always WRONG. Always calculate your own. Try this:

index=security extracted_eventtype=authentication
| bin _time span=1h
| eval date_hour=strftime(_time, "%H")
| stats count AS hits first(date_hour) AS date_hour BY _time
| stats avg(hits) BY date_hour

Re: Chart average event occurrence per hour of the day for the last 30 day

jpringle03 — Fri, 10 Feb 2017 18:47:57 GMT

This worked! Thanks so much.

How would I go about rounding those final numbers in each hour? I tried to add a round around the avg but Splunk does not like that at all.

Re: Chart average event occurrence per hour of the day for the last 30 day

woodcock — Fri, 10 Feb 2017 19:13:21 GMT

Add this:

| eval "avg(hits)" = round('avg(hits)', 2)

Re: Chart average event occurrence per hour of the day for the last 30 day

ericl42 — Tue, 18 Dec 2018 21:11:45 GMT

@woodcock I just stumbled across this post and I really like your solution. Would it be easy to expand upon it and search search back -3h@h to -1h@h and alert if there is a significant increase from -2 hours ago to -1 hour ago? Essentially I'm trying to come up with an easy way to alert on if my IPS events increase during a given period of time.

Re: Chart average event occurrence per hour of the day for the last 30 day

woodcock — Tue, 18 Dec 2018 22:36:57 GMT

yes, just add earliest=-3h@h latest=-1h@h.

Re: Chart average event occurrence per hour of the day for the last 30 day

ericl42 — Wed, 19 Dec 2018 14:39:59 GMT

Right, I added that and I see the avg(hits) showing up as I expect it to. The problem I'm running into is how to now look at the first row and see if there is a standard deviation or X increase in logs from line two. (i.e. Row one = 100, Row two = 150, 50% increase = trip an alert). Ideally I'm going to move this to a correlation rule.

Re: Chart average event occurrence per hour of the day for the last 30 day

smahoney — Wed, 15 Mar 2023 23:25:07 GMT

Not sure if you are still monitoring these, but I wanted to use this solution and compare the current count against the output of this query in a dashboard panel and its driving me nuts. So if the current hourly count is 5 at 6pm, compare it to the output of the 6PM average from this solution.