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

[apiprek2]

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

[DalJeanis · Accepted solution]

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

[adonio]

did you try | append or | appendcols commands?