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

[briancronrath]

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?

[briancronrath]

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?

[bowesmana]

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-SP...

concerningReplicatedFileSize

excludeReplicatedLookupSize

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

[gcusello]

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

[yuanliu]

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.