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
Motivator

@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
Get Updates on the Splunk Community!

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...

What's New in Splunk Cloud Platform 9.0.2208?!

Howdy!  We are happy to share the newest updates in Splunk Cloud Platform 9.0.2208! Analysts can benefit ...

Admin Console: A Single, Unified Interface for All Your Cloud Admin Needs

WATCH NOWJoin us to learn how the admin console can save you time and give you more control over the Splunk® ...