topic Re: append a field value to a lookup csv file in Splunk Search

append a field value to a lookup csv file

jtran9373 — Fri, 25 Oct 2024 17:19:24 GMT

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!!!

Re: append a field value to a lookup csv file

yuanliu — Fri, 25 Oct 2024 18:26:18 GMT

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.

Re: append a field value to a lookup csv file

jtran9373 — Fri, 25 Oct 2024 18:39:46 GMT

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

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.

Re: append a field value to a lookup csv file

yuanliu — Fri, 25 Oct 2024 18:45:26 GMT

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:

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

Re: append a field value to a lookup csv file

PickleRick — Sat, 26 Oct 2024 07:58:55 GMT

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

Re: append a field value to a lookup csv file

jtran9373 — Tue, 29 Oct 2024 13:24:01 GMT

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

Re: append a field value to a lookup csv file

yuanliu — Wed, 30 Oct 2024 06:24:48 GMT

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

Re: append a field value to a lookup csv file

jtran9373 — Wed, 30 Oct 2024 11:20:31 GMT

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

Re: append a field value to a lookup csv file

yuanliu — Thu, 31 Oct 2024 02:44:15 GMT

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.