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?
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'.
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'.
I had a feeling I was overcomplicating it - thank you so much!
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.