topic Re: How do you perform a search based on lookup values? in Splunk Search

How do you perform a search based on lookup values?

ndaniel88 — Wed, 14 Nov 2018 22:48:41 GMT

Hello, I'm trying to do an outer join, but without actually using a join,

I have a lookup with names and based on these names, I need to perform the search and return all values on the lookup, even if they were not found on the search. For example:

lookup
name
a
b
c
d
e
f
g

search
name | number
a | 12:34:56
b | 09:87:76
e | 45:23:65

So, the result should be something like:

a | 12:34:56
b | 09:87:76
c | Not found
d | Not found
e | 45:23:65
f | Not found
g | Not found

This is my search so far which is working, but I dont want to use join, because it takes sooo long to complete because a big amount of events:

| inputlookup my_names.csv | fields Name | rename Name as names         
| join type=left names[search index=my_index sourcetype=my_st names=* | fields number names
| dedup names
| stats latest(number) by names

Thanks in advance.

Re: How do you perform a search based on lookup values?

harishalipaka — Thu, 15 Nov 2018 10:51:04 GMT

hi @ndaniel88

try like this you will get..
pls accept answer or upvote it if helped.:)

|makeresults |eval name="a"  |append [|makeresults |eval name="b"] |append [|makeresults |eval name="c"]|append [|makeresults |eval name="d"] |table name|append [|makeresults |eval name="a" ,number="12:34:56" |append [|makeresults |eval name="c" ,number="45:23:65"] |table name number] | reverse |dedup name |sort name |fillnull value="Not Found" number

Re: How do you perform a search based on lookup values?

ndaniel88 — Thu, 15 Nov 2018 15:48:59 GMT

I'm not sure this is what I'm looking for. I don't get how to transform this for use a lookup and a search.

Probably I didn't explain myself correctly. My main information comes from the lookup, even if there is match or not inside the search, I need to print all values from the lookup.

Re: How do you perform a search based on lookup values?

harishalipaka — Tue, 29 Sep 2020 22:04:09 GMT

hi @ndaniel88