https://community.splunk.com/t5/Splunk-Search/Add-a-lookup-csv-colum-information-to-the-results-of-a/m-p/83554#M21285 <P>Hi,</P> <P>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:</P> <P>example of lookup table called ipwatchlist.csv:</P> <P>category ip_address isbad </P> <P>Fake-AV 109.235.251.49 TRUE</P> <P>Fake-AV 109.235.251.51 TRUE</P> <P>This is my search:<BR /> sourcetype=firewall [|inputlookup ipwatchlist.csv | fields ip_address | rename ip_address as dest_ip] | stats count by src_ip dest_ip | sort desc - count</P> <P>This gives me a result of:</P> <P>src_ip dest_ip count</P> <P>192.168.1.1 109.235.251.49 50</P> <P>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: </P> <P>src_ip dest_ip Category count</P> <P>192.168.1.1 109.235.251.49 Fake-AV 50</P> <P>Does anyone has an idea how to do this?</P> Mon, 28 Sep 2020 11:28:22 GMT Mannyi31 2020-09-28T11:28:22Z https://community.splunk.com/t5/Splunk-Search/Add-a-lookup-csv-colum-information-to-the-results-of-a/m-p/83554#M21285 <P>Hi,</P> <P>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:</P> <P>example of lookup table called ipwatchlist.csv:</P> <P>category ip_address isbad </P> <P>Fake-AV 109.235.251.49 TRUE</P> <P>Fake-AV 109.235.251.51 TRUE</P> <P>This is my search:<BR /> sourcetype=firewall [|inputlookup ipwatchlist.csv | fields ip_address | rename ip_address as dest_ip] | stats count by src_ip dest_ip | sort desc - count</P> <P>This gives me a result of:</P> <P>src_ip dest_ip count</P> <P>192.168.1.1 109.235.251.49 50</P> <P>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: </P> <P>src_ip dest_ip Category count</P> <P>192.168.1.1 109.235.251.49 Fake-AV 50</P> <P>Does anyone has an idea how to do this?</P> Mon, 28 Sep 2020 11:28:22 GMT https://community.splunk.com/t5/Splunk-Search/Add-a-lookup-csv-colum-information-to-the-results-of-a/m-p/83554#M21285 Mannyi31 2020-09-28T11:28:22Z https://community.splunk.com/t5/Splunk-Search/Add-a-lookup-csv-colum-information-to-the-results-of-a/m-p/83555#M21286 <P>Well you already have ipwatchlist.csv as a lookup table to just use that <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P> <PRE><CODE>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 </CODE></PRE> Fri, 02 Mar 2012 16:12:57 GMT https://community.splunk.com/t5/Splunk-Search/Add-a-lookup-csv-colum-information-to-the-results-of-a/m-p/83555#M21286 Ayn 2012-03-02T16:12:57Z https://community.splunk.com/t5/Splunk-Search/Add-a-lookup-csv-colum-information-to-the-results-of-a/m-p/83556#M21287 <P>Thanks Ayn, Your answer worked after I made some modifications and below is the end result:</P> <P>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</P> Mon, 28 Sep 2020 11:28:44 GMT https://community.splunk.com/t5/Splunk-Search/Add-a-lookup-csv-colum-information-to-the-results-of-a/m-p/83556#M21287 Mannyi31 2020-09-28T11:28:44Z https://community.splunk.com/t5/Splunk-Search/Add-a-lookup-csv-colum-information-to-the-results-of-a/m-p/83557#M21288 <P>Awesome. Could you please mark my answer as accepted? Thanks!</P> Mon, 05 Mar 2012 15:34:50 GMT https://community.splunk.com/t5/Splunk-Search/Add-a-lookup-csv-colum-information-to-the-results-of-a/m-p/83557#M21288 Ayn 2012-03-05T15:34:50Z