Add a lookup csv colum information to the results of a inputlookup search

[Mannyi31]

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?

[Ayn · Accepted solution]

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

[Ayn]

Awesome. Could you please mark my answer as accepted? Thanks!

[Mannyi31]

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