Splunk Search

How do you perform a search based on lookup values?

ndaniel88
Explorer

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.

0 Karma

harishalipaka
Motivator

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
Thanks
Harish
0 Karma

ndaniel88
Explorer

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.

0 Karma

harishalipaka
Motivator

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

Thanks
Harish
0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...