What is the best way to filter results for two queries with big data sets?

[jbanAtSplunk]

Hello, trying to create visualization that will show results from KV_Store used as filter and then query index.

Basically.

1) KV Store DB -> for example: Assets (hostname, ip,  key_id, ...). used as inputlookup -> this is much faster and can be populated from multiple index-es easier (also solve JOIN 50k limit).

2) Search index last 7 days that holds 200k+ results, index should be be filtered by key_id (returned from KV Store, KV store can be filtered much more granular from key_id than index that we wanna query later as it does not hold some fields that we wanna filter by).

Query execute and kv_store return key_id that should be passed as filter to index search.

What is the best way to filter based on two searches in big data sets (every data set is 50k+ events).

currently using (filter example with * so it can be 1 or 50k+ key_id's)
index=test [|inputlookup kv_store_lookup where filter=* | fields key_id ]

this search works well when I have filter with 10, 20, 50 key_id's (got results in a matter of second), when it's "*" with 10k+ key id's then it's a little slow (10 seconds+)
.
Is there "some better way" or my queries are good that will be Visualization search combined from two searches where first search returns key_id's that second search should use.

[bowesmana · Accepted solution]

subsearch limits apply, (50,000) results from the subsearch, so your inputlookup will be constrained by that.

If you look at the job inspector and look at the search log you will see the 'expanded' search from your subsearch in a log entry 'UnifiedSearch'.

I suggest, you try using the KV store as a lookup - and use this type of syntax

index=test 
| lookup kv_store_lookup fields key_id 
| where isnotnull(expected_field_from_lookup)

i.e. select all the data from the index (but be as restrictive as possible), then after the lookup, using isnotnull on a field that MUST be present in the lookup, will effectively act as a filtered view of the data in index=test.

FYI: KV store performance can be improved with accelerated fields 

https://dev.splunk.com/enterprise/docs/developapps/manageknowledge/kvstore/usingconfigurationfiles/#...

and this can make a significant different depending on usage.

[jbanAtSplunk]

Tnx. looks like will go with lookup (as inputlookup could break in future). Changing a logic a bit (for 180°), but Ok.

It's hard to "be restrictive" as Drop Down input selection | inputlookup xyz.csv does not work with "Submit" button. So, will initiate wildcard search least on initial opening of Visualization.