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.
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
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
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.
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.
@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.
yes, just add earliest=-3h@h latest=-1h@h
.
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.
Add this:
| eval "avg(hits)" = round('avg(hits)', 2)
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.
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