Using the results of a query , and search it in a lookup table

[Kishorebk]

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.

I know we can use Sub search, but I'm not sure how to integrate both. Using eval in a lookup query.

[Kishorebk]

Thanks dolivasoh.

I tried but it doesn't seem to work.

I giving you the query

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!="Connection denied from") | rename XXX_dest_IP as dest | rex "failed\D\s+Login\s+to\s+account\s+(?<Bruteforceuser>\w*)" | rename Username as Bruteforceuser | rename XXX_user as Bruteforceuser | rex "for\s+user\s+(?<Bruteforceuser>[^,]+)" | rex ""."com\s+"("+(?<Accept_IP>\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3})" | rex "-\sConnection\sdenied\sfrom\sIP\saddress\s(?<Bruteforce_IP>\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3})" | rex "IPAddress=+(?<Bruteforce_IP>\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3})" | rex "coming\sfrom\s(?<Bruteforce_IP>\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

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".

[dolivasoh · Accepted solution]

Try using a join,

| join IP [|inputlookup ip_.csv]