https://community.splunk.com/t5/Splunk-Search/Using-the-results-of-a-query-and-search-it-in-a-lookup-table/m-p/174433#M50054 <P>I have a query which looks at FTP attacks, and the resulting field is called "IP", now i want to search the results for the IP field in a lookup table and return if the IP is present in the lookup table.</P> <P>I know we can use Sub search, but I'm not sure how to integrate both. Using eval in a lookup query.</P> Thu, 07 May 2015 16:36:00 GMT Kishorebk 2015-05-07T16:36:00Z https://community.splunk.com/t5/Splunk-Search/Using-the-results-of-a-query-and-search-it-in-a-lookup-table/m-p/174433#M50054 <P>I have a query which looks at FTP attacks, and the resulting field is called "IP", now i want to search the results for the IP field in a lookup table and return if the IP is present in the lookup table.</P> <P>I know we can use Sub search, but I'm not sure how to integrate both. Using eval in a lookup query.</P> Thu, 07 May 2015 16:36:00 GMT https://community.splunk.com/t5/Splunk-Search/Using-the-results-of-a-query-and-search-it-in-a-lookup-table/m-p/174433#M50054 Kishorebk 2015-05-07T16:36:00Z https://community.splunk.com/t5/Splunk-Search/Using-the-results-of-a-query-and-search-it-in-a-lookup-table/m-p/174434#M50055 <P>Try using a <A href="http://docs.splunk.com/Documentation/Splunk/6.2.3/SearchReference/Join">join</A>,</P> <P>| join IP [|inputlookup ip_.csv]</P> Thu, 07 May 2015 16:59:58 GMT https://community.splunk.com/t5/Splunk-Search/Using-the-results-of-a-query-and-search-it-in-a-lookup-table/m-p/174434#M50055 dolivasoh 2015-05-07T16:59:58Z https://community.splunk.com/t5/Splunk-Search/Using-the-results-of-a-query-and-search-it-in-a-lookup-table/m-p/174435#M50056 <P>Thanks dolivasoh. </P> <P>I tried but it doesn't seem to work. </P> <P>I giving you the query</P> <P>index=* ("WARNING: DNS " OR "password authentication failed." OR "Authentication failed" OR "Login successful" ) OR (Message="There is no such user" OR "Failed to sign on: This IP address has been locked out.") OR ("Invalid login credentials;" XXX_ftp_ip!=xxx.* _raw!="<EM>Connection denied from</EM>") | rename XXX_dest_IP as dest | rex "failed\D\s+Login\s+to\s+account\s+(?&lt;Bruteforceuser&gt;\w*)" | rename Username as Bruteforceuser | rename XXX_user as Bruteforceuser | rex "for\s+user\s+(?&lt;Bruteforceuser&gt;[^,]+)" | rex ""."com\s+"("+(?&lt;Accept_IP&gt;\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3})" | rex "-\sConnection\sdenied\sfrom\sIP\saddress\s(?&lt;Bruteforce_IP&gt;\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3})" | rex "IPAddress=+(?&lt;Bruteforce_IP&gt;\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3})" | rex "coming\sfrom\s(?&lt;Bruteforce_IP&gt;\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3})" | iplocation Bruteforce_IP | iplocation Accept_IP | search Accept_IP!="xx.xxx*" AND Accept_IP!="xxx.xxx*" AND Accept_IP!="xx.xx*" AND Accept_IP!="xxx.xxx*" | eval status=if(Bruteforce_IP=Accept_IP, "ACCEPTED", "DENIED") | rename Accept_IP as IP | rename Bruteforce_IP as IP | stats count values(host) as dest, dc(Bruteforceuser) as bruteuser_count, values(Bruteforceuser) as Brute_userid values(index) as index by IP, Country, status | fields index, IP, count, Country, status, dest,bruteuser_count,Brute_userid | sort - count </P> <P>Now I want to use the field "IP" to search in the lookup table "Newbadlist" which has a field BadIp. And use eval to search if IP was seen in the lookup and if so , i should see the output under a field as "badIP" or "not badIP".</P> Mon, 28 Sep 2020 19:58:43 GMT https://community.splunk.com/t5/Splunk-Search/Using-the-results-of-a-query-and-search-it-in-a-lookup-table/m-p/174435#M50056 Kishorebk 2020-09-28T19:58:43Z