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