Splunk Search

Lookup not working if used twice on same table

gpugliese
Explorer

Hello Community,

I need your help to understand why if I use twice a "lookup" command on the same table lookup (out-of-the-box "geo_attr_countries"), I don't get the results I expect, since it looks like the second one is ignored, but it does work if I replace both the "lookup" commands with equivalent "join" commands.

I am using Splunk Cloud 8.1 and I would prefer the double "lookup" due to better search performance on my dataset, where events may have the country code or the country name in the same field and I need both of them.

Below 2 examples you can run in your Splunk (in bold the only lines changed):

  • Double "lookup" not working:
    • | makeresults | eval countryCodeOrName="Spain"
      | append [| makeresults | eval countryCodeOrName="IT"]
      | append [| makeresults | eval countryCodeOrName="France"]
      | append [| makeresults | eval countryCodeOrName="DE"]
      | table countryCodeOrName
      | eval country=if(len(countryCodeOrName)>2,countryCodeOrName,"-")
      | eval iso2=if((len(countryCodeOrName) == 2),countryCodeOrName,"-")
      | lookup geo_attr_countries country OUTPUT iso2 as countryCode
      | lookup geo_attr_countries iso2 OUTPUT country as countryName
      | table countryCodeOrName countryCode countryName
  • Double "join" working:
    • | makeresults | eval countryCodeOrName="Spain"
      | append [| makeresults | eval countryCodeOrName="IT"]
      | append [| makeresults | eval countryCodeOrName="France"]
      | append [| makeresults | eval countryCodeOrName="DE"]
      | table countryCodeOrName
      | eval country=if(len(countryCodeOrName)>2,countryCodeOrName,"-")
      | eval iso2=if((len(countryCodeOrName) == 2),countryCodeOrName,"-")
      | join type=left country [| inputlookup geo_attr_countries | table country iso2 | rename iso2 as countryCode ]
      | join type=left iso2 [| inputlookup geo_attr_countries | table country iso2 | rename country as countryName ]
      | table countryCodeOrName countryCode countryName

I would appreciate your suggestions!

Thanks,

G.P.

Labels (3)
Tags (3)

tscroggins
Builder

@gpugliese 

This is related to the caching mechanism used by the CSV data provider. You can force subsequent lookup commands to reload lookup data with update=t:

| makeresults | eval countryCodeOrName="Spain"
| append [| makeresults | eval countryCodeOrName="IT"]
| append [| makeresults | eval countryCodeOrName="France"]
| append [| makeresults | eval countryCodeOrName="DE"]
| table countryCodeOrName
| eval country=if(len(countryCodeOrName)>2,countryCodeOrName,"-")
| eval iso2=if((len(countryCodeOrName) == 2),countryCodeOrName,"-")
| lookup geo_attr_countries country OUTPUT iso2 as countryCode
| lookup update=t geo_attr_countries iso2 OUTPUT country as countryName
| table countryCodeOrName countryCode countryName

gpugliese
Explorer

@tscroggins

thanks a lot, it works, but I am not sure about the reason: the description of "update" is

"If the lookup table is modified on disk while the search is running, real-time searches do not automatically reflect the update. To do this, specify update=true. This does not apply to searches that are not real-time searches. This implies that local=true."

but I don't modify the lookup table (or do I unknowingly with the above search?) and, since that's an out-of-the-box lookup I didn't modified and I use Splunk Cloud, "local=true" should be irrelevant.

Do I miss something about how it works?

Thanks

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!