Combine two separate indexes based on any permutation of the matching ID fields

[eyeglassescase]

Hi Splunk Community,

I need help to write a Splunk query to join two different indexes using any Splunk command that will satisfy the logic noted below.

Two separate ID fields in Index 2 must match two separate ID fields in Index 1 using any permutation of index 2’s three ID fields. Here is an outline of the logic below.

Combine index 1 record with index 2 record into a single record when any matching condition is satisfied below:

(ID_1_A=ID_2_A AND ID_1_B=ID_2_B)

OR

(ID_1_A=ID_2_A AND ID_1_B=ID_2_C)

OR

(ID_1_A=ID_2_B AND ID_1_B=ID_2_A)

OR

(ID_1_A=ID_2_B AND ID_1_B=ID_2_C)

OR

(ID_1_A=ID_2_C AND ID_1_B=ID_2_A)

OR

(ID_1_A=ID_2_C AND ID_1_B=ID_2_B)

Sample Data:

Index 1:

-----------------------

|ID_1_A |ID_1_B|

------------------------

|123       |345      |

------------------------

| 345      |123      |

------------------------

Index 2:

________________________

|ID_2_A   | ID_2_B   | ID_2_C|

----------------------------------------

|123         |345           |999       |

----------------------------------------

|123         |999           |345       |

----------------------------------------

|345         |123            |999      |

----------------------------------------

|999         |123           | 345       |

----------------------------------------

| 345       | 999           |123        |

----------------------------------------

Any help would be greatly appreciated.

Thanks.

[ITWhisperer]

| eval correlation1=coalesce(ID_1_A, ID_2_A)
| eval correlation2=coalesce(ID_1_B, ID_2_B)
| eventstats values(index1data) as index1data, values(index2data) as index2data by correlation1 correlation2
| eval correlation1=coalesce(ID_1_A, ID_2_A)
| eval correlation2=coalesce(ID_1_B, ID_2_C)
| eventstats values(index1data) as index1data, values(index2data) as index2data by correlation1 correlation2
| eval correlation1=coalesce(ID_1_A, ID_2_B)
| eval correlation2=coalesce(ID_1_B, ID_2_A)
| eventstats values(index1data) as index1data, values(index2data) as index2data by correlation1 correlation2
| eval correlation1=coalesce(ID_1_A, ID_2_B)
| eval correlation2=coalesce(ID_1_B, ID_2_C)
| eventstats values(index1data) as index1data, values(index2data) as index2data by correlation1 correlation2
| eval correlation1=coalesce(ID_1_A, ID_2_C)
| eval correlation2=coalesce(ID_1_B, ID_2_A)
| eventstats values(index1data) as index1data, values(index2data) as index2data by correlation1 correlation2
| eval correlation1=coalesce(ID_1_A, ID_2_C)
| eval correlation2=coalesce(ID_1_B, ID_2_B)
| eventstats values(index1data) as index1data, values(index2data) as index2data by correlation1 correlation2