Timeseries with one record, but start and end event

[Ellery]

Hello friends, I'm back with another question. In DQL, I need to do the following: I have a record that contains the start time and end time of a problem or event, and I would like to transform this record into a time series. For example:

The record is event_start=18:15, event_finish=18:35. When I convert it to a time series with a 5-minute interval, from 18:00 to 19:00, the result is {"","","1","","","","","","","","",""}, but considering the end time, it should be: {"","","1","1","1","1","1","","","","",""}, since the event existed during all those intervals.

This would be very helpful when I want to calculate the availability of a service when events overlap.

[krzysztof_hoja]

I think similar request with solutions is described here: 

https://community.dynatrace.com/t5/DQL/Making-timeseries-from-logs/m-p/253848/highlight/true#M1143

Kris

[Ellery]

I have found the solution, taking the start and end times of the event, this is generated as a timeseries in the following way.

fetch dt.davis.problems
| expand entity_tags
| filter dt.davis.is_duplicate == false 
  and (filters conditions) 
   
 
)
| makeTimeseries tiempos = count(), spread: timeframe(from: event.start, to: coalesce(event.end, now())) , nonempty:true, interval: 5m
| fieldsAdd total_general = arraySize(tiempos) * 5
| fieldsAdd total_indispo = arraySize(arrayRemoveNulls(tiempos)) * 5
| fieldsAdd perc_dispo = if(isNull(toDouble(total_indispo)/toDouble(total_general)*100), "100", else: 100 - toDouble(total_indispo)/toDouble(total_general)*100)

| fields perc_dispo

[Fin_Ubels]

While you've already found a solution, I just wanted to note for future people coming across things like this, you can peak into the queries used across the Dynatrace UI and reuse them for yourself. For example, the above query is something we do on the new problems page. If you open the network tab in developer tools in your browser you can look for query:execute requests when loading the problems page and then peak into the payload to see the query used. You can then reuse that query directly or as a building block for what you want to do.