topic Re: How to limit the results of one lookup with a second lookup? in Splunk Search

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

mistydennis — Mon, 22 Aug 2022 20:52:41 GMT

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

centos

linux

web

workstation1

workstation2

2016

2017

apache

tomcat

http

server1

server2

server3

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?

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

bowesmana — Mon, 22 Aug 2022 23:18:34 GMT

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'.

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

bowesmana — Mon, 22 Aug 2022 23:25:58 GMT

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.

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

mistydennis — Tue, 23 Aug 2022 14:04:40 GMT

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