DQL problem with dates

[Ellery]

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

[krzysztof_hoja]

Yes, you cannot access these fields. This is the example of place where DQL's logic is very different (for a reason) the SQL's logic. 

Unfortunately there is not a proper solution for you problem, but it is indeed valid use case. For now I can offer only a workaround which is safe to use when you have low number of davis problem and events due to initial filtering applied. In the solution I introduce (same as you) fake key to produce full cartesian product between sets of davis problems and bizevents using join command and filter out not matching pairs (time condition) later:

data
  record(event.name="a", inicio=now()-10m, termino=now()-9m),
  record(event.name="b", inicio=now()-6m, termino=now()-5m),
  record(event.name="c", inicio=now()-3m, termino=now()-2m)
  
| fieldsAdd id=1
| join [
  data
    record(timestamp=now()-11m),
    record(timestamp=now()-9m-30s),
    record(timestamp=now()-9m-20s),
    record(timestamp=now()-7m),
    record(timestamp=now()-2m-30s)
    
    | fieldsAdd id_remoto=1
], on:{left[id]==right[id_remoto]}, kind:leftOuter

| summarize bizcount=countIf ( right.timestamp>inicio and right.timestamp<termino ), by: {event.name}

Notes:

• Of course my query runs on test data. 1st "problem" spans over 2nd and 3rd "bizevent", 2nd does not have any biz events matching, and 3rd jus one (5th)
• kind:leftOuter helps in situations when there are no bizevents at all 

Please note that temporary data structure will be as big as multiplication of number of problems and events, so handle with care 😉