Splunk Search

Any suggestion about how to make alert faster which has huge size lookup.

Shuhei052492
Path Finder

Hi

I have an alert to search proxy logs. And this alert creates its results to match 3 million Proxy logs and lookup files of 40,000 lines.
Also the alert takes 7 ~ 9 hours until it finishes running.

index=proxy sourcetype=proxy [inputlookup Proxy_blacklist.csv | table url ]
| stats count as total_count, last(_time) as ltime, first(_time) as ftime, values(host) as host, values(auth_user) as auth_user by client_ip,url

I would like to make this alert faster.
As I do this, I have the idea to divide the lookup file as 3 file and this alert as 3 alerts too.
If someone has another idea to make it faster, please give me your suggestion and advice.
I appreciate any answers so much.

Best regard,

0 Karma
1 Solution

starcher
SplunkTrust
SplunkTrust

You could use an accelerated data model and tstats.

Or use your lookup as a lookup not as a subsearch.

Avoid using a lookup with more than 100 rows in a subsearch filter pattern that way.

Use this pattern.

... |lookup blacklist url OUTPUT url as isFound | where isnotnull(isFound)

Also use min and max on _time not first and last.

View solution in original post

starcher
SplunkTrust
SplunkTrust

You could use an accelerated data model and tstats.

Or use your lookup as a lookup not as a subsearch.

Avoid using a lookup with more than 100 rows in a subsearch filter pattern that way.

Use this pattern.

... |lookup blacklist url OUTPUT url as isFound | where isnotnull(isFound)

Also use min and max on _time not first and last.

View solution in original post

Shuhei052492
Path Finder

Thanks for your answers.
I understood the important point to use subsearch filter pattern.

Also,unfortunately the search which uses "lookup" and "where" does not improve search performance in my environment.

0 Karma

HiroshiSatoh
Champion

Does the black list URL contain wild cards?
Does the field definition also exist on the indexer side?

0 Karma

Shuhei052492
Path Finder

Yes. All values of this list have wildcards.
Yes. My indexer has same field definition in props.conf.

0 Karma
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!