Splunk Search

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

Kishorebk
New Member

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.

Tags (1)
0 Karma
1 Solution

dolivasoh
Contributor

Try using a join,

| join IP [|inputlookup ip_.csv]

View solution in original post

0 Karma

Kishorebk
New Member

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

0 Karma

dolivasoh
Contributor

Try using a join,

| join IP [|inputlookup ip_.csv]

0 Karma
Get Updates on the Splunk Community!

Announcing Scheduled Export GA for Dashboard Studio

We're excited to announce the general availability of Scheduled Export for Dashboard Studio. Starting in ...

Extending Observability Content to Splunk Cloud

Watch Now!   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to leverage ...

More Control Over Your Monitoring Costs with Archived Metrics GA in US-AWS!

What if there was a way you could keep all the metrics data you need while saving on storage costs?This is now ...