Combine 2 indexes with join based on result of a substring search

[diliptmonson]

I have 2 indexes:
First index:
index= abc with field1 having values like "\A,\B,\C" and "\A,\D" and so on
Second index:
index=def with field 2 having values like A, F and so on.

Now I have to join the 2 indexes using field1 and field2 such that
1.Search field1 of index abc for the value of field2 in index def (For instance a match happens when A in field2 is same as \A in field1)
2. When a match happens i need to combine these 2 rows using a join

Can anyone please provide me guidance on how to approach this

[somesoni2]

Not sure how efficient it'll be, but try something like this (this is assuming you've field4 single valued field (just one value) and field1 multivalued field OR contains multiple values.

index=abc OR index=def   | eval commonfield=coalesce(field1,field4) | makemv commonfield delim="," | mvexpand commonfield | stats list(*) as * by commonfield | where isnotnull(field4) | eval temp=mvzip(field4,field5,"#") | mvexpand temp | rex field=temp "(?<field4>.*)#(?<field5>.*)"  | fields - temp commonfield

See this run anywhere sample with your example data.(first 3 rows are just to generate data)

| gentimes start=-1 | eval temp="A,B,C Def Dpx;D,E Ghi Abc;F Ghi " | table temp| makemv temp delim=";" | mvexpand temp | rex field=temp "(?<field1>.*)\s(?<field2>.*)\s(?<field3>.*)" | append [| gentimes start=-1 | eval temp="A 123;D 245;A 343" | table temp| makemv temp delim=";" | mvexpand temp | rex field=temp "(?<field4>.*)\s(?<field5>.*)" ] | fields - temp 
| eval commonfield=coalesce(field1,field4) | makemv commonfield delim="," | mvexpand commonfield | stats list(*) as * by commonfield | where isnotnull(field4) | eval temp=mvzip(field4,field5,"#") | mvexpand temp | rex field=temp "(?<field4>.*)#(?<field5>.*)"  | fields - temp commonfield

[somesoni2]

What all fields you want to join? Could you provide the fields from both indexes that you need in final output?

[diliptmonson]

Hi Somesoni2,
These are the 2 indexes I need to combine:

index= abc;
field1 - "\A,\B,\C";"\D,\E";"\F"
field2 - Def, Ghj, Klm
field3- Dpx,Abc,

index=def;
field4= "A","D","A"
field5=123,245, 571

Now I need to combine both these indexes together such that the result obtained is
Row 1: "\A,\B,\C",Def,Dpx,"A",123
Row2: "\D,\E";"\F",Ghj,Abc,
Row3:"\A,\B,\C",Def,Dpx,"A",571

Thanks a lot for your help... 😉