Splunk Search

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

jbanAtSplunk
Communicator

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.

Labels (3)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

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.

 

View solution in original post

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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.

 

0 Karma

jbanAtSplunk
Communicator

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.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...