How to alert when difference in count of two query is greater than some value

[icenitesh]

I have a below query where i search two text field and see how many time each occurred and find the difference. 

("SSO Initiated" OR "SSO Completed") | stats count(eval(searchmatch("SSO Initiated"))) as SSO_Initiated count(eval(searchmatch("SSO Completed"))) as SSO_Completed | eval Difference=SSO_Initiated-SSO_Completed

I want to create alert if Difference > 20, then mail needs to be sent.  This check should keep happening every 15 minute and check in last 15 minute if Difference > 20, then trigger mail.

[richgalloway · Accepted solution]

You already have most of what you need.  Adjust it a little as shown:

index=always_specify_an_index ("SSO Initiated" OR "SSO Completed") earliest=-15m
| stats count(eval(searchmatch("SSO Initiated"))) as SSO_Initiated count(eval(searchmatch("SSO Completed"))) as SSO_Completed 
| eval Difference=SSO_Initiated-SSO_Completed
| where Difference > 20

Save this search as an alert and have the alert trigger when the number of results is not zero.  Set the alert action to Send Email.

[icenitesh]

and how do we check if the Difference is 60% of the SSO Initiated?

[richgalloway]

... | eval Threshold = SSO_Initiated * 100/60
| where Difference > Threshold

[icenitesh]

Thanks a lot for the reply. One final help.. Can we place 2 where clause in the query ?

Like if difference is > 60% and SSO_Initiated > 100 .

Because even if the failure is 1, it becomes 100% failure. so want to have minimum count as 100 before difference is calculated. 

[richgalloway]

Yes, you can do that.  RTM.

... | eval Threshold = SSO_Initiated * 100/60
| where (Difference > Threshold AND SSO_Initiated > 100)