Splunk Search

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

Path Finder

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.

1 Solution

Esteemed Legend

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

View solution in original post

Esteemed Legend

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

View solution in original post

Path Finder

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.

0 Karma

Path Finder

@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.

0 Karma

Esteemed Legend

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

0 Karma

Path Finder

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.

0 Karma

Esteemed Legend

Add this:

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

SplunkTrust
SplunkTrust

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.

SplunkTrust
SplunkTrust

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
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!