Splunk Search

return match of mv field from a csv lookup

richtate
Path Finder

I have an index with a mv field (parts) that I want to match a value in that field with a csv file, but only return that matching value.  Currently, all parts are returned if any of the parts match the csv.  This is what I'm using:

index=inventory sourcetype=parts [inputlookup xyz.csv | table pnum | rename pnum as parts_num]  | table parts_num year vendor model

The problem is parts_num is mv so it will return all parts in the index.  How can I return only the rows with pnum from the csv?  I only want the year vendor and model that matches the csv...

 

Labels (3)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Try something like this

index=inventory sourcetype=parts
| mvexpand parts_num
| search [inputlookup xyz.csv | table pnum | rename pnum as parts_num]  
| table parts_num year vendor model
0 Karma

richtate
Path Finder

I get zero returned results now.  Also another issue just came up, the csv has the broker_id in it that we want to add to the returned results.  Like |table parts_num year vendor model broker_id.  How can I add a column from the csv to the table as well?

Thanks for your help!

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Try this

index=inventory sourcetype=parts
| mvexpand parts_num
| lookup xyz.csv pnum as parts_num OUTPUTNEW broker_id
| where isnotnull(broker_id)  
| table parts_num year vendor model broker_id
0 Karma

richtate
Path Finder

I got the error this time:

  • [PN89407234] Streamed search execute failed because: Error in 'lookup' command: Could not construct lookup 'xyz.csv, pnum, as, parts_num, OUTPUTNEW, broker_id'. See search.log for more details

I don't have access to the search.log for more details.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Sorry - try with capitalised AS

| lookup xyz.csv pnum AS parts_num OUTPUTNEW broker_id
0 Karma

richtate
Path Finder

Sorry, could not work on it yesterday.

I'm still getting the same error, just 'AS' instead of 'as'...

  • [PN98407519] Streamed search execute failed because: Error in 'lookup' command: Could not construct lookup 'xyz.csv, pnum, AS, parts_num, OUTPUTNEW, broker_id'. See search.log for more details
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Where are the commas coming from?

0 Karma

richtate
Path Finder

I don't know, here is the search:

richtate_0-1636645922187.png

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Your lookup file doesn't exist or isn't accessible by your user. Check here

ITWhisperer_0-1636646695775.png

 

0 Karma

richtate
Path Finder

I thought that a couple of days ago, so I changed the permissions before I started this conversation:

richtate_0-1636647296093.png

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Do you get anything if you just check the contents of the lookup?

| inputlookup xyz.csv
| head 1
0 Karma

richtate
Path Finder

richtate_0-1636654257811.png

 

0 Karma
Get Updates on the Splunk Community!

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...

Reminder! Splunk Love Promo: $25 Visa Gift Card for Your Honest SOAR Review With ...

We recently launched our first Splunk Love Special, and it's gone phenomenally well, so we're doing it again, ...