How to populate fields from two indexes that share one field

[maryamchar]

Hello,
I asked this question yesterday but didn't get the right solution. I have two indexes with different fields and only share one common field, I want to have a table where it display some fields form both indexes. So far, it displays fields from one index only, not sure what I'm doing wrong. Here is my attempt.

Note: field 2 and field 3 from index=1 , field 4 and field 5 from index=2 , common field is name

index=1 OR index=2
|eval name=coalescce(name1,name2)
|stats values(field2) as fields2 values(field3) as field3 values(field4) as field4 values(field5) as field5 by name

thank you in advance!!!

[PowerPacked · Accepted solution]

Hi

You should use join command to correlate data from one index to data in other index.

index=1 | stats c by name1, field2, field3 | rename name1 as name2| join name2 [| search index=2 | stats c by name2, field4, field5]

other example : index=_internal | stats c by host sourcetype | join type=left host [ | search index=_audit | stats c by host source]

Thanks

[maryamchar]

Thank you!!! It worked!!

[adonio]

not sure why or what you need to coalesce if the split by field is the same ...
index = a or index = b name=* |stats values(field2) as fields2 values(field3) as field3 values(field4) as field4 values(field5) as field5 by name

[maryamchar]

I need coalesce because it's the same field but named differently in each index. however, they both have same data. The query you provided didn't work