How to join 2 searches from 2 different sourcetypes

[ookamidono]

I am encountering problems joining 2 querries that are getting values from 2 different sourcetypes.

I would like to get the CPU load and maximum (s well as a trend line) of all my hosts, filtered by HostGroup. But the HostGroups are only linked to hosts in sourcetype=entity, and the value of CPU loads are located in sourcetype=metrics.

I'll link the 2 queries and an explaination of the results, as for the combination I tried to make of the 2 queries.

index=my_index sourcetype="metrics" timeseriesId="host.cpu.user"
| eval _time = strptime(timestamp, "%Y-%m-%d %H:%M:%S")
| stats avg(value) as AvgCPU, max(value) as MaxCPU, values(unit) as Unit, sparkline(avg(value)) as Trend by hostName
| eval AvgCPU = round(AvgCPU,2), MaxCPU = round(MaxCPU, 2)]

This query returns the average and maximum CPU load per host, which is the result I'm trying to get to, but sorted by HostGroup.

And the only way for me to filter hosts by HostGroup is to use this query :

index=my_index sourcetype="entity" hostGroup.name="*"
| spath
| stats values(discoveredName) as hostName by hostGroup.name

So I tried combining the two queries using the mvexpand command :

index=my_index sourcetype="entity" hostGroup.name=$hostGroup_token$
| spath
| stats values(discoveredName) as hostName
| mvexpand hostName
| join

[ search index=my_index sourcetype="metrics" timeseriesId="host.cpu.user"
| eval _time = strptime(timestamp, "%Y-%m-%d %H:%M:%S")
| stats avg(value) as AvgCPU, max(value) as MaxCPU, values(unit) as Unit, sparkline(avg(value)) as Trend
| eval AvgCPU = round(AvgCPU,2), MaxCPU = round(MaxCPU, 2)]

The problem is that this particular query returns only 1 value that is the average  and maximum value of CPU load and max of all my hosts. 

Any idea on how to join the 2 queries so that it returns the CPU load and max filtered by HostGroup ?

[ITWhisperer · Accepted solution]

Try something like this

index=my_index sourcetype="metrics" timeseriesId="host.cpu.user"
| eval _time = strptime(timestamp, "%Y-%m-%d %H:%M:%S")
| join hostName

[ search index=my_index sourcetype="entity" hostGroup.name=$hostGroup_token$
| spath
| stats values(discoveredName) as hostName by hostGroup.name
| mvexpand hostName]
| stats avg(value) as AvgCPU, max(value) as MaxCPU, values(unit) as Unit, sparkline(avg(value)) as Trend by hostGroup.name
| eval AvgCPU = round(AvgCPU,2), MaxCPU = round(MaxCPU, 2)

[ookamidono]

It doesn't seem to work, Splunk finds no results for this query.

[ookamidono]

My bad, forgot to rename some values. Thanks a lot for the help !