Hi,
I have a lookup search that works fine but I would like to add information from the lookup table that the source log does not have. let me explain:
example of lookup table called ipwatchlist.csv:
category ip_address isbad
Fake-AV 109.235.251.49 TRUE
Fake-AV 109.235.251.51 TRUE
This is my search:
sourcetype=firewall [|inputlookup ipwatchlist.csv | fields ip_address | rename ip_address as dest_ip] | stats count by src_ip dest_ip | sort desc - count
This gives me a result of:
src_ip dest_ip count
192.168.1.1 109.235.251.49 50
Now I would like to add the field in the ipwatchlist.csv of category to see what is the IP associated with, I would like to get a result like:
src_ip dest_ip Category count
192.168.1.1 109.235.251.49 Fake-AV 50
Does anyone has an idea how to do this?
Well you already have ipwatchlist.csv as a lookup table to just use that 🙂
sourcetype=firewall [|inputlookup ipwatchlist.csv | fields ip_address | rename ip_address as dest_ip] | stats count by src_ip dest_ip | sort desc - count | lookup ipwatchlist.csv dest_ip OUTPUT Category | table src_ip dest_ip Category count
Well you already have ipwatchlist.csv as a lookup table to just use that 🙂
sourcetype=firewall [|inputlookup ipwatchlist.csv | fields ip_address | rename ip_address as dest_ip] | stats count by src_ip dest_ip | sort desc - count | lookup ipwatchlist.csv dest_ip OUTPUT Category | table src_ip dest_ip Category count
Awesome. Could you please mark my answer as accepted? Thanks!
Thanks Ayn, Your answer worked after I made some modifications and below is the end result:
sourcetype=firewall [|inputlookup ipwatchlist.csv | fields ip_address | rename ip_address as dest_ip] | stats count by src_ip dest_ip | sort desc - count | lookup ipwatchlist ip_address as dest_ip OUTPUT category | table src_ip dest_ip category count