topic How can I add output of a search as a new column to existing csv ? in Splunk Search

How can I add output of a search as a new column to existing csv ?

batuhankutluca — Mon, 01 Apr 2019 13:04:16 GMT

Hello,
I have an existing .csv named "test.csv". In this csv file, there are fields named srcip and time. Also I have a search getting hostnames for ip's in that csv. Lookup is OK and my search is totally getting the right results. The thing I want to do is updating test.csv with the output of my results. For example I have 1.1.1.1 IP in test.csv. And my search gets "test-pc" hostname for that IP. After my search is completed I want :
- Create a new column in csv as "Host Name"
- Match IP's between csv and my search
- Add hostname for matching IP's to existing test.csv

I tried outputlookup command with append=true but It didn't work.
Hope you help me,
Thanks.

Re: How can I add output of a search as a new column to existing csv ?

somesoni2 — Mon, 01 Apr 2019 19:13:41 GMT

Whats' the full search that you've tried? Also, when you said it didn't work, what error or wrong result you were getting?

Re: How can I add output of a search as a new column to existing csv ?

woodcock — Mon, 01 Apr 2019 20:02:26 GMT

Like this:

Your Search for hostname here
| rename whatever_field_has_IP AS IP
| appendpipe [|inputlookup test.csv | rename whatever_field_has_IP AS IP]
| stats values(*) AS * BY IP
| sort 0 IP(IP)
| outputlookup test.csv

Re: How can I add output of a search as a new column to existing csv ?

batuhankutluca — Wed, 30 Sep 2020 00:00:36 GMT

This is my first search to create a lookup.

And my 2nd search is

Actually I'm not getting any errors, just my lookup is not updating. That's the problem.

Re: How can I add output of a search as a new column to existing csv ?

batuhankutluca — Tue, 02 Apr 2019 05:41:31 GMT

Thanks for your help but this one is overriding the results of an existing csv. I just want to do that steps:
- Create a new column in csv as "Host Name"
- Match IP's between csv and my search
- Add hostname for matching IP's to existing test.csv

I added my searches above as a comment if you want to check.

Re: How can I add output of a search as a new column to existing csv ?

woodcock — Tue, 02 Apr 2019 05:49:49 GMT

My answer does exactly what you are describing. If you are not writing answer back to test.csv then delete the | outputlookup test.csv line.

Re: How can I add output of a search as a new column to existing csv ?

batuhankutluca — Tue, 02 Apr 2019 05:54:52 GMT

I want to write results back to csv just for the matching ip's as a new column. Your query works like overriding all existing columns and rows and write back the new search's results to test.csv. That's why I said like that.

Re: How can I add output of a search as a new column to existing csv ?

batuhankutluca — Wed, 30 Sep 2020 00:00:39 GMT

I think I have figured it out somehow but still don't know why is your search doesn't work as I wanted. Maybe If I upload some screenshots, It will be more clear for you.

This is my first search with the output csv.

| outputlookup vpn.csv`

This is my 2nd search with the output csv.

| outputlookup vpn2.csv`

And the result is like that

--
`| inputlookup vpn.csv
| join IP
[inputlookup vpn2.csv]

| outputlookup vpn3.csv`

Now it works as I wanted. That's what I was trying to explain. But I'm still open for advices.

Images don't show up somehow.