Splunk Search

Append Non Matching Results to Lookup Table

IRHM73
Motivator

Hi, I wonder whether someone could help me please.

I'm trying to run a search, compare it against fields in a lookup table and then append any non matching values to the table.

This is the query I have so far:

index="dg_*"
| fieldsummary
| rename field AS DataField
| fields DataField
| inputlookup fieldlist2.csv DataField OUTPUT DataField AS exists
| where isnull(exists)
| fields - exists
| fields DataField
| outputlookup append=t fieldlist2.csv 

The problem I have is that when I run the query I receive the following error:

Error in 'inputlookup' command: Invalid argument: 'DataField'

I just wondered whether someone could look at this please and let me know where I've gone wrong?

Many thanks and kind regards

Chris

0 Karma
1 Solution

IRHM73
Motivator

Hi @cusello et al.

I spoke to a colleague of mine who is certainly a lot more advanced than I in the use of Splunk and the solution is:

index="dg_*"
| fieldsummary
| rename field as DataField
| lookup fieldlist2.csv DataField OUTPUT DataField AS exists
| where isnull(exists)
| fields - exists
| fields DataField
| outputlookup append=t fieldlist2.csv 

Note the change from 'inputlookup' to 'lookup'.

May thanks and kind regards

Chris

View solution in original post

IRHM73
Motivator

Hi @cusello et al.

I spoke to a colleague of mine who is certainly a lot more advanced than I in the use of Splunk and the solution is:

index="dg_*"
| fieldsummary
| rename field as DataField
| lookup fieldlist2.csv DataField OUTPUT DataField AS exists
| where isnull(exists)
| fields - exists
| fields DataField
| outputlookup append=t fieldlist2.csv 

Note the change from 'inputlookup' to 'lookup'.

May thanks and kind regards

Chris

gcusello
Esteemed Legend

Sorry but I don't understand what are the values in your lookup: why you use fieldsummary command and don't dedup your events?
try something like this:

index="dg_*" 
NOT [ | inputlookup fieldlist2.csv | table DataField ]
|dedup DataField 
| table DataField
| outputlookup append=t fieldlist2.csv

Bye.
Giuseppe

0 Karma

IRHM73
Motivator

Hi @cusello, thank you for coming back to me with this.

The 'fieldsummary' command is used to extract the field values from the specific indexes, so this needs to be included in the query.

Many thanks and kind regards

Chris

0 Karma

IRHM73
Motivator

Hi @cusello, thank you for coming back to me with this.

I've clearly written this wrong, but when I use the query below I can't extract any results.

index="dg_*" NOT 
[ | inputlookup fieldlist2.csv
| where isnull(exists)
| fields DataField]
| fieldsummary
| fields DataField
| outputlookup append=t fieldlist2.csv

Could you please let me know where I've gone wrong?

Many thanks and kind regards

Chris

0 Karma

gcusello
Esteemed Legend

Hi IRHM73,
you have to insert the "| inputlookup" command as a subsearch of the first search, something like this:

index="dg_*" NOT [ | inputlookup fieldlist2.csv | fields DataField ]
| ...

where DataField is the field that you want to use for search, if you have more fields you have to put all of them in the subsearch "fields" command.

"| inputlookup" isn't a command to use in the middle of a search, but only in the main search (as first) or in a subsearch.

Bye.
Giuseppe

0 Karma
Get Updates on the Splunk Community!

Splunk Education - Fast Start Program!

Welcome to Splunk Education! Splunk training programs are designed to enable you to get started quickly and ...

Five Subtly Different Ways of Adding Manual Instrumentation in Java

You can find the code of this example on GitHub here. Please feel free to star the repository to keep in ...

New Splunk APM Enhancements Help Troubleshoot Your MySQL and NoSQL Databases Faster

Splunk Observability has two new enhancements to make it quicker and easier to troubleshoot slow or frequently ...