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!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...