How to Perform a Lookup in Splunk When Some Fields in the Lookup Table Are Empty ?

[tomapatan]

I’m working on a Splunk search that needs to perform a lookup against a CSV file. The challenge is that some of the fields in the lookup table contain empty values, meaning an exact match doesn’t work.

Here’s a simplified version of my search:

index="main" eventType="departure"
| table _time commonField fieldA fieldB fieldC fieldD fieldE
| lookup reference_data.csv commonField fieldA fieldB fieldC fieldD fieldE OUTPUTNEW offset

The lookup file reference_data.csv contains the fields: commonField , fieldA, fieldB, fieldC, fieldD, fieldE, lookupValue.
Sometimes, fieldB, fieldC, or other fields in the lookup table are empty. fieldA always has a value, sometimes the same, but the value of the offset field changes based on the values of the other fields.
If a lookup row has an empty value for fieldB, I still want it to match based on the available fields.

What I've Tried:
Using lookup normally, but it requires all fields to match exactly, which fails when lookup fields are empty.
Creating multiple lookup commands for different field combinations, but this isn’t scalable.

Desired Outcome:
If commonField  matches, but fieldB is empty in the lookup file, I still want the lookup to return lookupValue.
The lookup should prioritize rows with the most matching fields but still work even if some fields are missing.

Is there a way to perform a lookup in Splunk that allows matches even when some lookup fields are empty?

[livehybrid]

Hi @tomapatan 

I've been having a play around with this and think I might have a solution for you...lets see...

Ive created a lookup containing region, locality and postcode (see below). The region has a high level postcode but the postcode becomes more details when a locality is specified. But what if locality is blank in the search events?? In this case it will return the postcode where the locality is "*"

It is important the the fields which are empty have an asterisks (*) in them as we will configure them as a wildcard (see below):

When configuring the lookup definition, set max matches to 1 so it only returns the first matching result (this will be the most specific result, else it will return the one with an "empty" (wildcard/asterisks) value.
Set the Match type to WILDCARD(yourOptionalField1)

You can set multiple wildcard fields here.

It is important that the default lookup response for your commonField is lower than the more specific options.

Then when we search it will look like this, note that when we specify a locality we get the more detailed postcode, when we omit the locality we only get the high level postcode.

Does this help at all??

If this doesnt help, then worth checking this post to see if this helps at all? https://community.splunk.com/t5/Splunk-Search/Any-way-to-filter-multiple-wildcard-lookup-matches-to-...

Please let me know how you get on and consider adding karma to this or any other answer if it has helped.
Regards

Will

[tomapatan]

And to confirm - I`ve updated the lookup csv file so that empty values now contain a "*" value, but still not working.

[tomapatan]

Hi Will,

Great idea, although I`m not having much success I`m afraid and the output field contains empty values.

Lookup definition screenshot attached (the fieldnames are correct) - can you spot any issues ?