Question for lookup a large blacklist IP

[kitkit321]

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!

[thambisetty]

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)

[kitkit321]

Thank you for your help.

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

[bowesmana]

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

[kitkit321]

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?

[bowesmana · Accepted solution]

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

[kitkit321]

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.

[bowesmana]

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]

Thank you for your help! the problem is solved.