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