append a field value to a lookup csv file

[jtran9373]

I have a hostname.csv file and contact these attributes.

hostname.csv

ip                     mac                           hostname

x.x.x.x                                                abc_01
                       00:00:00                  def_02
x.x.x.y           00:00:11                  ghi_03
                                                            jkl_04

i would like to search in Splunk index=* host=* ip=* mac=*, compare my host equal to my hostname column from a lookup file "hostname.csv",  if it matches, then I would like to write ip and mac values to hostname.csv file. the result look like this.
new hostname.csv file.

ip                              mac                                       hostname

x.x.x.x                  00:new:mac                            abc_01
x.x.y.new            00:00:00                                   def_02
x.x.x.y                  00:00:11                                    ghi_03
new.ip                new:mac                                      jkl_04

thank you for your help!!!

[PickleRick]

One important thing - you can't add or remove something to/from csv lookup. You can only overwrite it as a whole.

[yuanliu]

Let me try to understand the requirement.  You will only compare hostname then add ip and mac from index, but only if hostname already exists in hostname.csv.  Is this correct? lookup is your friend.

index=* host=* ip=* mac=*
| fields host ip mac
| dedup host ip mac
| lookup hostname.csv hostname AS host output hostname AS match
| table host ip mac
| outputlookup hostname.csv

[jtran9373]

thank you for your help.
hostname.csv

ip                     mac                           hostname                location

x.x.x.x                                                abc_01                     NYC
                       00:00:00                  def_02                       DC
x.x.x.y           00:00:11                  ghi_03                        Chicago
                                                            jkl_04                         LA

i would like to search in Splunk index=* host=* ip=* mac=*, compare my host equal to my hostname column from a lookup file "hostname.csv",  if it matches, then I would like to write ip and mac values to hostname.csv file. the result look like this.  the based_search doesn't have location. I would like to keep the location column as it.
new hostname.csv file.

ip                              mac                                       hostname                     location

x.x.x.x                  00:new:mac                            abc_01                       NYC_orig
x.x.y.new            00:00:00                                   def_02                        DC_orig
x.x.x.y                  00:00:11                                    ghi_03                        Chicago_orig
new.ip                new:mac                                      jkl_04                        LA_orig


thank you.

[yuanliu]

---

hostname.csv file. the result look like this.  the based_search doesn't have location. I would like to keep the location column as it.

---

Pro tip: It is critical to give full use case and all relevant data when asking a question.  The solution is the same, just add location to output.  But before I illustrate code, you also need to answer the question whether location info is available in index data.  My speculation is not.  But that's just speculation.  It is very important to describe nuances.

Anyway, suppose location is not in index data, here is the search you can use:

index=* host=* ip=* mac=*
| fields host ip mac
| dedup host ip mac
| lookup hostname.csv hostname AS host output hostname AS match location
| table host ip mac location
| outputlookup hostname.csv

 Of course, location will be blank for any host that didn't have location in the old version of hostname.csv.

[jtran9373]

hostname.csv

         ip                              mac                               hostname                                     location                     description

1.      x.x.x.x                                                             abc_01                                           NYC                            null mac
2.                                      00:00:00                       def_02                                            DC                              null ip
3.      x.x.x.y                    00:00:11                        ghi_03                                           Chicago                     no update
4.                                                                                jkl_04                                             LA                                null mac & ip
5.                                                                               Hostname_not_in_idx             Seatle                        not match

i would like to search in Splunk index=* host=* ip=* mac=*, compare my host equal to my hostname column from a lookup file "hostname.csv".
if it matches, then I would like to append ip and mac values from the index=* to hostname.csv file. if it doesn't match the Hostname and host, it will not alter hostname.csv file. (I don't want to overwrite the hostname.cvs. I want to append only the ip and mac values from the index to the hostname.csv file.)
the result look like this. the based_search doesn't have location field. I would like to keep the location column as it.
new hostname.csv file.

            ip                              mac                             hostname                                               location                 description

1.       x.x.x.x                     00:new:mac                abc_01                                                   NYC_orig               append mac
2.       x.x.y.new               00:00:00                       def_02                                                    DC_orig                 append ip
3.       x.x.x.y                      00:00:11                       ghi_03                                                     Chicago_orig     no update
4.       new.ip                     new:mac                       jkl_04                                                       LA_orig               append ip & mac
5.                                                                                  Hostname_not_in_idx                      Seatle                   no update

thank you for your help

[yuanliu]

So, you are indirectly confirming that location information does not exist in index data.  Have you tried the search I gave above?

[jtran9373]

yes, that is correct. I don't want to alter the location and hostname columns. I just want to append the IP and MAC columns if it matches the hostname and host.  In addtion, I don't want to overwrite the hostnames.csv file. 
thank you

[yuanliu · Accepted solution]

---

In addtion, I don't want to overwrite the hostnames.csv file. 

---

You have no choice about this.  CSV file is just a file.  You can append new rows into a file - which your use case does not call for; or you can rewrite the file.