topic Re: adding fallback ("others") to a lookup table in Splunk Search

adding fallback ("others") to a lookup table

wsw70 — Fri, 02 Dec 2011 15:15:59 GMT

Hello,

Following up on the excellent answer to my question about (essentially) using a lookup table, I wonder how to deal with events not referenced in the lookup table. Namely I have a lookup table

product,vendor
MS,Microsoft
Microsoft,Microsoft
Adobe,Adobe
Flash,Adobe

It works fine, but the only events available after a lookup are the ones which match the lookup table. This means that the other ones are discarded -- I would like to keep them, though, and assign them to a vendor "other" (a fallback category, so to speak). Essentially I would be looking for a lookup table like

product,vendor
MS,Microsoft
Microsoft,Microsoft
Adobe,Adobe
Flash,Adobe
*,others

I know that this is not the correct way to build the table, I just wanted to give an idea about the sought result 🙂
I use this lookup directly from the search field (as of now).

Thanks!

Re: adding fallback ("others") to a lookup table

dwaddle — Fri, 02 Dec 2011 15:48:08 GMT

One approach is to use an eval to fix it after the fact.

... | eval vendor=coalesce(vendor,"others")

Re: adding fallback ("others") to a lookup table

wsw70 — Fri, 02 Dec 2011 16:17:12 GMT

Thank you! This does the trick

Re: adding fallback ("others") to a lookup table

MickSheppard — Fri, 02 Dec 2011 16:34:51 GMT

I've been using the default matches section of the lookup for that. If something doesn't exist in my lookup table it takes the defaults.

Re: adding fallback ("others") to a lookup table

gkanapathy — Fri, 02 Dec 2011 16:45:36 GMT

Another approach (in addition to default_match as mentioned by MickSheppard, and in addition to evaluating afterwards) is tospecify in your lookup defintion that a certain field is matched using wildcards. In transforms.conf in the lookup table definition:

[mylookuptablename]
filename=mylookupfile.csv
match_type=WILDCARD(product)

This will then let your above CSV match using the "*" wildcard. (Partial wildcard matches are also possible.)

Re: adding fallback ("others") to a lookup table

wsw70 — Fri, 09 Dec 2011 12:55:46 GMT

All the solutions work fine - thanks a lot!
This one (subjectively) is the most versatile one - but thanks a lot to everyone for helping 🙂

It's a shame more than one answer cannot be marked as the right one...

Re: adding fallback ("others") to a lookup table

wsw70 — Fri, 09 Dec 2011 13:56:41 GMT

One final question: what do you mean by "Partial wildcard matches are also possible"?

The * wilcard on its own works fine.
I tried * Java * to catch both "Oracle Java something" and "Sun Java something" but the match does not work
(there is no space between the stars and the word Java, otherwise the comment system interprets this as "Java in italics")