Filter search results based on return value of subsearch

[burns498]

Hello Splunk Community,

I am attempting to restrict search results based on the return value of a subsearch. My end goal is to construct a dashboard summary of our fail2ban intrusion prevention framework. In this particular panel I am trying to figure out which hosts have a bad fail2ban config and are NOT banning properly. My idea was to extract a field of hosts that did have a fail2ban Ban action via a subsearch, and then run a search on sshd failed login attempts with the hosts successfully banned removed from the end result. This way I can see which hosts exceeded the failed login attempts in a certain timeframe but did NOT successfully ban. I am able to extract the hosts that banned properly with this:

process=fail2ban.actions ban | dedup host | eval fail2ban_hosts=host | fields fail2ban_hosts

The result is one custom field named fail2ban_hosts that is a copy of the "host" field with duplicates removed. I was hoping I could pass these values to an outer search of sshd login attempts like so:

(process=sshd "failed password") | eval fail2ban_hosts=[search process=fail2ban.actions ban | dedup host | eval fail2ban_hosts=host | fields fail2ban_hosts]

This particular query results in an eval error but it's the sort of approach I am leaning toward. In my attempts I did get the following query to change the host field but then it only shows sshd logins for the hosts that ARE banning properly:

(process=sshd "failed password") [search process=fail2ban.actions ban | dedup host | eval fail2ban_hosts=host | fields host]

Perhaps I am using subsearch incorrectly or my approach in general may need some work. Any help would be greatly appreciated.

[Ayn · Accepted solution]

If your last search there works as you want except you completely want to negate the results, just throw a NOT in there:

(process=sshd "failed password") NOT [search process=fail2ban.actions ban | dedup host | fields host]

[burns498]

Wow. It's embarrassing how much time I spent on that for such a simple solution. Thanks again Ayn!