using lookup without common field

[smanojkumar]

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

[dtburrows3]

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:

<base_search>
    | lookup splunk_community_keyword_association keyword as Asset OUTPUT country as match_country
    | eval
        country=coalesce(if(NOT match(country, "^(?i)(?:unknown|not\s+available|n\/a|na)$"), 'country', null()), 'match_country')

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*

[ITWhisperer]

Try something like this

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