Splunk Search

Question for lookup a large blacklist IP

kitkit321
Explorer

Dear All,

I encounter a question on setting up a blacklist ip use case.

I create a blacklist.csv which stored over 500,000 record and the format is like

BlacklistIP

x.x.x.x

abc.com

y.y.y.y

bcd.com

 

I use the following search 


index=test dst_ip=* OR src_ip=* [ | inputlookup blacklist.csv | fields BlacklistIP | rename BlacklistIP as query]

however, I discovered that splunk is limited the subsearch to 10000 result.

If the 1.1.1.1 is in col 1000 and the src_ip/dst_ip is 1.1.1.1, it appears in the search result.

If the 3.3.3.3 is in col 30000, even the src_ip/dst_ip is 3.3.3.3, it is not appear in the search result.

If the 4.4.4.4 is in col 50000, even the src_ip/dst_ip is 4.4.4.4, it is not appear in the search result.

After i change the subsearch limit in the limit.conf,

maxout = 1,000,000

maxtime = 240

ttl = 600

The result contain 3.3.3.3 but 4.4.4.4 is still not appear.

Also, the search is taking a long time, may be around 5 to 6 mins.

here is the hardware spec.

Splunk Enterprise Server 8.0.4 Linux, 7.64 GB Physical Memory, 8 CPU Cores Mode: Standalone

Is there any suggestion for me? Thank you for help!

 

Labels (2)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

@kitkit321 

Yes, you can use a single column lookup, like this

| lookup blacklist.csv src_ip as ip OUTPUT ip as foundIp
| where !isnull(foundIp)

so, the lookup will return the IP if found into the new field 'foundIp' and then you can test for not null on that new field.

 

View solution in original post

0 Karma

thambisetty
Super Champion

if you are using lookup command look at splunk lookup property that lookup should have 2 fields at least to be qualified as lookup.

https://youtu.be/E3-JaZEIXPw

add a new column let’s say flag and have value yes for all rows.

index=test dst_ip=* OR src_ip=*
| lookup blacklist.csv src_ip as ip OUTPUT flag as foundIp
| where isnotnull(foundIp)

————————————
If this helps, give a like below.
0 Karma

kitkit321
Explorer

Thank you for your help.

If I don't use lookup, is there any suggestion to perform the blacklist?

0 Karma

bowesmana
SplunkTrust
SplunkTrust

You are using the contents of the lookup as search criteria. In the initial instance, you would be better off just searching all the data and then looking up the value in the blacklist and based on the result act accordingly

index=test dst_ip=* OR src_ip=* 
| lookup blacklist.csv ip as dst_ip OUTPUT domain
| lookup blacklist.csv ip as src_ip OUTPUTNEW domain
| where !isnull(domain)

where your lookup is

ip,name

1.1.1.1,abc.com

However, CSV lookups are not very efficient, so you should think about a KV store. Also, you should think about using CIDR as a way to define IP ranges, where appropriate, so that you can limit your rows.

For that you would need to create a lookup definition on top of the CSV/KV store lookup data, as that is how it can do CIDR lookups

 

0 Karma

kitkit321
Explorer

However, the csv is one contain 1 column, it is just like

ip

1.1.1.1

2.2.2.2

3.3.3.3

4.4.4.4.

 

Since there is no 2 columns in the csv, i think i cannot use the lookup command.

In other word, how can i use dst_ip  or src_ip to search in the blacklist.csv?

0 Karma

bowesmana
SplunkTrust
SplunkTrust

@kitkit321 

Yes, you can use a single column lookup, like this

| lookup blacklist.csv src_ip as ip OUTPUT ip as foundIp
| where !isnull(foundIp)

so, the lookup will return the IP if found into the new field 'foundIp' and then you can test for not null on that new field.

 

0 Karma

kitkit321
Explorer

index=test dst_ip=* OR src_ip=*
| lookup blacklist.csv src_ip as ip OUTPUT ip as foundIp
| where !isnull(foundIp)

After i perform the search, the error message is
Error in 'lookup' command: Could not find all of the specified lookup fields in the lookup table.

0 Karma

bowesmana
SplunkTrust
SplunkTrust

Argh, my bad - the syntax is the wrong way round, should have been

| lookup blacklist.csv ip as src_ip OUTPUT ip as foundIp

 

kitkit321
Explorer

Thank you for your help! the problem is solved. 

0 Karma
Get Updates on the Splunk Community!

New Cloud Intrusion Detection System Add-on for Splunk

In July 2022 Splunk released the Cloud IDS add-on which expanded Splunk capabilities in security and data ...

Happy CX Day to our Community Superheroes!

Happy 10th Birthday CX Day!What is CX Day? It’s a global celebration recognizing innovation and success in the ...

Check out This Month’s Brand new Splunk Lantern Articles

Splunk Lantern is a customer success center providing advice from Splunk experts on valuable data insights, ...