Match nested span data based on time in DQL

[SimGiants92]

Hello, wondering if this is possible using DQL i gave it a solid try but cannot do it.
I have the opentelemetry plugin installed on jenkins sending data to dynatrace.
I would like to use the trace/span data to see all the pipelines that a given computer was used on.
The problem is only the first span contains the pipeline name and url (ci.pipeline.id and ci.pipeline.url)
And later in the trace some spans contain the computer name (jenkins.computer.name). So far no problem. I have the query below that uses a lookup to match the data.
There is an exception though , if a pipeline is called by another pipeline they are all part of the same trace. In that case the query below can return wrong results.
I need a way to find the span that contains the computer name that happened the closest in time after the span that contains the pipeline name. Wondering if a DQL magician knows how to do this? 

This is what i already have:

fetch spans
| filter jenkins.instance.name == $Controller
| filter isNotNull(ci.pipeline.id)
| lookup [
    fetch spans
    | filter jenkins.computer.name == $Computer
    | filter jenkins.instance.name == $Controller
    | fields trace.id,span.name,jenkins.computer.name
  ], sourceField:trace.id, lookupField:trace.id
| filter isNotNull(lookup.trace.id)
| dedup trace.id
| sort start_time asc
| fields start_time,duration,lookup.jenkins.computer.name,ci.pipeline.id,ci.pipeline.run.url

[t_pawlak]

Hi,
IMO Yes, it’s possible, but lookup can’t reliably pick the “closest in time” span when multiple pipeline spans exist.
Try use join, compute the time delta, keep only positive deltas.
here example:

fetch spans
| filter jenkins.instance.name == "jenkins-prod"
| filter jenkins.computer.name == "agent-01"
| fields trace.id,
         computer_start = start_time,
         computer_span = span.name,
         jenkins.computer.name
| join [
    fetch spans
    | filter jenkins.instance.name == "jenkins-prod"
    | filter isNotNull(ci.pipeline.id)
    | fields trace.id,
             pipeline_start = start_time,
             ci.pipeline.id,
             ci.pipeline.run.url
  ],
  on: { left[trace.id] == right[trace.id] }
| fieldsAdd delta = computer_start - right.pipeline_start
| filter delta >= 0
| sort delta asc
| dedup trace.id, computer_start
| fields computer_start,
         jenkins.computer.name,
         right.ci.pipeline.id,
         right.ci.pipeline.run.url,
         delta
| sort computer_start asc