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.
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
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.
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.
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
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
And the result is like that
--
`| inputlookup vpn.csv
| join IP
[inputlookup vpn2.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.
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.
Whats' the full search that you've tried? Also, when you said it didn't work, what error or wrong result you were getting?
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.