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

smahoney
Path Finder

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.

0 Karma

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
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...