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

[Shuhei052492]

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,

[starcher · Accepted solution]

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.

[Shuhei052492]

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.

[HiroshiSatoh]

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

[Shuhei052492]

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