Splunk Search

Looking for way to return a value from subsearch when it returns zero results

jjohns86
Explorer

Maybe there is a much easier way to do that I'm just missing.....but here goes. I have a search that I am using to alert when there are multiple failed logons for a specific IP:

sourcetype=mysource "AUTHENTICATION_FAILED" | rex field=_raw "WHO: \[username: (?<user>.\w+)" | rex field=_raw "CLIENT IP ADDRESS: (?<SourceIP>.\d+\.\d+\.\d+\.\d+)" |eval userID=UPPER(user) | stats dc(userID) as uniqueUser by SourceIP | where uniqueUser >3

This works all well and good in an alert and notifies when the situation happened. What I want to do is return the raw events so we can grab some relevant details to include in the alert email (e.g. the offending sourceIP of the failed logons,what account(s) they used, how many times they attempted it, success/failure,etc), so tried using a subsearch to do this.

sourcetype=mysource [search sourcetype=mysource "AUTHENTICATION_FAILED" | rex field=_raw "WHO: \[username: (?<user>.\w+)" | rex field=_raw "CLIENT IP ADDRESS: (?<SourceIP>.\d+\.\d+\.\d+\.\d+)" |eval userID=UPPER(user) | stats dc(userID) as uniqueUser by SourceIP | where uniqueUser >3 | return $SourceIP ] 

This works fine if the subsearch returns a value (meaning there are >X (in this example 3) failed logons, but otherwise returns ALL events (due to the subsearch not returning any rows and therefore not returning a SourceIP that can be used for filtering down the main search results). I saw a few other examples of how to return a default when a subsearch returns zero rows, but can't seem to get it to work properly within my search, so any help or guidance is appreciated.
Thanks all!

0 Karma

sundareshr
Legend

In your first search try adding values(_raw) to your stats command.. Like this

...| stats dc(userID) as uniqueUser values(_raw) as raw by SourceIP | where uniqueUser >3

jjohns86
Explorer

Thanks much Sundrareshr, that works nicely (and much cleaner than what I was trying).

0 Karma

jjohns86
Explorer

Should have added that I've tried a few different options/tactics (such as trying to do a case or coalesce on the SourceIP field with a default ("0.0.0.0") ) with no luck. 😞

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...