How to create a search to compare counts from 2 different query and trigger alert?

[harishnpandey]

Hi,

I need help with building query which compares value from 2 different search and trigger alert if count from both the query is less than 1

For e.g.

index=query1| stats count as count1|appendcols [search index=query2 | stats count as count2 ]|eval final_count=if(matchcount1,count2),"0","1") | stats count AS final_count

Current alert condition as :

If number of results is less than 0 and schedule cron runs at every 5 mins

But my current query triggers alert even if count matches from both the query and it shows final_count value as 1 .. I am expecting alert to be triggers only if count does not matches between both queries and specially in case of counts from both queries result zero after compare.

Appreciate you help with correcting to reframe my logic and build query and trigger condition

[harishnpandey]

Splunk Query:

index=query1| stats count as count1|appendcols [search index=query2 | stats count as count2 ]|eval final_count=if(match (count1,count2),"0","1") | stats count AS final_count

[gaurav_maniar]

Hi,

You can try the following approach. It will provide you the event count difference between queries.
let me know if you require more details on this.

index=_internal OR index=_audit 
 | eval internal_count=if(index="_internal", 1, null()) 
 | eval audit_count=if(index="_audit", 1, null()) 
 | stats sum(internal_count) AS internal sum(audit_count) AS audit 
 | eval diff=internal-audit

[Rob2520]

index=query1| stats count as c1|appendcols [search index=query2 | stats count as c2 ]|eval final_count=if(c1=c2,0,1)

If count matches your final_count value will be 0 and if not, final_count value will be 1. Save it as an alert and in trigger condition choose custom and type | search final_count=1. What this does is if your counts doesn't match you will get an alert. Is this what you are looking for?

[harishnpandey]

So far It looks good .. once I get alerted for condition when count does not matches . It would be surely win win .

Appreciate your help on this.

[Rob2520]

@harishnpandey if my answers helps please accept/vote so someone else can benefit from it.