Wildcards with "| lookup"

[geomore]

Hi,

I'm trying to get wildcard lookups to work using the "lookup" function. I've followed guidance to set up the "Match Type" for the fieldin the lookup definition as per Define a CSV lookup in Splunk Web - Splunk Documentation (I don't have access to transforms.conf) and whatever I try,  adding WILDCARD(foo) makes no difference, as if the feature is not being applied. I've found several posts where people report success, but cannot replicate myself.

Lookup example:  

foo	bar
abc	1
*cba*	2

| makeresults
| eval foo="x"
| lookup mylookup foo

x="abc" matches
x="*cba*" matches
x="ab*" does not match
x="dcba" does not match

I'd rather not resort to inputlookup subsearches if possible as my applications are quite complex!

Splunk Verision: 8.2.2.1

Many Thanks in Advance

[ldongradi_splun]

Assuming your lookup definition has a match type set to WILDCARD(foo), you have to understand the wildcard in the lookup as either * for a search or % for a where command. 

Even if your lookup table uses *, we will interpret the match that way:

x="abc" matches because

• | where x="abc"
• | where "abc"="abc" matches the lookup

x="*cba*" matches because

• | where x LIKE "%abc%"
• | where "*cba*" LIKE "%cba%" matches the lookup. See how the * in the eval/where syntax are treated as characters, not wildcards

x="ab*" does not match because

• | where x="abc"
• | where "ab*"="abc" does not match ! * is NOT a wildcard in the eval syntax for the left argument
• | where x LIKE "%cba%"
• | where "ab*" LIKE "%cba%" obviously does not match

x="dcba"does not match you say, but it should match (it does on my env) because

• | where x LIKE "%cba%"
• | where "dcba" LIKE "%cba%" matches thanks to the wildcards.

So, you can't consider the * in the data as a wildcard, but as a character.

[geomore · Accepted solution]

Many thanks for clarifying the logic.

I have recreated a new lookup and csv from scratch, applied exactly the same settings, and now the functionality works as intended (with asterisks, not percents, as I expected). I guess I'll have to put that down as a glitch. 

[PickleRick]

 Are you sure you reference the lookup name i  your search, _not_ the lookup file?

[geomore]

100% sure - first thing I checked!

[PickleRick]

And it's not named the same? Asking because that's the typical cause for wildcard match not working.

If the lookup is named the same as the lookup file (quite typical situation I'd say) the name applies to the file, not the lookup.

[geomore]

The lookup is e.g "mylookup" and the csv is "mylookup.csv". I 've just tried changing the lookup name too, and no improvement. This couldn't be caused by a the setting being overridden by default in the backend?

[PickleRick]

Unless you have other lookup with the same settings defined elsewhere with higher priority overriding your settings, there should not be "general" settings overriding your lookup.

A very typical case of "should work but doesn't" is permissions problem but I don't see how mismanaged permissions would cause the lookup to be working in general but just not in the wildcard mode. Just to be on the safe side you can check the permissions for the lookup anyway.