TIPS - DQL Timeframe selection

[gbaudart]

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.

[radek_jasinski]

Great😊 Thank you it's a very useful 

[g_pmu]

Great !

[36Krazyfists]

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.

[Christophe_N]

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

[Peter_Youssef]

Thanks for sharing useful tip.

[Christophe_N · Accepted solution]

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