topic Re: How to do an outer join on two tables with two fields? in Splunk Search

How to do an outer join on two tables with two fields?

apiprek2 — Tue, 28 Apr 2020 15:24:41 GMT

Hi,
I'm wondering if it's possible to do an outer/left join two tables on two fields. I have two indexes with the following data:

Index1:
col1 col2
123 abc
456 def

Index2:
col1 col2 col3
123 abc xyz

Desired results:
col1 col2 col3
123 abc xyz
456 def

Here's my search:

index=index1
|join type=outer col1, col2
[search index=index2
|fields col1, col2, col3]
|table col1, col2, col3

The results I get are inconsistent. It seems almost as if Splunk is going the outer join on the two columns independently, so I get more results than I need. If I remove the "type=outer", making it an inner join, I get the below results, so I know the join works for the inner:
col1 col2 col3
123 abc xyz

Thanks,
AP

Re: How to do an outer join on two tables with two fields?

adonio — Tue, 28 Apr 2020 15:47:51 GMT

did you try | append or | appendcols commands?

Re: How to do an outer join on two tables with two fields?

DalJeanis — Tue, 28 Apr 2020 15:51:59 GMT

From the fact that you're talking about outer joins, you are coming from an SQL background, so READ THIS FIRST:

https://answers.splunk.com/answers/561130/how-to-join-two-tables-where-the-key-is-named-diff.html

The general answer is called "splunk soup" or "splunk stew". You throw all the records and fields you want together in the pot and then stir until they come apart the way you want.

Here's generic pseudocode for that method...

  ((filters identifying events of type A) OR (filters identifying events of type B)) 
 | fields ... the list of every field that you need from either type A or B... 
 | eval joinfield = case(expression to detect type A, functions(to(transform(events, of, type, A))), 
                         expression to detect type B, functions(to(transform(events, of, type, A)))) 
 | stats values(field1) as field1 values(... as fieldN by joinfield

Then you can also look at this one. If you still have any questions, then please feel free to ask via a comment here.

https://answers.splunk.com/answers/816615/how-to-search-for-a-value-in-multiple-fields.html