Does Splunk have a NVL-like function or feature?

[suhprano]

Is it possible to return a 0 or -1 for null subsearch matches in splunk?

sourcetype=log1 [search sourcetype=log2|fields id|dedup id]|fields id,status |sort id

This gives me all id and status entries from log1 that match all the ids in log2, but if there are no log2 ids matching in log1, is it possible to report/create a dummy entry with a default value in the result set?

[jamesdaily]

COALESCE is the ANSI standard SQL function equivalent to Oracle NVL. Unlike NVL, COALESCE supports more than two fields in the list.

In Splunk, coalesce() returns the value of the first non-null field in the list.

Here's an example where you'd get the Preferred_Name if it's present, otherwise use the First_name if it's present, and if both of those are null, then just use the word "Friend". If you didn't include a default at the end, and both fields were blank, you'd just get a blank.

eval name_to_use=coalesce(preferred_name,first_name,"Friend")| table name_to_use

[Ayn · Accepted solution]

There is indeed a NVL equivalent in the Splunk search language. It is called fillnull. More information on that command is available here: http://www.splunk.com/base/Documentation/latest/SearchReference/Fillnull