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

[gilgi]

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? 

[Julius_Loman]

@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 necessary when some of the datapoints can be absent, see docs

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