avg of number of events by day

[pinzer]

Hi all, i need to search the average number from the count by day of an event.

for example if i have 3 5 and 4 events in three different days i need the average that is 4.

the eventtype is

eventtype="searchAccountLocked"

i need also to use rangemap in my search...to control if the number of events of today is higher than the average. I'm using this search queri that doesn't work

eventtype="searchAccountLocked" | stats count as count2 | timechart span=1d count | stats avg(count) |rangemap field=count2 green=1-count2 red=count2-50000 default=gray

please help me! 🙂

[ziegfried]

You won't be able to use the rangemap command for this purpose. But you can use the eval command to do so:

eventtype="searchAccountLocked" | timechart span=1d count | stats last(count) as today_count avg(count) as avg_count | eval range=if(today_count>avg_count, "red", "green")

Documentation on the eval command: http://www.splunk.com/base/Documentation/latest/SearchReference/Eval

[pinzer]

Perfect! thanks a lot! Really good work!

[ziegfried · Accepted solution]

eventtype="searchAccountLocked" | timechart span=1d count | stats avg(count)

or

eventtype="searchAccountLocked" | bucket span=1d _time | stats count by _time | stats avg(count)

[ziegfried]

I've added a separate answer for the solution.

[pinzer]

i need also to use rangemap in my search...to control if the number of events of today is higher than the average. I'm using this search queri that doesn't work

eventtype="searchAccountLocked" | stats count as count2 | timechart span=1d count | stats avg(count) |rangemap field=count2 green=1-count2 red=count2-50000 default=gray

please help me! 🙂

[pinzer]

thanks a lot! Good work!