return match of mv field from a csv lookup

[richtate]

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

[ITWhisperer]

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

[richtate]

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!

[ITWhisperer]

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

[richtate]

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.

[ITWhisperer]

Sorry - try with capitalised AS

| lookup xyz.csv pnum AS parts_num OUTPUTNEW broker_id

[richtate]

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

[ITWhisperer]

Where are the commas coming from?

[richtate]

I don't know, here is the search:

[ITWhisperer]

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

[richtate]

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

[ITWhisperer]

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

| inputlookup xyz.csv
| head 1

[richtate]