Timeseries returns timestamp

[RPbiaggio]

Guys, I need to collect data on the volume of requests, for example, but considering only a specific period, for example, only Monday to Friday, from 9:00am to 6pm

Has anyone done this? Can you tell me if it is possible via DQL?

I was trying to execute a query in DQL, but the timeseries only returns the timeframe and not the collection timestamp. Can you tell me if it is possible, with the timeseries, to return the timestamp? If I can get this return, I believe I can only show the volume for that period.

timeseries count = sum(dt.service.request.count), 
  by: { endpoint.name }
| fieldsAdd Total = arraySum(count)
| fieldsAdd time = getStart(timeframe)
| fieldsAdd day = toTimestamp(getStart(timeframe))
| fieldsAdd timeH = formatTimestamp(time, format:"HH")
| fieldsAdd DayOfWeek = getDayOfWeek(day)
| fieldsAdd WeekDay = if(((DayOfWeek == 1)),"Segunda-Feira",
                            else:if(((DayOfWeek == 2)),"Terça-Feira",
                            else:if(((DayOfWeek == 3)),"Quarta-Feira",
                            else:if(((DayOfWeek == 4)),"Quinta-Feira",
                            else:if(((DayOfWeek == 5)),"Sexta-Feira",
                            else:if(((DayOfWeek == 6)),"Sábado",
                            else:if(((DayOfWeek == 7)),"Domingo")                            
                           ))))))
| fieldsAdd BH = if(timeH > "09" and timeH <= "18","Dentro BH",else:"Fora BH")
//| summarize Total = Sum(Total), by:{endpoint.name,BH}
| filterOut contains(BH,"Fora BH")

I'm using the query above, but as it returns timeframe, that is, it returns the period I'm collecting, these rules won't work. If I could get the collection timestamp, for example, I believe it would work.

Thank you

[krzysztof_hoja · Accepted solution]

You can use special function start() or end() in timeseries command to get timeseries of timestamps corresponding to each individual metric value. Then you can use these timestamps to determine if value of metric is withing or outside business hours according to you definition. To access individual elements of timeseries/arrays you need to use iterative expressions: https://docs.dynatrace.com/docs/platform/grail/dynatrace-query-language/operators#iterative-expressi...

I split request count into 2 timeseries in 2 different records (one for BH, one for non-BH) this way:

timeseries {count = sum(dt.service.request.count), timestamp=start()}, interval:1h
| fieldsAdd BH = getDayOfWeek(timestamp[])<6 and getHour(timestamp[])>=9 and getHour(timestamp[])<18
| fieldsAdd d=array(
  record( count=iCollectArray(if(BH[], count[])), BH="Dentro BH"),
  record( count=iCollectArray(if(not BH[], count[])), BH="Fora BH")
)
| expand d
| fields timeframe, interval, count=d[count], BH=d[BH]

note: I did not split by endpoint to keep result small and chart readable, but it will work with by:{endpoint} too.

Kris

[RPbiaggio]

@krzysztof_hoja, Thank you very much. This turned out perfect and is exactly what I need.