Using Regex on Custom Metric entity object (metric.series ) Via DQL to reduce complex queries for throughput,error_rate etc.

[NitinOjha003]

We are pushing out metrics through Otel into both Newrelic and Dynatrace. We are in process of migrating out our application metric data from newrelic to dynatrace.
In NRQL, we use these queries:
1. SELECT sum(`sling.cmw.b-cmwnext-player.PlayerHandler%.failure_totals`) / sum(`sling.cmw.b-cmwnext-player.PlayerHandler%.request_totals`) * 100 as 'Error Rate' FROM Metric WHERE entity.name LIKE '%-cmwnext-player' FACET cluster_name SINCE 1 hour ago[For error_rate]

2. 

SELECT count(*) FROM Metric WHERE pod_name LIKE 'cmwnext-profiles%' AND metricName LIKE 'sling.cmw.b-cmwnext-profiles%.request_totals'

AND cluster_name LIKE 'b-a%' FACET cluster_name,metricName SINCE 1 hour ago LIMIT 200 [For throughput Handler/route wise across cluster]



We are able to use regex with string based handler routes(sling.cmw.b-cmwnext-player.PlayerHandlerScreen.request_totals). We are unable to use regex properly here in DQL as we are able to do in NRQL.Please suggest some solution to avoid these complex queries for throughput, error_rate etc.


For similar queries in Dynatrace , we are using these complex and bulk queries  and at last doing aggregation to get throughput and error_rate

IN DQL, We are using 
1. 
timeseries {
request_screen_totals=sum(`sling.cmw.b-cmwnext-player.PlayerHandlerScreen.request_totals`, default: 0),
request_content_totals=sum(`sling.cmw.b-cmwnext-player.PlayerHandlerContent.request_totals`, default: 0),
request_bar_totals=sum(`sling.cmw.b-cmwnext-player.PlayerHandlerBar.request_totals`, default: 0),
request_extended_totals=sum(`sling.cmw.b-cmwnext-player.PlayerHandlerExtended.request_totals`, default: 0),
request_episodes_totals=sum(`sling.cmw.b-cmwnext-player.PlayerHandlerEpisodesRibbon.request_totals`, default: 0),
request_nextplay_totals=sum(`sling.cmw.b-cmwnext-player.PlayerHandlerNextPlayInfo.request_totals`, default: 0)
}, union: true, by: {cluster_name}, filter: { container_name == "cmwnext-player" }, interval:1m
| fieldsAdd result = request_screen_totals[] + request_content_totals[] + request_bar_totals[] + request_extended_totals[] + request_episodes_totals[] + request_nextplay_totals[]
| fieldsAdd throughput = arraySum(result)
| fieldsRemove request_screen_totals, request_content_totals, request_bar_totals, request_extended_totals, request_episodes_totals, request_nextplay_totals
, result [for throughput]

2. timeseries {
request_screen_totals=sum(`sling.cmw.b-cmwnext-player.PlayerHandlerScreen.request_totals`, default: 0),
request_content_totals=sum(`sling.cmw.b-cmwnext-player.PlayerHandlerContent.request_totals`, default: 0),
request_bar_totals=sum(`sling.cmw.b-cmwnext-player.PlayerHandlerBar.request_totals`, default: 0),
request_extended_totals=sum(`sling.cmw.b-cmwnext-player.PlayerHandlerExtended.request_totals`, default: 0),
request_episodes_totals=sum(`sling.cmw.b-cmwnext-player.PlayerHandlerEpisodesRibbon.request_totals`, default: 0),
request_nextplay_totals=sum(`sling.cmw.b-cmwnext-player.PlayerHandlerNextPlayInfo.request_totals`, default: 0)
},union: true, by: {cluster_name}, filter: { container_name == "cmwnext-player" AND `sling.service.cmwnext-player.player.requesttype` != "ota_channel" }
| fieldsAdd match = "match"
| join [
timeseries {
request_screen_failure=sum(`sling.cmw.b-cmwnext-player.PlayerHandlerScreen.failure_totals`, default: 0),
request_content_failure=sum(`sling.cmw.b-cmwnext-player.PlayerHandlerContent.failure_totals`, default: 0),
request_bar_failure=sum(`sling.cmw.b-cmwnext-player.PlayerHandlerBar.failure_totals`, default: 0),
request_extended_failure=sum(`sling.cmw.b-cmwnext-player.PlayerHandlerExtended.failure_totals`, default: 0),
request_episodes_failure=sum(`sling.cmw.b-cmwnext-player.PlayerHandlerEpisodesRibbon.failure_totals`, default: 0),
request_nextplay_failure=sum(`sling.cmw.b-cmwnext-player.PlayerHandlerNextPlayInfo.failure_totals`, default: 0)
}, union: true, by: {cluster_name},filter: { container_name == "cmwnext-player" AND `sling.service.cmwnext-player.player.requesttype` != "ota_channel" }
| fieldsAdd match = "match"
], on: {match}
| fieldsAdd result = 100 * (
right.request_screen_failure[]
+ right.request_content_failure[]
+ right.request_bar_failure[]
+ right.request_extended_failure[]
+ right.request_episodes_failure[]
+ right.request_nextplay_failure[]
) / (
request_screen_totals[]
+ request_content_totals[]
+ request_bar_totals[]
+ request_extended_totals[]
+ request_episodes_totals[]
+ request_nextplay_totals[]
)
| fields timeframe, interval, result. [for error_rate]

Solution needed for using regex effectively for metric data in DQL?