Splunk Search

improving lookup and inputlookup performance

Olli1919
Path Finder

Hi,

when trying to filter a high EPS feed with a lookup I am experiencing quite some performance issues. Are are known paths to get this to run faster?

Basically, I have a feed (averages betwewen 500-2000 EPS) that contains hostnames in "querystring1" and I am trying to filter that using a simple static list of known bad hostnames. The latter has 19.000 entries and does a wildcard match for e.g. "*hitnet.net".

So far I have tried the following:

A) lookup on the index with returning only non-null fields
SPL: index=FeedToFilter | lookup RBL matchstring as querystring1 OUTPUT matchstring as test | where isnotnull(test)
transforms.conf: case_sensitive_match = false / match_type = WILDCARD(matchstring)

This variant is quite slow with taking about 5 minutes to search 2 minutes of events, even on historical data. Adding non-null filters like where "isnotnull(test)" or "search NOT test=*" does slow it down even more.

B) inputlookup on the index
SPL: index=FeedToFilter [ | inputlookup RBL | rename matchstring as matchto | fields + matchto ]

This variant either does not start or takes about 10 minutes to start when the inputlookup is limited with "head 500" (with unlimited inputlookup chrome simply cannot access splunk anymore as long as the search is running. IE remains responsive, but also does not show progress in the unlimited search).

From what I found on Splunk Answers inputlookup will not be a solution as it is limited to < 10k entries. But a lookup should work fine and not choke like that. Any insight on how to speed up that query would be greatly appreciated.

Thanks,
Oliver

0 Karma
1 Solution

Olli1919
Path Finder

The following seems to help a lot:

index=FeedToFilter "IN A*" | rex ".(?[\w-]+.[\w-]+)\sIN" | lookup RBL2 badhost as test OUTPUT badhost | search badhost=*

a) added "IN A*": some prefilter reducing event to work with by 50%
b) rex: eliminating the lookup wildcard match by introducing a rex which only takes the SLD and TLD from full hostnames. E.g. "www.example.org" get removed to "example.org", which is exactly what I have in the lookup csv file.

Now searching 60 minutes of events only takes 80 seconds, not 20 minutes.

If anyone has further ideas, I would be happy to hear them.

View solution in original post

0 Karma

Olli1919
Path Finder

The following seems to help a lot:

index=FeedToFilter "IN A*" | rex ".(?[\w-]+.[\w-]+)\sIN" | lookup RBL2 badhost as test OUTPUT badhost | search badhost=*

a) added "IN A*": some prefilter reducing event to work with by 50%
b) rex: eliminating the lookup wildcard match by introducing a rex which only takes the SLD and TLD from full hostnames. E.g. "www.example.org" get removed to "example.org", which is exactly what I have in the lookup csv file.

Now searching 60 minutes of events only takes 80 seconds, not 20 minutes.

If anyone has further ideas, I would be happy to hear them.

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

Filtering early is always a good idea, provided you know your data really well.

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

The lookup filtering is slow because Splunk needs to load every event, perform the lookup, and then throw it out - instead of filtering using the index structure, and only reading what is likely to be required by the search.

However, filtering through 120k events (2 minutes at 1k eps) should not take 5 minutes despite this lookup drawback, assuming reasonably specced indexers.

Olli1919
Path Finder

playing around with inputlookup shows the following:

index=FeedToFilter [|inputlookup RBL | head 5000 | fields + matchstring | rename matchstring as querystring1]

using the [|inputlookup] instead of a *|lookup removes the delay described above.

[|inputlookup] with head=50 is as fast as a non-parametrized search.

[|inputlookup] with head=500 does not add a performance decrease.

[|inputlookup] with head=5000 suddenly explodes the search, it takes 472 seconds:
392.306 dispatch.evaluate
392.295 dispatch.evaluate.search

0 Karma

Olli1919
Path Finder

Just some more info. With 500k Events:

"index=FeedToFilter" takes 45 seconds
80.435 command.search
107.877 dispatch.stream.remote

"index=FeedToFilter | lookup RBL matchstring as querystring1 OUTPUT matchstring as test" takes 475 seconds
1,259.78 command.lookup
467.29 dispatch.fetch
1,378.063 dispatch.stream.remote

"index=FeedToFilter | lookup RBL matchstring as querystring1 OUTPUT matchstring as test | search NOT test=*" takes 482 seconds
1,241.517 command.lookup
473.866 dispatch.fetch
1,354.832 dispatch.stream.remote

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