Schedule an alert if ExceedHigh and ExceedLow are breached 3 times in a row?

[iamsplunker]

Hello Community,

I wanted to schedule an alert If  ExceedHigh OR ExceedLow columns breaches 3 times in a row

I have few columns say Highest , Lowest, ExceedHigh, ExceedLow and the values are

Highest,  Lowest , ExceedHigh,  ExceedLow

3520        2882         NO                    NO

3502        2860       YES                     NO

3590       2941        YES                    YES

3705       2890       YES                     YES

3474     3028         NO                  YES

If ExceedHigh OR  ExceedLow values breaches (the values are YES, YES, YES in a row ONLY) then alert should be triggered . with last 15 min time range and Scheduled Frequency is 24 hours . Please help

[Nisha18789]

hi @iamsplunker, I have a query- how often this alert will run which will check 3 consecutive YES? I mean is it possible that when it runs it might have 100 rows and if  it sees any 3 consecutive YES , alert will fire?

[iamsplunker]

Hello @Nisha18789 : Thanks for your response. this alert will run for every 24 hours. Yes, it is possible it might have more than 100 rows and when there are 3 "YES" in a row the alert should fire. Thanks

[Nisha18789]

Hi @iamsplunker , you can use streamstats for that. Plese refer this great post by @niketn for complete details.

https://community.splunk.com/t5/Splunk-Search/how-to-sum-consecutive-success-of-sequential-order-of-...

[iamsplunker]

@Nisha18789 : That answer is little different. Would you mind to develop a query/condition for me. Thanks

[niketn · Accepted solution]

@iamsplunker It is better if you read, understand and try out streamstats command yourself and reach out to the community if your query is not working as expected. Community experts will be happy to assist you when you get stuck. However, you must also understand that all the contributors to the community are volunteering their time outside of their respective day jobs. While actual data differs for each implementation, the use case pointed out in the post by @Nisha18789 is exactly the same as what you need. The reset_on_change argument for streamstats resets the counting each time status changes. You need to count the same and alert only if 3 or more instances of consecutive YES is found for events. 

You can try the following query which should return the events where either ExceedHigh or ExceedLow is more Yes for more than three times.

<yourCurrentSearchWhichGivesFollowingFields>
| fields _time Highest Lowest ExceedHigh ExceedLow 
| streamstats count as ExceedHighCounter reset_on_change=true by ExceedHigh
| streamstats count as ExceedLowCounter reset_on_change=true by ExceedLow

 Then you can have the following Alert Trigger Condition

| search (ExceedHighCounter=3 AND ExceedHigh="Yes") OR (ExceedLowCounter=3 AND ExceedLow="Yes")

 PS: You can change the trigger condition as per actual need like whether you need the event for maximum exceeds or whether you need all the events. You can also segregate the alerts for ExceedHigh or ExceedLow depending on your use case.

Following is a run anywhere search example based on your sample data:

| makeresults 
| eval _raw="Highest  Lowest  ExceedHigh  ExceedLow
3520        2882         NO                    NO
3502        2860       YES                     NO
3590       2941        YES                    YES
3705       2890       YES                     YES
3474     3028         NO                  YES" 
| multikv forceheader=1 
| eval delta=300 
| accum delta 
| eval _time=_time-delta 
| table _time Highest Lowest ExceedHigh ExceedLow 
| foreach * 
    [| eval <<FIELD>>=trim(<<FIELD>>)] 
| reverse
| streamstats count as ExceedHighCounter reset_on_change=true by ExceedHigh
| streamstats count as ExceedLowCounter reset_on_change=true by ExceedLow
| eval ExceedHighCounter=case(ExceedHigh="YES",ExceedHighCounter),  ExceedLowCounter=case(ExceedLow="YES",ExceedLowCounter)
| search (ExceedHighCounter=3 AND ExceedHigh="Yes") OR (ExceedLowCounter=3 AND ExceedLow="Yes")

[iamsplunker]

@niketn : Thanks for your response. Yes, I did try with the streamstats initially but it did not worked as expected. May be I missed some logic behind it . Your Answer works just fine. Thank you!