That's a good reduction with the stats values, I assume the simple host count is significantly less than the 1.9m rows, so although you will have the same number of qids per host, the lookup command count will be reduced - it would be interesting to compare the job inspector details between the two searches. As for KV store replication - a KV store on the search head is not a KV store on the indexer, instead a CSV is transferred to the indexers, so any accelerations in the KV are lost and you are simply using CSV lookups on the indexer. I am not sure how a 250MB CSV on the indexer will be handled - if it works the same way as on the SH, it exceeds the  max_memtable_bytes value discussed in limits.conf https://docs.splunk.com/Documentation/Splunk/9.2.0/Admin/Limitsconf#.5Blookup.5D so I imagine it will then be "indexed" (as in a file system index) on disk. If you are not running in Splunk Cloud you may want to try a local CSV lookup and play around with the limits.conf settings for your app. If you have the time, you might also want to experiment with using the eval lookup() statement and use a split CSV. For example, you could split the CSV into 10 * 25MB files to stay under the existing threshold and partition the QIDs into each lookup. Then you could do some weird SPL like  | eval p=partition_logic(QID) | eval output_json=case( p=1, lookup("qid_partition_1.csv", json_object("QID", QID), json_array("field1", "field2"), p=2, lookup("qid_partition_2.csv", json_object("QID", QID), json_array("field1", "field2"), p=3, lookup("qid_partition_3.csv", json_object("QID", QID), json_array("field1", "field2"), p=4, lookup("qid_partition_4.csv", json_object("QID", QID), json_array("field1", "field2"), p=5, lookup("qid_partition_5.csv", json_object("QID", QID), json_array("field1", "field2"), p=6, lookup("qid_partition_6.csv", json_object("QID", QID), json_array("field1", "field2"), p=7, lookup("qid_partition_7.csv", json_object("QID", QID), json_array("field1", "field2"), p=8, lookup("qid_partition_8.csv", json_object("QID", QID), json_array("field1", "field2"), p=9, lookup("qid_partition_9.csv", json_object("QID", QID), json_array("field1", "field2"), p=10, lookup("qid_partition_10.csv", json_object("QID", QID), json_array("field1", "field2")) technically this could work, but whether you would see any improvements, I have no idea. Was that 50 seconds using local lookup or standard lookup. If it was normal (remote) then try using local so it does use the local KV store 

Hi @marnall  Your suggestion worked fine.   I accepted this as a solution. Thank you so much for your help. It looks like the reason it didn't work earlier because I assigned  eval _time = info_max_time, but I didn't put "addinfo", so it went to default value which is info_min_time. Can you test on your end if _time set to info_min_time, if you don't use/remove the following eval? Thanks | eval _time = now() + 3600  

Didn't think that far ahead but Ill be making a dashboard for this so I think it would be easier if I separated them instead of trying to put it all in one search. What you just did works perfectly so thank you so much for that! 

Thanks for the reply! My team ended up having an OnDemandService request to look into this. Will report back. To answer your question by question. The irony is that it was originally CSV that we have since converted to kvStore for performance. The CSV file had became big, like 250 MB if I recall. This is the Qualys Knowledge Base we are talking about that Qualys provide out of box so it is not something we can trim down to size as CSV. The row count after the first stats and before lookup is 1,926,000. The fact stats calculates this in 20 seconds is perfectly fine. The problem becomes when lookup is used, it puts on an extra 140 seconds or so according to the Job Inspector. The dc(HOST_ID) ultimately ends with 7,800 rows. Now for your suggested approach - good catch for the stats. That works great for this particular query that only cares about PATCHABLE. In fact, the last stats can be changed from dc() to just count. Much faster at 50 seconds now! At some point however, we will need to provide full vulnerability data pulled from Qualys Knowledge Base through Splunk as the means of reporting for the engineering teams. We will run into this problem of the lookup hanging up for at least 140 seconds again. Regarding poor performance, when you say 'replicate' - is this what you mean for collections.conf? Because the kvstore is already replicated. [qualys_kb_kvstore] accelerated_fields.QID_accel = {"QID": 1} replicate = true  I think ultimately, we were under the impression the kvStore replication to the indexers makes it so there's a local copy handy for them, making a lookup really fast in matter of seconds. Maybe we had the wrong expectation?

If your JSON-compliant data contains two arrays that has to be mapped externally, your developers have committed the highest design crime.  If you have any influence over development team, beg them, implore them, curse them to change custom_attributes to something like   {"root-entity-id":"3","campaign-id":"XXXX","campaign-name":"XXXXX","marketing-area":"CCCC","record_count":"","country":"","id_array":[{"internal":"12345678","lead":"000000"},{"internal":"9876543","lead":"1111111"},{"internal":"2341234","lead":"3333333"}]}   This way, data processing (in any language, not just Splunk) will be much cleaner.  More importantly, downstream programmers such as yourself will not need to have this vertical knowledge about implied semantics. No implied semantics is one of the most important advantages for people to adopt structured data formats such as JSON.  This means lower maintenance cost in the future.

Is the data the same data or different? What is the search in each case. Take a look at the job inspector and job properties https://www.splunk.com/en_us/blog/tips-and-tricks/splunk-clara-fication-job-inspector.html Have a look at the phase0 job property in each case and also look at the LISPY in the search.log  

Have you tried making the Qualys CSV a CSV rather than KV and tried using that? Does it exceed the CSV size threshold? What's the row count after the stats because you're only doing the lookup on the aggregated host count Have you tried this approach instead? | stats count values(QID) by HOST_ID | lookup qualys_kb_kvstore QID AS QID OUTPUTNEW PATCHABLE | search PATCHABLE="YES" | stats dc(HOST_ID) ```Number of patchable hosts!``` which will reduce the stats host count to one row per host and then do an MV lookup - all you care about it PATCHABLE = "yes" in any of the returned results, so then changing where to search will find any MV value of YES I do recall having some poor performing KV searches some time ago, but we ended up moving away from KV store anyway, because most of our lookups were needed to be done on the indexers, so unless you replicate, the data is returned to the SH and when KV is replicated, it ends up as CSV on the indexer anyway.

You can't do exactly that with a 2 line header, but depending on your SPL, yes, it's possible. I'm guessing you have those resuts from a chart or stats command. The columns are 'sorted', so all you need to do is make your column names Start-ApplicationX and Stop-ApplicationX  so the starts come before the stops or you can just take your existing table and use the table command to do | table *Start *Stop

How are you reading the values from the lookup table - you didn't say if this was a multiselect dropdown input? No you cannot do what you suggest here. "parameters" generally mean tokens and multiselect specifically support this type of case.