How to join two timeseries in a single DQL to map/alias an GUID to Readable Name

[Raul_rrr]

I have created a dashboard to monitor multiple MuleSoft API metrics ( Response Time, Endpoint Usage, Request Count). Each tile contains respective DQL query that includes the dimension `request_attribute.Client-Id` in GUID format.

For example, when a  API request occurs, Dynatrace automatically captures the API name, endpoint, and also records the Client ID in `request_attribute.Client-Id`. For dashboard filtering and data splitting, we require three key details: API name, endpoint, and client ID (The API request header does not have Client Name, so we capture ID in request attribute).

timeseries {
value = sum(service.muleapi.response.count)
},
by: {dt.entity.service, endpoint.name, `request_attribute.Client-Id`},

Separately, I have a custom metric (`custom.api.metric`) ingested via the Dynatrace API, which contains the mapping of `clientid` (GUID) to `clientname` (two columns).

timeseries testmetric = sum(custom.api.metric), by: { clientid, clientname }

Within a single DQL query for a tile, I want to:

1. Fetch the main metric (e.g., API Response Time) as a timeseries.
2. Fetch the `custom.testmetric` as a timeseries.
3. Match `request_attribute.Client-Id` from the main metric with `clientid` from `custom.api.metric`.
4. Dynamically alias or replace the GUID with the corresponding `clientname`.

Is there a way to join these two timeseries results within a single query, so I can achieve this aliasing/mapping without hardcoding as a lookup data record[] table?

[krzysztof_hoja]

This is how to combine results of these 2 queries (from 2nd I take only clientname):

timeseries { value = sum(service.muleapi.response.count)},
by: {dt.entity.service, endpoint.name, `request_attribute.Client-Id`}
| lookup [
  timeseries testmetric = sum(custom.api.metric), by: { clientid, clientname }
], sourceField:`request_attribute.Client-Id`, lookupField:clientid, fields:{clientname}

Just 2 additional comment from my side:

• I hope that there is limited set of client IDs. Grail is quite capable, but everything has limits and crossing them may have some consequences (e.g. too much data points may result in inability of long term analysis). You can check details here: https://docs.dynatrace.com/docs/analyze-explore-automate/metrics/limits 
• Query will provide aliases/mapping as long as in timeframe of the inner query mapping was provided. As metrics are not kept forever you probably will need to re-ingest mappings regularly. There is new functionality released recently to keep such mappings, which may be more optimal for this use-case: https://docs.dynatrace.com/docs/discover-dynatrace/platform/grail/lookup-data