I have
sourcetype = sourcetype1 with field 1 , field 2 , field 3 , field 4 , part1 , key1
sourcetype = sourcetype2 with field 1b , field2b , field3b , field4b , part2 , key2
The pseudo logic at a high level is
Join fields in sourcetye1 and sourcetype2 if part1 = part2
Else join fields in sourcetype1 and sourcetype2 if part1 NOT in part2 with key1 = key2
key – is a concatenation of other fields within the sourcetype (i.e eval key=field4+field1+field5+field6 )
index=poc sourcetype=sourcetype1 | rename "Field 1" as Afield1 | rename "Field 2" AS Afield2, "Field 3" AS Apart1, "field 4" AS Afield4, “Field 5” AS Afield5, “Field 6” AS Afield6
| eval Akey=Afield2+Afield5+Afield6+Afield1
| stats max(_time) as latestA by Afield2, Apart1, Apart4, Apart5, Apart6, Afield1, Akey
| where status=30 AND Afield2=* AND Apart1* AND Afield4=*
| rename Apart1 as part
| join part type=outer [ search index=poc sourcetype=sourcetype2 | rename "Field 1" as Bfield1 | rename "Field 2" AS Bfield2, "Field 3" AS Bpart1, "field 4" AS Bfield4, “Field 5” AS Bfield5, “Field 6” AS Bfield6
| eval Bkey=Bfield2+Bfield5+Bfield6+Bfield1
| where B_appstatus = complete | stats max(_time) as latestA by Bfield2, Bpart1, Bpart4, Bpart5, Bpart6, Bfield1, Bkey
| rename Bpart1 as part ]
***| search NOT Bpart1=*
| rename Akey as key
| join key type=inner [search index=poc sourcetype=sourcetype2 | rename "Field 1" as Bfield1 | rename "Field 2" AS Bfield2, "Field 3" AS Bpart1, "field 4" AS Bfield4, “Field 5” AS Bfield5, “Field 6” AS Bfield6
| eval Bkey=Bfield2+Bfield5+Bfield6+Bfield1
| where B_appstatus = complete | stats max(_time) as latestA by Bfield2, Bpart1, Bpart4, Bpart5, Bpart6, Bfield1, Bkey
| rename Bkey as key*** ]
| table Field, part, Afield1, Bfield1
where the table should be a merge data of the first join (with common part fields) and 2nd join (with common key fields)
... View more