Efficient way to join several time series commands when some of them return null values - is there any other way?

gilgi — Sun, 11 Jan 2026 08:29:33 GMT

Hi,

We have a situation in which we want to calculate (from logs) the percentage of errors from the total number of requests based on officename and apinames. It could happen that some of the offices/api's will not experience any errors during the timeframe, hence its timeseries command will return null. Doing a math operation on null values will return nulls and as we want to calculate the percentages over time, we need to be able to "create" an array, equal in size to the number of samples (based on timeframe and interval), of zeros in such case. As I don't know of any function (coalesce will return a single zero and not an array) to do this I've done the following:

timeseries { total = sum(apigee.apiname_requestcount, default:0)//, scalar: true ) }, by: { destinationoffice, apiname }, filter: { not matchesValue(destinationoffice, "*-t.*") and not matchesValue(destinationoffice, "*-d.*") and not matchesValue(destinationoffice, "*.dev.*") and not matchesValue(destinationoffice, "*test*") } | fields interval=record( timeframe.start = timeframe[start] + interval * iIndex(), totalrequests=coalesce(total[],0), destinationoffice, apiname ) | expand interval | fieldsFlatten interval | fieldsRemove interval // Add 4xx errors | join [ timeseries { errors = sum(apigee.apiname_requestcount, default:0)//, scalar: true ) }, by: { destinationoffice, apiname }, filter: { not matchesValue(destinationoffice, "*-t.*") and not matchesValue(destinationoffice, "*-d.*") and not matchesValue(destinationoffice, "*.dev.*") and not matchesValue(destinationoffice, "*test*") and responsecode >= 400 and responsecode <=499 } | fields interval1=record( timeframe.start = timeframe[start] + interval * iIndex(), errors=coalesce(errors[],0), destinationoffice, apiname ) | expand interval1 | fieldsFlatten interval1 | fieldsRemove interval1 ], on:{ left[interval.timeframe.start]==right[interval1.timeframe.start], left[interval.destinationoffice]==right[interval1.destinationoffice], left[interval.apiname]==right[interval1.apiname] }, prefix:"error4xx.", kind:leftOuter | fieldsAdd errors4xx = coalesce(error4xx.interval1.errors,0) //add errors 5xx | join [ timeseries { errors = sum(apigee.apiname_requestcount, default:0)//, scalar: true ) }, by: { destinationoffice, apiname }, filter: { not matchesValue(destinationoffice, "*-t.*") and not matchesValue(destinationoffice, "*-d.*") and not matchesValue(destinationoffice, "*.dev.*") and not matchesValue(destinationoffice, "*test*") and responsecode >= 500 and responsecode <=599 } | fields interval1=record( timeframe.start = timeframe[start] + interval * iIndex(), errors=coalesce(errors[],0), destinationoffice, apiname ) | expand interval1 | fieldsFlatten interval1 | fieldsRemove interval1 ], on:{ left[interval.timeframe.start]==right[interval1.timeframe.start], left[interval.destinationoffice]==right[interval1.destinationoffice], left[interval.apiname]==right[interval1.apiname] }, prefix:"error5xx.", kind:leftOuter | fieldsAdd errors5xx = coalesce(error5xx.interval1.errors,0) | fieldsAdd errorPct = coalesce((errors5xx+errors4xx)*100 / interval.totalrequests,0) // | summarize {errors=collectArray(errorPct), start=min(interval.timeframe.start), end=max(interval.timeframe.start) },by: {destinationoffice = interval.destinationoffice, apiname = interval.apiname} | makeTimeseries errorPct=avg(errorPct), by: {destinationoffice = interval.destinationoffice, apiname = interval.apiname}, time: interval.timeframe.start, interval:1m

Which is actually is based on the great tip from @marco_irmer found on https://community.dynatrace.com/t5/Dynatrace-tips/Pro-Tip-DQL-Query-Snippets-Collection/m-p/255109 . What I've done is seperate the arrays into single value lines and used the coalesce to do the join and then in the end recreate the timeseries.

To all of you - DQL experts - any better way to do this?

Re: Efficient way to join several time series commands when some of them return null values - is there any other way?

Julius_Loman — Mon, 12 Jan 2026 07:18:02 GMT

@gilgi, your query looks quite overengineered

From what I see and deduce (maybe I missed something), it's doable in a single timeseries command, something like this:

timeseries { total=sum(apigee.apiname_requestcount, default: 0) , errors_4xx=sum(apigee.apiname_requestcount, default:0, filter:{ responsecode >= 400 and responsecode <500 }) , errors_5xx=sum(apigee.apiname_requestcount, default:0, filter:{ http.response.status_code>=500 and http.response.status_code<600}) } , union:{ true } , by: { destinationoffice, apiname } , filter: { not matchesValue(destinationoffice, "*-t.*") and not matchesValue(destinationoffice, "*-d.*") and not matchesValue(destinationoffice, "*.dev.*") and not matchesValue(destinationoffice, "*test*") }

The key is:

filter can be applied to individual metric keys independently
union true is required if some of the datapoints can be absent, see docs

Hope this helps. A sample using the built-in service metric in the playground).

Re: Efficient way to join several time series commands when some of them return null values - is there any other way?

gilgi — Mon, 12 Jan 2026 06:57:43 GMT

@Julius_Loman and this is why it says you're a legend!! thanks.

topic Efficient way to join several time series commands when some of them return null values - is there any other way? in DQL

Efficient way to join several time series commands when some of them return null values - is there any other way?

Re: Efficient way to join several time series commands when some of them return null values - is there any other way?

Re: Efficient way to join several time series commands when some of them return null values - is there any other way?