Splunk Search

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

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

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

Explorer

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

0 Karma

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
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!