Splunk Search

How to join two tables with default row if tables do not match?

harshal_chakran
Builder

Hi,

i have a table whose result is as below:

parameter value result
P1          V1    R1
P2          V2    R2
P3          V3    R3
P4          V4    R4
P5          V5    R5

And a lookup file as below:

parameter value result color1 color2
P1          V1    R1     C1     C2
def        def   def     C3     C4

How can I join the two table? If the parameter value result doesn't match for both, then it should take the "def" row value.

Planning to get the result as below:

parameter value result color1 color2
P1          V1    R1     C1     C2
P2          V2    R2     C3     C4
P3          V3    R3     C3     C4
P4          V4    R4     C3     C4
P5          V5    R5     C3     C4

I have used the join , but doesnt displays the result as I expected.
Please Help...!!

Tags (4)
1 Solution

sideview
SplunkTrust
SplunkTrust

Let's say your lookup is named "myLookup", then here's what you want. The rows that are a match for all three values, (parameter, value and result) will get the corresponding color1 and color2 values from the lookup. The rows that are not a match will end up with whatever color1 and color2 values are listed in the row that has the value "def" for all three fields.

<your search terms> | lookup myLookup parameter value result OUTPUT color1 color2 |fillnull defaultParameter defaultValue defaultResult value="def" | lookup myLookup parameter as defaultParameter value as defaultValue result as defaultResult OUTPUT defaultColor1 defaultColor2 | eval color1=coalesce(color1,defaultColor1) | eval color2=coalesce(color2,defaultColor2)

And I don't normally advise using append or join, but in cases where the subsearch is extremely efficient and returns only a couple rows, their drawbacks don't really come into play.

Since the above example runs the lookup twice, and with a join command all you're joining in is an inputlookup search yielding only a single row, the join command way below is a viable alternative.

<your search terms> | lookup myLookup parameter value result OUTPUT color1 color2 | eval foo=1 | join foo [| inputlookup myLookup | search parameter="def" value="def" result="def" | rename color1 as defaultColor1 defaultColor2 | eval foo=1 | table foo defaultColor1 defaultColor2] | fields - foo | eval color1=coalesce(color1,defaultColor1) | eval color2=coalesce(color2,defaultColor2)

Or instead of the eval+coalesce at the end you can avail yourself of the join command's overwrite argument.

<your search terms> | lookup myLookup parameter value result OUTPUT color1 color2 | eval foo=1 | join overwrite=f foo [| inputlookup myLookup | search parameter="def" value="def" result="def" | eval foo=1 | table foo color1 color2] | fields - foo 

View solution in original post

sideview
SplunkTrust
SplunkTrust

Let's say your lookup is named "myLookup", then here's what you want. The rows that are a match for all three values, (parameter, value and result) will get the corresponding color1 and color2 values from the lookup. The rows that are not a match will end up with whatever color1 and color2 values are listed in the row that has the value "def" for all three fields.

<your search terms> | lookup myLookup parameter value result OUTPUT color1 color2 |fillnull defaultParameter defaultValue defaultResult value="def" | lookup myLookup parameter as defaultParameter value as defaultValue result as defaultResult OUTPUT defaultColor1 defaultColor2 | eval color1=coalesce(color1,defaultColor1) | eval color2=coalesce(color2,defaultColor2)

And I don't normally advise using append or join, but in cases where the subsearch is extremely efficient and returns only a couple rows, their drawbacks don't really come into play.

Since the above example runs the lookup twice, and with a join command all you're joining in is an inputlookup search yielding only a single row, the join command way below is a viable alternative.

<your search terms> | lookup myLookup parameter value result OUTPUT color1 color2 | eval foo=1 | join foo [| inputlookup myLookup | search parameter="def" value="def" result="def" | rename color1 as defaultColor1 defaultColor2 | eval foo=1 | table foo defaultColor1 defaultColor2] | fields - foo | eval color1=coalesce(color1,defaultColor1) | eval color2=coalesce(color2,defaultColor2)

Or instead of the eval+coalesce at the end you can avail yourself of the join command's overwrite argument.

<your search terms> | lookup myLookup parameter value result OUTPUT color1 color2 | eval foo=1 | join overwrite=f foo [| inputlookup myLookup | search parameter="def" value="def" result="def" | eval foo=1 | table foo color1 color2] | fields - foo 

woodcock
Esteemed Legend

This will do it:

... lookup <yourLookupName> parameter value result OUTPUT color1 AS color1lookup color2 AS color2lookup | eval color1=coalesce(color1lookup,"C3") | eval color2=coalesce(color2lookup,"C4")
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...