Splunk Search

Lookup field values not in search(index)

vickram
New Member

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

0 Karma
1 Solution

niketn
Legend

@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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

0 Karma

niketn
Legend

@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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...