How to limit the results of one lookup with a second lookup?

[mistydennis]

Hi all - I am trying to take one lookup and limit its results with another lookup.  I can kinda get it to work with my current SPL, but it's taking a long time to run and the results don't come out as expected. Here's what I have so far:

| inputlookup my_kvstore 
| lookup my_lookup lookupfield_1 AS kvstorefield_1 OUTPUT lookupfield_1 
| lookup my_kvstore kvstorefield_1 AS lookupfield_1 OUTPUT kvstorefield_2, kvstorefield_3, kvstorefield_4, kvstorefield_5 
| WHERE kvstorefield_1=lookupfield_1

Results:

kvstorefield_1	kvstorefield_2	kvstorefield_3	kvstorefield_4	kvstorefield_5	lookupfield_1
2016 2016	centos centos	linux linux	web web	workstation1 workstation2	2016 2016
2017 2017 2017	apache apache apache	tomcat tomcat tomcat	http http http	server1 server2 server3	2017 2017 2017

1. Is my search formed correctly? 
2. How do I get each of the events to come out in their own row instead of being grouped into one line based on the matching kvstorefield/lookupfield?

[bowesmana · Accepted solution]

If I understand the example correctly, kv store lookup contains data set 1 and you want to only select rows where a row also exists in your second lookup. 

Seems to me that you already have kvstorefield_ fields 2-5 from the inputlookup on your first line, so the lookup on line 2 will simply validate if it contains kvstorefield_1. I believe your line 3 and 4 can just be replaced with 

| where isnotnull(lookupfield_1)

which will say 'if kvstorefield_1 is NOT in lookup, then ignore'.

[mistydennis]

I had a feeling I was overcomplicating it - thank you so much!

[bowesmana]

not sure what your second lookup is containing, but if you have multiple matches for the lookup field (2016/2017), then you will get multiple results in the same row, so when you re-lookup the data, it will create multiple values in the final table.

If you remove the second lookup, which appears unnecessary, then your result should look better.