DQL for cumulative percentage of time

[elenaperez]

Hi team!

I have the following query:

fetch spans
| filter matchesValue(dt.system.bucket, "obs-educamosclm_spans")
| fieldsAdd duration = toDuration(end_time-start_time), request.is_failed
| fieldsAdd failed = if(request.is_failed, 1, else: 0), dt.kubernetes.workload.name
| fieldsAdd span.timing.cpu, http.response.status_code
| fieldsAdd errors5xx = if(http.response.status_code>=500 and http.response.status_code <600, 1, else: 0)
| fieldsAdd errors4xx = if(http.response.status_code>=400 and http.response.status_code <500, 1, else: 0)
| summarize requestCount = count(), sumTime = sum(duration), meanTime = avg(duration), maxTime = max(duration), standardDeviation = toDuration(stddev(toLong(duration))), meanCPUTime = avg(span.timing.cpu), failed = sum(failed), errors5xx = sum(errors5xx), errors4xx = sum(errors4xx), failureRate = (sum(failed)/count())*100, by:{dt.kubernetes.workload.name, endpoint.name, code.function}
| sort dt.kubernetes.workload.name desc

And I would like to add a column with the percentage of sumTime per workfload. Basically, adding the sumTime for the whole workload and calculate the percentage of time that each endpoint takes divided by the total time per workload. However, I want to keep the rest of the fields as well. Is this possible to do?
Thanks in advance!

[MaximilianoML · Accepted solution]

Hello @elenaperez ,

You can achieve this by using a lookup to bring in the total sumTime per workload, and then calculate the percentage for each endpoint from that total.

This may not be the most optimized approach, since it reads spans twice, but it worked fine from my side and keeps all the original fields while adding the percentage column.

fetch spans
| filter matchesValue(dt.system.bucket, "obs-educamosclm_spans")
| fieldsAdd duration = toDuration(end_time - start_time)
| fieldsAdd workloadName = dt.kubernetes.workload.name
| fieldsAdd failed = if(request.is_failed, 1, else: 0)
| fieldsAdd errors5xx = if(http.response.status_code >= 500 and http.response.status_code < 600, 1, else: 0)
| fieldsAdd errors4xx = if(http.response.status_code >= 400 and http.response.status_code < 500, 1, else: 0)
| summarize
    requestCount = count(),
    sumTime = sum(duration),
    meanTime = avg(duration),
    maxTime = max(duration),
    standardDeviation = toDuration(stddev(toLong(duration))),
    meanCPUTime = avg(span.timing.cpu),
    failed = sum(failed),
    errors5xx = sum(errors5xx),
    errors4xx = sum(errors4xx),
    failureRate = (sum(failed) / count()) * 100,
    by: { workloadName, endpoint.name, code.function }
| lookup [
    fetch spans
    | filter matchesValue(dt.system.bucket, "obs-educamosclm_spans")
    | fieldsAdd duration = toDuration(end_time - start_time)
    | fieldsAdd workloadName = dt.kubernetes.workload.name
    | summarize workloadSumTime = sum(duration), by: { workloadName }
  ],
  sourceField: workloadName,
  lookupField: workloadName,
  fields: { workloadSumTime }
| fieldsAdd sumTimePctPerWorkload = if(
    isNull(workloadSumTime) or toLong(workloadSumTime) == 0,
    null,
    else: round((toLong(sumTime) * 100.0) / toLong(workloadSumTime), decimals: 2)
  )
| fieldsRemove workloadSumTime
| sort workloadName desc

I hope it helped 😀

[elenaperez]

That worked! Thank you so much 😀

[MaximilianoML]

Nice to know, it was my pleasure to help 😊