How do you perform a search based on lookup values?

[ndaniel88]

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.

[harishalipaka]

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

[ndaniel88]

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.

[harishalipaka]

hi @ndaniel88

try liek this
| inputlookup my_names.csv | fields Name | rename Name as names
|append [search index=my_index sourcetype=my_st names=* | fields number names]
| reverse |dedup name |sort name |fillnull value="Not Found" number