Using a first query on second query data

[Ellery]

Hello people.

    i have big issue, im playing with spans, and y need obtains a metric.

my query

fetch spans
| filter isNotNull( http.request.header.host)
//| fields endpoint.name, http.request.header.host, http.request.method, trace.id
and endpoint.name == "/api/inicio/v1/empresa/estadisticasV2" 
| fieldsAdd http.response.status_code, response_time = end_time - start_time
| summarize percentile(response_time, 90), promedio = avg(response_time), maximo = max(response_time), count(),  by:{endpoint.name}

| append 
  [ fetch spans 
   | filter isNotNull( http.request.header.host) and endpoint.name == "/api/inicio/v1/empresa/estadisticasV2" 
   | fieldsAdd http.response.status_code, response_time = end_time - start_time
   | summarize countIf(response_time >= promedio), countIf(response_time >= maximo) ,by:{endpoint.name} 
  ]

i want get the record count over promedio and count the max.

but my result was

thanks for your help !!

[peter_zahrer · Accepted solution]

Hi,

first, i hope i understand your requirement / intended result correctly. A couple of observations upfront:

• you do not need to calculate the response time of a span manually, it is done by Dynatrace and available via the duration field, see example.
• Your query can never show a number of spans with a response time larger than the maximum as you are using the same timeframe for both queries. So the only possibility is that there are spans with a duration equal to the max, but never larger as the max was already found by the first query. Please clarify and provide more detail on your use case.

Here is my interpretation & example:

There is a certain reference of "performance" for certain requests. This reference can be defined by e.g. the observed response time during a certain timeframe (last business week in my case, or e.g. yesterday). Then, the current performance is compared to that reference and you want to see if your services are performing better or worse than before.

Thus i compiled this example:

fetch spans, from:@w1 //this business week, from Monday to now
| fields start_time, end_time, duration, endpoint.name
| filter endpoint.name == "/api/products"
| join [

        fetch spans, from:-1w@w1, to:@w5 //last business week
        | fields start_time, end_time, duration, endpoint.name
        | filter endpoint.name == "/api/products"
        | summarize promedio=avg(duration), maximo=max(duration), by:endpoint.name

], on:endpoint.name, prefix:"reference."
| summarize mas_que_promedio = countIf(duration >= reference.promedio), mas_que_maximo = countIf(duration >= reference.maximo)

you can copy paste this example and try it on our Playground environment based on real data.

Ultimately in case you need to be alerted in case the response time of your requests exceeds your specified thresholds consider using Dynatrace anomaly detection.

[Ellery]

Work's Thank's a lot !!!😁