Lookup not working if used twice on same table

[gpugliese]

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.

[tscroggins]

@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]

@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

[gpugliese]

@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