makeTimeseries | DQL

[RPbiaggio]

Guys, can you help me understand how to transform the data I have separated by month into timeseries? I'm trying to use makeTimeseries, but when I do this, the data no longer returns separated by month due to the time parameter.

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`}

Thanks

[krzysztof_hoja · Accepted solution]

If you wanted to have timeseries subsequent months you cannot have it. Timeseries requires even intervals and different months have different length. But if you want chart metric with monthly aggregation it is not necessary to build timeseries at all. Here is simpler query showcasing use of summarize for such purpose (it also uses new functionality to rounding timestamps)

fetch dt.davis.problems, from:now() - 365d
| fieldsAdd Duration = resolved_problem_duration
| summarize MTTR = avg(toDouble(Duration))/60000000000, by:{timestamp@M}

Without new rounding it would look like this:

fetch dt.davis.problems, from:now() - 365d
| fieldsAdd Duration = resolved_problem_duration

| fieldsAdd month = toLong(formatTimestamp(timestamp,format:"M"))
| fieldsAdd yaer = getYear(timestamp)
| fieldsAdd timestamp=timestamp(yaer, month, 1, 0, 0,0)

| summarize MTTR = avg(toDouble(Duration))/60000000000, by:{timestamp}