topic Re: Question for lookup a large blacklist IP in Splunk Search

Question for lookup a large blacklist IP

kitkit321 — Fri, 04 Sep 2020 05:07:43 GMT

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!

Re: Question for lookup a large blacklist IP

bowesmana — Fri, 04 Sep 2020 05:49:42 GMT

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

Re: Question for lookup a large blacklist IP

kitkit321 — Fri, 04 Sep 2020 06:17:21 GMT

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

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?

Re: Question for lookup a large blacklist IP

bowesmana — Fri, 04 Sep 2020 06:35:57 GMT

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

Re: Question for lookup a large blacklist IP

kitkit321 — Fri, 04 Sep 2020 07:18:37 GMT

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.

Re: Question for lookup a large blacklist IP

thambisetty — Fri, 04 Sep 2020 08:05:19 GMT

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)

Re: Question for lookup a large blacklist IP

kitkit321 — Fri, 04 Sep 2020 09:50:29 GMT

Thank you for your help.

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

Re: Question for lookup a large blacklist IP

bowesmana — Sun, 06 Sep 2020 22:43:38 GMT

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

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

Re: Question for lookup a large blacklist IP

kitkit321 — Mon, 07 Sep 2020 01:44:42 GMT

Thank you for your help! the problem is solved.