using lookup without common field

smanojkumar — Tue, 09 Jan 2024 11:46:27 GMT

Hi Splunkers,

I'm having a lookup country_categorization, which have the keyword and its equivalent country, we need to use this info for the main search asset when the country field from index is "not available" or "Unknown", we need to use this keyword from lookup, need to compare with asset name with index, usually keyqords are set of prefix of asset name with multiple entries and it should match with equivalent country.

Index-

Asset, country
braiskdidi001, Britain

breliudusfidf002, Unknown

bruliwhdcjn001, not available

lookup

keyword, country

bru - Britain
bre - Britain

the output should be

braiskdidi001, Britain

breliudusfidf002, Britain

bruliwhdcjn001, Britain.

Thanks in Advance!

Manoj Kumar S

Re: using lookup without common field

ITWhisperer — Tue, 09 Jan 2024 12:12:05 GMT

Try something like this

| eval keyword=substr(Asset,0,3) | lookup country_categorization keyword

Re: using lookup without common field

dtburrows3 — Tue, 09 Jan 2024 14:35:49 GMT

This sounds like a good use case to utilize the WILDCARD(keyword) capability within advanced settings in lookup definitions.

I tried it out on a local instance and think I got what you are looking for.

Wildcards will need to be included in the lookup though so would look like this.

And if you are only looking for matches against the beginning of the "Asset" field value then you can also just set up the wildcards on the end of the values in lookup (This example also has a net-new field in lookup to retain the original keyword value in the lookup in case it is needed elsewhere)

and under the advanced settings checkbox in the lookup definition you would configure the field "keyword" to match with wildcards like this (you can turn off case-sensitivity too.

Note: If you decide to go with the wildcard match using a new "keyword_wildcard" field from lookup you will have to adjust the lookup definition advanced settings to WILDCARD(keyword_wildcard) instead.

Example SPL:

Full SPL to simulate:

| makeresults | eval Asset="braiskdidi001", country="Britain" | append [ | makeresults | eval Asset="breliudusfidf002", country="Unknown" ] | append [ | makeresults | eval Asset="bruliwhdcjn001", country="not available" ] | rename country as country_from_index ``` lookup wildcard match against Asset field value to the keyword_wildcard field in lookup and return the country if match is found ``` | lookup splunk_community_keyword_association keyword_wildcard as Asset OUTPUT country as country_from_lookup ``` evaluate new country field that uses derived country from lookup if a match is found and the country_from_index indicates that it was not found ``` | eval coalesced_country=coalesce(if(NOT match(country_from_index, "^(?i)(?:unknown|not\s+available|n\/a|na)$"), 'country_from_index', null()), 'country_from_lookup') | fields + _time, Asset, country_from_index, country_from_lookup, coalesced_country

Referenced splunk_community_keyword_association.csv

country	keyword	keyword_wildcard
Britain	bru	bru*
Britain	bre	bre*
USA	usa	usa*

topic using lookup without common field in Splunk Search

using lookup without common field

Re: using lookup without common field

Re: using lookup without common field