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

[chambern]

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 😞

[thisissplunk]

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.

[DalJeanis]

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.