All Apps and Add-ons

Count based on a rolling average

akpadhi
Explorer

I have following query which provides me details of a db userid whenever the count crosses X value, however I want to modify this to a dynamic search based on a rolling average of that value for last 10 days. Can you pls help?

 

index=abc sourcetype=DBConnectionUsage  | spath cdb | spath pdb | spath application_user | search cdb=* pdb=* application_user = "*" cluster="E3"| bin span=30m _time| stats sum(connection_count) as connection_count by application_user, pdb | where connection_count >100

 

 

I want to modify the where condition to where connection_count > 'avg (conn count for last 10 days)'.

Labels (4)
Tags (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

I presume you want time to be part of your stats and that the ten day average is for application_user and pdb.

index=abc sourcetype=DBConnectionUsage  
| spath cdb 
| spath pdb 
| spath application_user 
| search cdb=* pdb=* application_user = "*" cluster="E3"
| bin span=30m _time
| stats sum(connection_count) as connection_count by _time, application_user, pdb 
| streamstats time_window=10d avg(connection_count) as tendayavg by application_user, pdb
| where connection_count > tendayavg
0 Karma

akpadhi
Explorer

@ITWhisperer i am getting an error Error in 'streamstats' command: time_window can only be used on input that is sorted in time order (both ascending and descending order are ok).

tried few options nothing worked, can you pls cross check..

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| stats ... by _time ...

should be sorting by _time for you

0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to November Tech Talks, Office Hours, and Webinars!

🍂 Fall into November with a fresh lineup of Community Office Hours, Tech Talks, and Webinars we’ve ...

Transform your security operations with Splunk Enterprise Security

Hi Splunk Community, Splunk Platform has set a great foundation for your security operations. With the ...

Splunk Admins and App Developers | Earn a $35 gift card!

Splunk, in collaboration with ESG (Enterprise Strategy Group) by TechTarget, is excited to announce a ...