Splunk Search
Highlighted

Is it possible to use a column from a .csv lookup file as a column in the results?

New Member

So, I tried https://answers.splunk.com/answers/480296/how-to-add-an-additional-column-in-my-results-from.html?ut... and that answer doesn't seem to work. I've also reviewed the documentation for lookup and inputlookup, but there's something simple here I'm missing (I hope)

So what I have is a .csv full of phone numbers and names, called phonebook.csv:

5135550010 Bob
5135550012 Jake

I have a index in splunk with phone numbers, model of phone, etc. as a data source (let's call it "inventory") I can search:

5135550009 Pineapple 6S

5135550010 Pineapple 7
5135550029 Gootle Paxel 2

What I am trying to match and what I'm trying to end up with should look something like this:

5135550010 Bob Pineapple 7
5135550012 Jake

That is, when the model of the phone exists in the inventory, add it as a field. If it does not exist in the inventory, don't add anything.

I tried this search:

index="inventory" [|inputlookup phonebook.csv | fields PhoneNumber] | stats last(Username), last(Model) BY PhoneNumber

But all this gives me is:

5135550010 Bob Pineapple 7

What I want is to see every row of the original phonebook.csv, even if there are no results returned for that row:

5135550010 Bob Pineapple 7
5135550012 Jake

How does one achieve this? I have done a lot of searching and trying to understand "inputlookup" and "lookup" but I'm just not getting something. It seems so simple.

p.s. I don't have the power to just add phonebook.csv as a data source and just append the results column to that. Our admin is on vacation until next week 😞

0 Karma
Highlighted

Re: Is it possible to use a column from a .csv lookup file as a column in the results?

SplunkTrust
SplunkTrust

Try this...

index="inventory" 
| stats latest(Model) as Model BY PhoneNumber
| rename COMMENT as "Above produces one record per PhoneNumber on file.  use latest() for most recent.  "

| rename COMMENT as "Add all the records then stats them together"
| inputlookup append=t phonebook.csv 
| stats values(Model) as Model values(UserName) as UserName by PhoneNumber 

| rename COMMENT as "Fill in the word unknown for any that had no records on the inventory index"
| eval Model=coalesce(Model,"unknown")

Remember that last() refers to the last record returned, which is usually the earliest record on the index.

0 Karma
Highlighted

Re: Is it possible to use a column from a .csv lookup file as a column in the results?

Builder

What you want to do is use a join instead. You want to join to different sets of data based off of a key. That's what join accomplishes.

0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.