I need your help. I have a query in Dynatrace (DQL) where I have events, and these events have a start date and an end date. I can have multiple events within a time range. These events are retrieved from dt.davis.problems, and now I would like to count sub-events using a join or lookup, but specifically from bizevents, and only those that occurred between the start and end dates of each event.
For example:
- Event 1: Start Date : End Date → Here, it should search for how many events in bizevents occurred between the Start Date and End Date.
- Event 2: Start Date : End Date → Here, it should search for how many events in bizevents occurred between the Start Date and End Date.
- Event 3: Start Date : End Date → Here, it should search for how many events in bizevents occurred between the Start Date and End Date.
The start and end dates are distinct and individual for each event, and all these events are within the general timeframe.
There is also no event ID to match the davis events with the bizevents.
I had thought of something like this, but it doesn’t allow me to access the start and end date information from the main query before performing the lookup.
fetch dt.davis.problems
| expand entity_tags
| filter dt.davis.is_duplicate == false
and (
event.name == "xxxx"
or (contains(entity_tags, "xxxx") and event.name == "Http monitor global outage")
)
| fields indisponibilidad = (resolved_problem_duration / 1000000000 / 60), event.name ,inicio = event.start , termino = event.end, id = 1
| dedup inicio, termino, indisponibilidad
| fields id, inicio, termino, indisponibilidad, event.name
| fieldsAdd cant_exito = lookup([
fetch bizevents
| filter contains(event.type ,"xxx") and (step == 1001 or step == 1000.20) and event.provider == "xxxxxxx"
and timestamp > toTimestamp(inicio)
and timestamp < toTimestamp(termino)
| fieldsAdd id_remoto = 1
| fields id_remoto, timestamp, salida = inicio
],
sourceField: id,
lookupField: id_remoto)
| fields inicio, termino, indisponibilidad, event.name, cant_exito
| sort inicio
