topic Re: TIPS - DQL Timeframe selection in Dynatrace tips

TIPS - DQL Timeframe selection

gbaudart — Tue, 21 Nov 2023 11:03:09 GMT

I'm taking the liberty of sharing this tip because I've seen several people share difficulties in selecting a specific timeframe with the DQL, particularly when searching for information in the Logs.

Here are some examples to get around current limitations :

For -1d/d :

fetch logs,from:toTimestamp("T00:00:00")-1d

For -0d/d :

fetch logs,from:toTimestamp("T00:00:00")

For GMT+2 :

fetch logs,from:toTimestamp("T00:00:00+2")

I hope this TIP can be useful to as many people as possible 🙂
Please let me know your comments.

Re: TIPS - DQL Timeframe selection

radek_jasinski — Thu, 16 Nov 2023 13:37:37 GMT

Great😊 Thank you it's a very useful

Re: TIPS - DQL Timeframe selection

g_pmu — Mon, 27 Nov 2023 09:16:10 GMT

Great !

Re: TIPS - DQL Timeframe selection

36Krazyfists — Tue, 12 Dec 2023 18:41:09 GMT

What is this "toTimestamp" function? I tried searching for it and, while I see it used in examples in their documentation, I don't see any explanation of what the function actually is, and my Google searches aren't producing results.

*EDIT* Ahh, found it. It's in their Conversion and Casting Functions section of their docs instead of in their Time functions section. They should maybe cross-reference those though, just to make finding it easier.

Re: TIPS - DQL Timeframe selection

Christophe_N — Mon, 04 Mar 2024 12:56:19 GMT

Hi all ,

Thanks for the tip here 😀
Do you have any other tip if we want to go through relative timeframe?

like if I want relative timings (like "now-1h/h to now-0h/h") or "from: now-1M/M to :now-0M/M" , I have the message "The parameter `from` has to be constant"
I have not found any tips to do so.

Temp solution I have found is to query larger data and then filter afterwards :

|filter getHour(timestamp) >= (getHour(now())-1) and getHour(timestamp) < getHour(now())

Is it possible to do it directly from timeframe filter ?

Best regards,

Christophe

Re: TIPS - DQL Timeframe selection

Peter_Youssef — Sat, 12 Oct 2024 13:23:11 GMT

Thanks for sharing useful tip.

Re: TIPS - DQL Timeframe selection

Christophe_N — Wed, 11 Dec 2024 17:46:23 GMT

Hi all,
additional tip , for those who wants to use dashboard timeframe in one calculation

For example, if you want to use the timeframe of one dashboard in one tile , here is a tip you can use :

| lookup [ data record(timestamp=now()) | makeTimeseries sum(1) | fieldsAdd fk=1, timeframe=timeframe[end]-timeframe[start]-interval ], lookupField:fk, sourceField:fk, fields:{timeframe}

For example, you want to have Outage duration calculation from Davis Events

fetch events | filter event.kind == "DAVIS_EVENT" //you can add any filter here | fieldsAdd duration=event.end-event.start| filter isNotNull(duration) | fields event.start,event.end,duration,arrayLast(entity_tags),Tags=arrayFlatten(entity_tags) | summarize `Outage Duration`= sum(duration), by:{ Tags} | fieldsAdd fk=1 | lookup [ data record(timestamp=now()) | makeTimeseries sum(1) | fieldsAdd fk=1, timeframe=timeframe[end]-timeframe[start]-interval ], lookupField:fk, sourceField:fk, fields:{timeframe} | fieldsadd `Availability : 100%-duration outage`= if(100*(1-(`Outage Duration`/timeframe))>0,(100*(1-(`Outage Duration`/timeframe))),else:100) , `Outage Duration`=if(isNull(`Outage Duration`),0,else:`Outage Duration`) | fields Tags,`Availability : 100%-duration outage`,`Outage Duration`

Kudo to the support team to have found this while I got exotic request 🙂

Christophe

Re: TIPS - DQL Timeframe selection

Peter_Jelitsch1 — Wed, 16 Apr 2025 10:15:25 GMT

Thanks, very helpful! A small additional hint when interested in UTC. Add the "Z" to your timestamp format. Example:

fetch events, from:toTimestamp("T00:00:00Z")-1d, to: toTimestamp("T00:00:00Z") | summarize by: {}, sum=count()

gives use the count of events for yesterday, measured in UTC.

Edit: "T00:00:00Z" appears to be just a shortcut for "T00:00:00+0"

Cheers, PJ.