Splunk Search

How to include all results from a joined lookup table, even if the search does not return all matching fields?

kobie
New Member

Case:
1. Lookup table (ex below)
name, day
example1,1
example2,2
2. Search that joins the lookup table and adds the day field
eventtype=Eventtype1 | lookup CustomLookupTable name | table name, code

All of this works fine and I get results shows the name and code.

My issue lies in the fact that sometimes I will have names that do not appear in the search, but I want them to display regardless. Example below:

CustomLookupTable

name, day
example1,1

example2,2

example3, 3

example4, 4

Search Results
example1
example2
example4

Table Output
example1 1
example2 2
example4 4

What I would like to see
example1 1
example2 2
example3 3
example4 4

I have simplified everything in the search to make it easier to write, but there are other fields being populated in the table from the search. As you can see in what i would like to see it is going to show all results from the lookup table regardless if it finds it in the table or not and then will add the fields I want from the search.

I hope all this makes sense. Thank you in advance.

Tags (2)
0 Karma

somesoni2
Revered Legend

Give this a try

eventtype=Eventtype1 | table name| append [| inputlookup CustomLookupTable | table name, code] | eval code=coalesce(code,"Not Available") | stats count by name code | table name code
0 Karma

kobie
New Member

Poking the thread.

0 Karma

davebrooking
Contributor

I don't really understand what you're trying to achieve, but the inputlookup command should give you the output you've described.

| inputlookup CustomLookupTable

However, I suspect that isn't really you're requirement. I would probably start by trying to use the inputlookup command to read in the lookup as the start of the search, and then use a join type=outer with your search of eventtype=Eventtype1 as the subsearch within the join. You may also find the fillnull command useful if you need to give values to fields which are null in the output.

0 Karma

kobie
New Member

Can you provide an example search? Thank you.

0 Karma

davebrooking
Contributor

Not really as I said I don't understand what you're trying to achieve. I need more details. What is your current search?

0 Karma

kobie
New Member

Current search is eventtype=Eventtype1 | lookup CustomLookupTable name | table name, code

Everything comes out fine with the lookup table adding the name field into the search. My problem is that I want all names from the lookup table to show up in the search regardless if the name showed up in the eventype or not.

0 Karma

davebrooking
Contributor

Given the limited information you've provided and I don't have a Splunk instance to hand - try something like

| inputlookup CustomLookupTable 
| join type=outer name [search eventtype=Eventtype1]
| ...
0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...