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?

[MaciejNeumann]

Hello @NitinOjha003,

Here are some threads around the topic of using pattern matching in DQL/DPL:
DQL Regex  
Regex in DQL
Hope that they'll help you solve your use case!  

[marco_irmer]

If I am reading the original post correctly, the core of the issue is that NRQL allows the user to query multiple metrics by specifying a regex pattern that will match multiple metrics, whereas the DQL timeseries command requires the user to explicitly specify each individual metric to be included, which is then followed up by more DQL to combine all the data into aggregated values. 

I don't think DQL supports this sort of thing currently. Perhaps a product idea is in order 🙂