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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...