SPL using search NOT comparing 2 Lookup (csv) files returns wrong results

[marceloalejandr]

Greetings,

I've 2 Lookup (csv) files, one generated from index _internal (approx 15k events) and another generated from ldap (approx 20k events) and both files have only the host name field.   

When running the following SPL the "search NOT" is returning results that should not be included.    At first we thought it was a problem with case-sensitivity but we ruled this out by changing all the host names to upper-case but still the "search NOT" was returning wrong results.   

"| inputlookup LDAP_source.csv | search NOT [ | inputlookup INTERNAL_source.csv ]"

We've tested using 1k Lookup files without any problems.  Is there perhaps a limit being reached or a known issue with comparing 2 Lookup files using "search NOT"?

Thanks in advance for any assistance or insight.  

[scelikok]

Hi @marceloalejandr,

Subsearch limit cannot be increased from UI. Please try below search with correct field name.

| inputlookup LDAP_source.csv 
| lookup INTERNAL_source.csv host OUTPUTNEW host as internal_hostname
| where isnull(internal_hostname)

[marceloalejandr]

@scelikok 

Thanks again for your response.   The correction with the syntax works like a charm. 

However now we reverted back to the original lookup files prior changing the hostnames to all upper-case and the case-sensitivity matter has returned.     The original  INTERAL_source.csv file as it was originally extracted contained some hostnames having a combination of upper and lower case characters.    We've tried defining the Lookup Definition having the "Case sensitive match" setting to unchecked.  

Other than changing the Lookup files for all the hostnames to be upper-case after the extract, is there a setting or something that can be done so that the Lookup files can be case insensitive?  

[scelikok]

Hi @marceloalejandr,

It is nice to hear that lookup works.

Regarding case sensitivity, it seems you are not using lookup definitions on the query but lookup table files. I am telling you since ".csv" extension. If this is the case your lookup definitions have no effect, so please test the same query by updating the lookup name with the definition names. I assume your lookup definitions are the same with CSV file name without ".csv".

| inputlookup LDAP_source 
| lookup INTERNAL_source host OUTPUTNEW host as internal_hostname
| where isnull(internal_hostname)

[marceloalejandr]

@scelikok 

Excellent feedback.    Referencing the Lookup Definition name and NOT the Lookup CSV file did the trick.  

Thanks again for the assist. 

[marceloalejandr]

@scelikok ,

Thank you for your response.   Just to clarify a couple of things:
- is there a way to increase the subsearch limit in a conf file or via Splunk UI?    

- in the SPL, if "hostname" the field in the 2 Lookup csv files is actually "host" and the internal_hostname is the new field then a slight modification to the SPL to "host" should have worked but it does not.  Is something else needed?

| inputlookup LDAP_source.csv 
| lookup INTERNAL_source.csv host OUTPUTNEW internal_hostname
| where isnull(internal_hostname)

Thanks again.

[scelikok]

Hi @marceloalejandr,

You may be hitting subsearch limits, can you try below;

| inputlookup LDAP_source.csv 
| lookup INTERNAL_source.csv hostname OUTPUTNEW internal_hostname
| where isnull(internal_hostname)