How to optimize this search ?

[Woodpecker]

Hi,

I have an SPL, which should exclude the ip values from 4 lookups. So i tried it with a subsearch approach. But this search takes a longer time than usual to run.  How can I optimize it?

index=a OR b action=* attack!=N/A NOT ((
    [| inputlookup a.csv
    | fields ip 
    | rename ip as srcip]) OR ( 
    [| inputlookup b.csv
    | fields ip 
    | rename ip as src_ip]) OR ( 
    [| inputlookup c.csv
    | fields ip 
    | rename ip as src_ip]) OR ( 
    [| inputlookup d.csv
    | fields ip 
    | rename ip as src_ip]))  
| stats dc(attack) as dc_attack by src_ip
| where dc_attack >2
| dedup src_ip

Thanks in advance!

[gcusello]

Hi @Woodpecker,

at first, the index condition is wrong, you should have

(index=a OR index=b)

Then, if your lookups don't change frequently and the number of IPs in lookups are less than 50,000, you could schedule a search that creates one lookup to use in the search, deduping the values and reducing in this way the occurrences to check.

Then you could use a KV-Store for your lookup to have better performaces.

Then the last dedup isn't mandatory.

In general your search isn't bad, but, if you have too many events in indexes "a" and "b", you could use a summary index in which you could store only the src_ips to test from your indexes, in this way you'll surely have a much more performant search.

Ciao.

Giuseppe