Splunk Search

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

jpringle03
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

woodcock
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

woodcock
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

jpringle03
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

ericl42
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

woodcock
Esteemed Legend

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

0 Karma

ericl42
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

woodcock
Esteemed Legend

Add this:

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

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

starcher
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
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!