Setting Rolling seven day average alert with current day data

[Akdeveloper]

Hi,

I am trying to setup an alert and notify by email, when count of last 3hrs is greater than rolling average of last 7 days using the below query. Query is working fine but in the alert is not working/not getting triggered I tried as below Alert Config

Trigger conditions in alert Screen are, Trigger alert when ,Custom option ,search alert==true

Query:

sourcetype="cloudwatch" index=***** earliest=-6d@d latest=@d
|bucket _time span=1d
|stats count by _time
|stats avg(count) as SevenDayAverage
|appendcols [search sourcetype="cloudwatch" index=*****
|stats count as IndividualCount]
|eval alert = if((IndividualCount.SevenDayAverage),"true","false")
SevenDayAverage IndividualCount alert
5 1139 true

[ITWhisperer]

It is not clear what your if condition is supposed to be doing (was there a typo?), nor indeed what your search is trying to find as you seem to be trying to compare an average daily count with a 3 hour count?

Also, this is potentially going to be very slow - have you considered using metasearch or summary indexes?

[Akdeveloper]

Sorry there was typo,this is correct if currently,if((IndividualCount>SevenDayAverage),"true","false").

Reg the query I am trying to compare counts in last three hours with seven day average count,if true then alert.

I didn't tried metasearches,will give a try too

[ITWhisperer]

So you are trying to alert if a 3 hour count is greater than the average for a whole day over the last 6 days?

[Akdeveloper]

That's correct @ITWhisperer 

[ITWhisperer]

Which version of Splunk are you running as there have been problems with custom alert conditions?

A way to work around this is to add a where command to your search and then alert if there are any results e.g.:

| where alert="true"