Reuse search query, but conditionally

[bitnoise]

Hi,

I have a 'complex' (for me at least) question.  What I want to achieve is the following:

1) 

index=abc msg="*firewall off*" |table _time,hostname,msg

>this will give me, for example:
hostname = machine1
msg = "the firewall has been turned off"
>> I want to be triggered if someone turns off the firewall

Now, the actual issue I have now is the following:  A few seconds before this event, I might get a "system update event" that updates the firewall (agent update), which is OK, and I do NOT want this event.
I would need to combine both queries into 1 alert.  
2) 

index=abc hostname=machine1 NOT msg="*system updated*"

I want to see the result of 1, but only if it was not preceeded by 2.

I hope this makes sense.

[bitnoise]

Cool, never saw that streamstats thingy, I'll test it and let you know 🙂

[bowesmana · Accepted solution]

Technically the search msg="*firewall off*" will not match the firewall has been turned off

but assuming that's understood, then this may work for you

index=abc msg="*firewall off*" OR msg="*system updated*"
| streamstats time_window=30s dc(msg) as msgTypes count by hostname 
| where (match(msg, "firewall off") AND count=1) OR (count>1 AND msgTypes=1)
| table _time, hostname, msg

It uses streamstats to combine events within a 30 second time window - set that to your expected range.

The where clause filters only those events where it just contains firewall off OR there are multiple firewall off messages, but no system updated message.

[bitnoise]

I was fighting with the query, as it kept on giving me results, but seems I overlooked the fact that the "off" trigger happend twice and the other only once 🙂 

Great! Thanks a lot