topic How to join two tables with default row if tables do not match? in Splunk Search

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

harshal_chakran — Tue, 09 Jun 2015 14:41:50 GMT

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

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

woodcock — Tue, 09 Jun 2015 17:32:06 GMT

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")

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

sideview — Tue, 09 Jun 2015 19:49:16 GMT

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