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
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...