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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...