Lookup field values not in search(index)

[vickram]

I need to display the values which are present in mylookup and NOT in my index

Search I tried :

| inputlookup mylookup.csv  | search NOT [search index=myindex | dedup ToolName | table ToolName | format]

The search is working as expected, but only one value is always coming in result but presents in search(index).
Anyone experienced this issue? or please give me any other logic for the scenario.

Thanks in advance,
vickram

[niketn · Accepted solution]

@vickram the approach seems correct. So which is the value that is present in index? Does it have any white-space or special character because of which it does not match the value in lookup? Also perform same check in lookup.

Following are some of other approaches but the output might be the same as to what you are seeing in your current search.

Similar to your approach but uses stats with eval for formatting:

| inputlookup mylookup.csv 
| search 
    [ search index=myindex 
    | stats values(ToolName) as ToolName 
    | eval ToolName=" NOT ToolName IN (\"".mvjoin(ToolName,"\",\"")."\") "]

Uses stats with eval for formatting but uses != instead of NOT:

| inputlookup mylookup.csv 
| search 
    [ search index=myindex 
    | stats values(ToolName) as ToolName 
    | eval ToolName=" ToolName!=\"".mvjoin(ToolName,"\" ToolName!=\"")."\" "]

Refer to Splunk Documentation for difference between NOT and !=: https://docs.splunk.com/Documentation/Splunk/latest/Search/NOTexpressions