Help me convert span-based P90 downstream duration query to metric-based Timeseries DQL in milliseconds?

[S1012]

Hi Team,

I am trying to convert the following span-based DQL query into a metric-based Timeseries query using OpenPipeline metric extraction. The goal is to build a dashboard panel that shows the P90 duration of downstream calls, grouped by scope name.

Original span-based query

fetch spans
| filter service.name == "Service_Name"
| filter in(Service.domain, array($Service_Domain))
| filter in(Service.node, array($Service_Node))
| fieldsAdd scope_name = toString(otel.scope.name)
| summarize duration_p90 = percentile(duration, 90), by:{scope_name}
| sort duration_p90 desc

This query gives the expected P90 duration values in milliseconds.

I have created a custom metric through OpenPipeline: spans.service_request_durations

I am now trying to replicate the same logic using the metric-based Timeseries query below:

timeseries duration_p90_ns_series = percentile(
    spans.service_request_durations,
    percentile: 90,
    rollup: avg
  ),
  by: {otel.scope.name, Service.domain, Service.node}
| filter in(Service.domain, array($Service_Domain))
| filter in(Service.node, array($Service_Node))
| fieldsAdd scope_name = toString(otel.scope.name)
| fieldsAdd duration_p90_ms_value = arrayAvg(iCollectArray(toDouble(duration_p90_ns_series[]))) / 1000000.0
| filter isNotNull(duration_p90_ms_value)
| summarize duration_p90_ms = avg(duration_p90_ms_value), by: {scope_name}
| fieldsAdd duration_p90_ms = round(duration_p90_ms, decimals: 2)
| sort duration_p90_ms desc

However, when I run this Timeseries query, the value of duration_p90_ms_value is displayed in ps, whereas the original fetch spans query shows the duration correctly in ms.

Could someone please help me understand the correct way to convert this metric-based Timeseries result into milliseconds, while keeping the same logic as the original span-based query?

[t_pawlak]

Hi,

The issue is most likely caused by how the metric value is being handled in the timeseries query.

timeseries returns an array of values, so the conversion should be done after reducing the array to a scalar value. I would simplify the query and avoid converting each array element separately.

try domething like this:

timeseries {
    duration_p90 = percentile(spans.service_request_durations, 90)
},
by: { otel.scope.name, Service.domain, Service.node }
| filter in(Service.domain, array($Service_Domain))
| filter in(Service.node, array($Service_Node))
| fieldsAdd scope_name = toString(otel.scope.name)
| fieldsAdd duration_p90_ms_value = round(toDouble(arrayAvg(duration_p90)), decimals: 2)
| filter isNotNull(duration_p90_ms_value)
| summarize duration_p90_ms = avg(duration_p90_ms_value), by: { scope_name }
| sort duration_p90_ms desc

[S1012]

Hi @t_pawlak 
I am getting No records and a warning as below.

timeseries percentile function requires a rollup with the given metric key(s). Use the `rollup` parameter to calculate, for example, the 90th percentile of averages.

[t_pawlak]

Hi,

timeseries {
  duration_p90 = percentile(
    spans.service_request_durations,
    percentile: 90,
    rollup: avg
  )
},
by: { otel.scope.name, Service.domain, Service.node }
| filter in(Service.domain, array("aaa"))
| filter in(Service.node, array("aaa"))
| fieldsAdd scope_name = toString(otel.scope.name)
| fieldsAdd duration_p90_ms_value = arrayAvg(duration_p90)
| filter isNotNull(duration_p90_ms_value)
| summarize duration_p90_ms = avg(duration_p90_ms_value), by: { scope_name }
| fieldsAdd duration_p90_ms = round(toDouble(duration_p90_ms), decimals: 2)
| sort duration_p90_ms desc

The warning is expected because percentile() on a metric requires a rollup parameter.