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

[batuhankutluca]

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.

[woodcock]

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

[batuhankutluca]

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.

[woodcock]

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.

[batuhankutluca]

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.

--
`sourcetype=firewall action=pass action=proxy
| bin _time span=5m
| dedup srcip
| eval Time = strftime(_time,"%Y-%m-%d %H:%M:%S")
| table Time, srcip, srcport, dstip
| rename srcip AS IP

| outputlookup vpn.csv`

This is my 2nd search with the output csv.

--
`index=server sourcetype=dhcp
| lookup vpn.csv srcip AS dest_ip
| search Time=*
| eval event_time=strptime(Time,"%Y-%m-%d %H:%M:%S")
| where _time < event_time
| stats latest(dest_nt_host) as dest_nt_host max(_time) as _time by dest_ip
| sort - _time
| rename dest_ip AS IP

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

[batuhankutluca]

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.

[somesoni2]

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

[batuhankutluca]

sourcetype=firewall action=pass action=proxy
| bin _time span=5m
| dedup srcip
| eval Time = strftime(_time,"%Y-%m-%d %H:%M:%S")
| table Time, srcip, srcport, dstip, dstport, app
| outputlookup vpn.csv

This is my first search to create a lookup.

And my 2nd search is

sourcetype=dhcp
| lookup vpn.csv srcip AS dest_ip
| search Time=*
| eval event_time=strptime(Time,"%Y-%m-%d %H:%M:%S")
| where _time < event_time
| stats latest(dest_nt_host) as dest_nt_host by dest_ip
| outputlookup append=true vpn.csv

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