topic Re: How to include all results from a joined lookup table, even if the search does not return all matching fields? in Splunk Search

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

kobie — Wed, 12 Nov 2014 16:31:47 GMT

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.

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

kobie — Fri, 21 Nov 2014 17:40:48 GMT

Poking the thread.

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

davebrooking — Fri, 21 Nov 2014 18:44:38 GMT

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.

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

kobie — Fri, 21 Nov 2014 19:15:01 GMT

Can you provide an example search? Thank you.

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

davebrooking — Fri, 21 Nov 2014 19:25:44 GMT

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

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

kobie — Fri, 21 Nov 2014 19:31:17 GMT

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.

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

davebrooking — Fri, 21 Nov 2014 19:42:54 GMT

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

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

somesoni2 — Fri, 21 Nov 2014 20:47:39 GMT

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