topic Re: Why am I able to get values via joining on an input lookup command, but cannot get values when using the actual look in Splunk Search

Why am I able to get values via joining on an input lookup command, but cannot get values when using the actual lookup?

briancronrath — Tue, 15 Aug 2023 18:48:33 GMT

I have a sourcetype that is exhibiting very odd behavior. If I try to run a lookup command such as the following:

index=index_here sourcetype=sourcetype_here |lookup lookup_name JoiningID as JoiningID output Value1 Value2

It will not give me Value1 or Value2 in my results, however if I instead run:

index=index_here sourcetype=sourcetype_here |join type=left JoiningID [|inputlookup lookup_name]

I get the Value1 and Value2 here joined in no problem. What are some reasons for the actual lookup command not giving me any values?

Re: Why am I able to get values via joining on an input lookup command, but cannot get values when using the actual look

yuanliu — Wed, 16 Aug 2023 06:21:11 GMT

A likely cause is that you uploaded a CV file and named it lookup_name, but did not define a lookup named lookup_name. This is a quirk in Splunk. It doesn't give you an error when you use the filename as lookup name but don't define a lookup with that name. See Define a CSV lookup in Splunk Web if you need help setting one up.

Personally, I often name my files with .csv, and my lookups without .csv. This can reduce confusions like this.

Re: Why am I able to get values via joining on an input lookup command, but cannot get values when using the actual look

gcusello — Wed, 16 Aug 2023 09:31:13 GMT

HI @briancronrath ,

as @yuanliu said, when you create a lookup, you have also to create a lookup definition [Settings > Lookups > Lookup Definitinon].

In this way you can use the lookup using the lookup command.

Only for conclusion: the lookup command is a left join correlation, but in general, use the join command only when you haven't any other solution and when you have few data to join, otherwise, you'll have very slow searches.

Ciao.

Giuseppe

Re: Why am I able to get values via joining on an input lookup command, but cannot get values when using the actual look

briancronrath — Wed, 16 Aug 2023 18:01:08 GMT

Digging in further it looks like it might be an issue with replication. If I put local=true on the lookup call I seem to get results. Most other lookups don't have this issue, wondering if there is some odd state that could occur where a specific lookup file isn't replicated?

Re: Why am I able to get values via joining on an input lookup command, but cannot get values when using the actual look

bowesmana — Thu, 17 Aug 2023 00:34:49 GMT

Yes, that's definitely a possibility - if the knowledge bundle replication size exceeds the limit, the bundle won't get replicated and you will get exactly the behaviour you're seeing, i.e. different results depending on where the looking is being done.

Any large lookup created by someone else in any app can cause this to occur - I've had an issue where someone created a 5GB lookup (as a temp result set) and so it broke replication.

There are some settings in distsearch.conf that can affect what gets replicated and this is sometimes configured to prevent lookups from breaking replication

https://docs.splunk.com/Documentation/Splunk/9.1.0/Admin/Distsearchconf#.27classic.27_REPLICATION-SPECIFIC_SETTINGS

concerningReplicatedFileSize

excludeReplicatedLookupSize

join will run on the search heads, whereas your lookup example will run on the indexer.