Count based on a rolling average

[akpadhi]

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)'.

[ITWhisperer]

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

[akpadhi]

@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..

[ITWhisperer]

| stats ... by _time ...

should be sorting by _time for you