Search from 2 different sourcetypes

[ookamidono]

How can I join two fields from different sourcetypes that don't share the same name ?

The content of the two fields is strictly the same though. For more details, here are the two queries I would like to "join" :

index=dynatrace* sourcetype="dynatrace:entity" hostGroup.meId="HOST_GROUP-*"
| spath
| stats values(discoveredName) as HostName by hostGroup.name

In this one, I'm searching the entities to list all my host names by host group.

index=dynatrace_hp sourcetype=dynatrace:metrics timeseriesId="com.dynatrace.builtin:host.mem.used"
| stats avg(value) as avgCPU, values(unit) as Unit by hostName

In this one, I'm searching the metrics to list all cpu loads by host name.

I need to do it like this as I don't have access to the host group from the data in the metrics sourcetype.

Already tried to make some joined queries but I can't find a working solution. Any idea ?

[ookamidono]

Thank you for your help, the solution works perfectly fine.

[ITWhisperer · Accepted solution]

mvexpand the first search by HostName, so you have host name and group name events. You can then join this by host name to your metrics search