topic Re: Any suggestion about how to make alert faster which has huge size lookup. in Splunk Search

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

Shuhei052492 — Tue, 29 Sep 2020 18:13:17 GMT

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,

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

HiroshiSatoh — Wed, 28 Feb 2018 06:26:00 GMT

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

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

starcher — Thu, 01 Mar 2018 03:36:34 GMT

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.

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

Shuhei052492 — Thu, 01 Mar 2018 04:05:00 GMT

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

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

Shuhei052492 — Thu, 01 Mar 2018 05:42:17 GMT

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.