topic Re: How to calculate a running average of events by a user? in Splunk Search

How to calculate a running average of events by a user?

Log_wrangler — Fri, 29 Jun 2018 13:38:40 GMT

Hi
I am trying to write a query where I can monitor transactions/hr/user. I would like an output where I have the hourly count and historic hourly average.

I started with this, for past 24 hours, to look for users above a 10000 events per hour ...

index=some_db sourcetype=syslog_tranactions |bin _time span=1h | stats count by created_by | WHERE count > 10000

But now I was asked to get a baseline of average transactions per hour per user as a running average as a separate output column

Something like this:

User, Hourly Count, Historic Ave

Jon, 125, 140

I am hoping someone could point me in the right direction as I peruse the documentation.

Thank you

Re: How to calculate a running average of events by a user?

FrankVl — Fri, 29 Jun 2018 13:48:21 GMT

Try the following: add _time to the by clause of your initial stats, to generate hourly counts for each user. Then use eventstats to calculate historic average by user.

index=some_db sourcetype=syslog_tranactions
|bin _time span=1h
| stats count as hourly_count by created_by,_time
| eventstats avg(hourly_count) as historic_avg by created_by

If you need that historic_avg to be a running avg (ie. only based on the previous hours, rather than calculating 1 avg. over all your search results), you could use a similar query but using streamstats instead of eventstats:

index=some_db sourcetype=syslog_tranactions
|bin _time span=1h
| stats count as hourly_count by created_by,_time
| sort _time
| streamstats avg(hourly_count) as historic_avg by created_by

Re: How to calculate a running average of events by a user?

Log_wrangler — Fri, 29 Jun 2018 14:06:02 GMT

thank you for the answer.

If I want to put a threshold like, "| WHERE count > 1000"

is this the best way?

index=some_db sourcetype=syslog_tranactions
 |bin _time span=1h
 | stats count as hourly_count by created_by,_time
|WHERE hourly_count>1000
 | sort _time
 | streamstats avg(hourly_count) as historic_avg by created_by

Re: How to calculate a running average of events by a user?

FrankVl — Fri, 29 Jun 2018 14:14:11 GMT

That depends a bit on what exactly you want to achieve with that threshold. You may want to put it all the way at the bottom, such that the historic avg is still calculated accurately. In your suggestion, hours with small counts will be ignored for the historic avg.

Also: do you want to filter out low hourly counts. Or do you want to completely ignore users with a low total count? For that latter case, you would need to calculate that total first.

Re: How to calculate a running average of events by a user?

Log_wrangler — Fri, 29 Jun 2018 14:22:44 GMT

Thank you, that makes sense. I will run a few tests and as another question if I get stuck. Thank you.