How to write multiple fetch spans query

[rishirajk]

Hi ,

I wish to know how to write multiple fetch spans queries plus i want to use them to perform basic arithmetic operations .
I have tried using join statement however I am missing something .  

fetch spans, scanLimitGBytes: -1
| filter request.is_root_span == true AND isNotNull(endpoint.name)
| filter (matchesValue(`k8s.workload.name`, "services")
OR matchesValue(`dt.kubernetes.workload.name`, "services"))
| filter matchesValue(`endpoint.name`, "ID")
| summarize generated_count = toLong(count())
| fieldsAdd join_key = 1
| join [
fetch spans, scanLimitGBytes: -1
| filter request.is_root_span == true AND isNotNull(endpoint.name)
| filter (matchesValue(`k8s.workload.name`, "services")
OR matchesValue(`dt.kubernetes.workload.name`, "services"))
| filter matchesValue(`endpoint.name`, "Verify")
and (`http.response.status_code` == 202 OR toLong(http.status_code) == 202
OR `http.response.status_code` == 200 OR toLong(http.status_code) == 200)
| summarize verified_count = toLong(count())
| fieldsAdd join_key = 1
],
on: { left[join_key] == right[join_key] },
fields: { generated_count, verified_count }
| join [
fetch spans, scanLimitGBytes: -1
| filter request.is_root_span == true AND isNotNull(endpoint.name)
| filter (matchesValue(`k8s.workload.name`, "services")
OR matchesValue(`dt.kubernetes.workload.name`, "services"))
| filter matchesValue(`endpoint.name`, "Verify")
and (`http.response.status_code` == 409 OR toLong(http.status_code) == 409)
| summarize failed_count = toLong(count())
| fieldsAdd join_key = 1
],
on: { left[join_key] == right[join_key] },
fields: { generated_count, verified_count, failed_count }
| fieldsAdd unused_otp = generated_count - (verified_count + failed_count)

[t_pawlak · Accepted solution]

Hi! You don’t need multiple fetch + join queries for this. It’s much faster and simpler to do a single pass over spans and compute conditional counts directly.
Try this:

fetch spans, scanLimitGBytes: -1
| filter request.is_root_span == true
| filter isNotNull(endpoint.name)
| filter matchesValue(k8s.workload.name, "services")
      OR matchesValue(dt.kubernetes.workload.name, "services")
| fieldsAdd status = toLong(coalesce(http.response.status_code, http.status_code))
| summarize
  generated_count = toLong(countIf(matchesValue(endpoint.name, "ID"))),
  verified_count  = toLong(countIf(matchesValue(endpoint.name, "Verify") AND (status == 200 OR status == 202))),
  failed_count    = toLong(countIf(matchesValue(endpoint.name, "Verify") AND status == 409))
| fieldsAdd unused_otp = generated_count - (verified_count + failed_count)

[rishirajk]

Thanks It works 🙂