Append data from an Index to a lookup

[dgitdos]

Hello, 

Splunk newbie here. I have a CSV file with a bunch of hostnames titled 'Device' that I added as a lookup 'hostnames.csv'. I have an index that has ComputerName, User, and a bunch of other fields. I want the Index data to enrich my csv data by adding the User that corresponds to the hostname. I will then export back to csv to hand the data to someone else.  Does anyone have some pointers so I can achieve this?

I was looking at other similar posts, but I couldn't figure out if I need append, outputlookup, join or something else. This is what I have so far. 

|inputlookup lookup.csv
| append [ search index=data source=Source1 Code=22]
| rename Device as ComputerName
| table ComputerName user_email 

[gcusello]

Hi @dgitdos,

the solution depends on one requirement: your have only to add the new hostnames and usermails or you have also to update the values already in the lookup?

If the first you could add something like this:

index=data source=Source1 Code=22 NOT [ | inputlookup lookup.csv | rename  Device  AS ComputerName | fields ComputerName ]
| dedup ComputerName
| table ComputerName user_email 
| outputlookup lookup.csv append=true

if the second one, try something like this:

index=data source=Source1 Code=22 NOT [ | inputlookup lookup.csv | rename  Device  AS ComputerName | fields ComputerName ]
| append [ | inputlookup lookup.csv | rename  Device  AS ComputerName | fields ComputerName user_email ]
| stats last(user_mail) AS user_mail BY ComputerName
| outputlookup lookup.csv

Ciao.

Giuseppe

[dgitdos]

Hey @gcusello ,

Thanks for your suggestion! I ran the search but I seem to be getting more results than I was expecting. 

Let me see if I can clear up what I am wanting. I don't want to add any new hostnames. I just want to take the Hostnames that I have in the lookup.csv and have their associated user added. 

lookup.csv contains lets say 200 hostnames

Devices

Device1

Device2

Device 3

Device200

Index contains hostnames and associated users + a bunch of other data. 

I want to use the index data to fill in the user field for the 200 devices that I have. 

Thanks so much for your help!

[gcusello]

Hi @dgitdos,

let me understand:

• you want to take from the index only devices already contained in the lookup and exclude the others,
• then you want to update the field user in the lookup with the last value you found in the index for each device,

is this correct?

if yes, you can modify my second search:

index=data source=Source1 Code=22 [ | inputlookup lookup.csv | rename  Device  AS ComputerName | fields ComputerName ]
| append [ | inputlookup lookup.csv | rename  Device  AS ComputerName | fields ComputerName user_email ]
| stats last(user_mail) AS user_mail BY ComputerName
| outputlookup lookup.csv

Ciao.

Giuseppe