Splunk Search

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

Log_wrangler
Builder

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

Tags (3)
0 Karma
1 Solution

FrankVl
Ultra Champion

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

View solution in original post

0 Karma

FrankVl
Ultra Champion

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
0 Karma

Log_wrangler
Builder

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
0 Karma

FrankVl
Ultra Champion

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.

0 Karma

Log_wrangler
Builder

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...