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!

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...