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

[jjohns86]

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!

[sundareshr]

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]

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

[jjohns86]

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. 😞