Timeframe selector to DQL timeframe

[RPbiaggio]

Guys, is it possible with DQL to pass the dynamic timeframe, just like it is done in classic mode? I need, for example, to extract the MTTR of the last month, from 01 to 00, until the last day of the same, 31 or 30, but I'm only able to get the last 30 days. That's not what I need.

In classic mode, you just put a previous month. Simple and easy, but now with DQL it doesn't seem possible to do that.

Thanks

[Mizső]

Hi @RPbiaggio 

Could you please check this post:

TIPS - DQL Timeframe selection - Dynatrace Community

I hope it helps.

Best regards,

Mizső

[michal_lewi · Accepted solution]

Hello @RPbiaggio 

you can take advantage of DQL and write your own timeframe selector:

fetch logs,
from:timestampFromUnixSeconds(unixSecondsFromTimestamp(timestampFromUnixSeconds(unixSecondsFromTimestamp(toTimestamp("00:00:00"))-((getDayOfMonth(toTimestamp("00:00:00"))-1)*24*60*60)-1))-(getDayOfMonth(timestampFromUnixSeconds(unixSecondsFromTimestamp(toTimestamp("00:00:00"))-((getDayOfMonth(toTimestamp("00:00:00"))-1)*24*60*60)-1))*24*60*60)+1),
to:timestampFromUnixSeconds(unixSecondsFromTimestamp(toTimestamp("00:00:00"))-((getDayOfMonth(toTimestamp("00:00:00"))-1)*24*60*60)-1)

maybe there is better way to do it:)

Best Regards

Michał

[RPbiaggio]

@michal_lewi , Thank you very much, that was exactly what I needed. 

I don't know if I can ask for help here, or if I should open a new thread, but can you tell me if it is possible to transform this data to be shown as a timeseries? I'm trying to put a makeTimeseries, but when I do this the time parameter is the problem. It doesn't show me separated by month as I want.

fetch events, from:now() - 720d
| filter event.kind == "DAVIS_PROBLEM"
| sort timestamp desc
| fieldsAdd Duration = resolved_problem_duration
| fieldsAdd Time = event.end
| fieldsAdd DayOfMonth = getDayOfMonth(timestamp)
| fieldsAdd DayOfWeek = getDayOfWeek(timestamp)
| fieldsAdd DayOfYear = getDayOfYear(timestamp)
| fieldsAdd Ano = getYear(timestamp)
| fieldsAdd `Mês` = if(DayOfYear >=1 and DayOfYear <=31,"Janeiro",
                  else:if(DayOfYear >=32 and DayOfYear <=59,"Fevereiro",
                  else:if(DayOfYear >=60 and DayOfYear <=90,"Março",
                  else:if(DayOfYear >=91 and DayOfYear <=120,"Abril",
                  else:if(DayOfYear >=121 and DayOfYear <=151,"Maio",
                  else:if(DayOfYear >=152 and DayOfYear <=181,"Junho",
                  else:if(DayOfYear >=182 and DayOfYear <=212,"Julho",
                  else:if(DayOfYear >=213 and DayOfYear <=243,"Agosto",
                  else:if(DayOfYear >=244 and DayOfYear <=273,"Setembro",                  
                  else:if(DayOfYear >=274 and DayOfYear <=304,"Outubro",
                  else:if(DayOfYear >=305 and DayOfYear <=334,"Novembro",
                  else:if(DayOfYear >=335 and DayOfYear <=365,"Dezembro"
                  ))))))))))))
| fieldsAdd MesOrder = if(DayOfYear >=1 and DayOfYear <=31,"01",
                  else:if(DayOfYear >=32 and DayOfYear <=59,"02",
                  else:if(DayOfYear >=60 and DayOfYear <=90,"03",
                  else:if(DayOfYear >=91 and DayOfYear <=120,"04",
                  else:if(DayOfYear >=121 and DayOfYear <=151,"05",
                  else:if(DayOfYear >=152 and DayOfYear <=181,"06",
                  else:if(DayOfYear >=182 and DayOfYear <=212,"07",
                  else:if(DayOfYear >=213 and DayOfYear <=243,"08",
                  else:if(DayOfYear >=244 and DayOfYear <=273,"09",                  
                  else:if(DayOfYear >=274 and DayOfYear <=304,"10",
                  else:if(DayOfYear >=305 and DayOfYear <=334,"11",
                  else:if(DayOfYear >=335 and DayOfYear <=365,"12"
                  ))))))))))))
| filter Ano == 2024
| filter `dt.davis.is_duplicate` == false
| filter not(matchesPhrase(event.name,"Memory usage close to limits"))
| filterOut contains(event.name,"")
| summarize MTTR = avg(toDouble(Duration))/60000000000, by:{Ano,`Mês`,MesOrder,Time}

| sort MesOrder asc
| makeTimeseries MTTR = avg(MTTR), time:{`Mês`},interval:(730h), by:{`Mês`}

Thank you

[michal_lewi]

Hello @RPbiaggio,

your next problem is not related to your original question. Please mark this problem as solved and create new task with meaningful title.  It allow future users to find solution more easily.

Thanks