Add a variable to the “from:" and "to:” section of a DQL query

[cwinkler109]

Is it possible to add a variable to the “from:" and  "to:” section of a DQL query that is fetching logs?

Background: I have a dashboard that surfaces info from logs from a specific time of the day each morning. I want to be able to select a value in a dashboard variable that represents today, yesterday, 2 days ago, 3 days ago, etc. and have the dashboard show data for that specific time period of the chosen day.

The following DQL examples work correctly. I’d like to have a dashboard variable that changes the day being specified in the To and From sections.

Today:

• fetch logs, from: bin(now(), 1d)+ 5h + 50m, to: bin(now(), 1d) + 6h + 30m

Yesterday: 

• fetch logs, from: bin(now()-1d, 1d)+ 5h + 50m, to: bin(now()-1d, 1d) + 6h + 30m

Two Days Ago:

• fetch logs, from: bin(now()-2d, 1d)+ 5h + 50m, to: bin(now()-2d, 1d) + 6h + 30m

Is this possible? What would the DQL look like? 

[AustinSabel · Accepted solution]

@cwinkler109 you can accomplish this using a list variable, for example: 

Along with the following query: 

fetch logs, 
 from: duration(toLong(concat("-",$daysAgo)), unit:"d")@d+5h+50m, 
 to: duration(toLong(concat("-",$daysAgo)), unit:"d")@d+6h+30m
| summarize min(timestamp), max(timestamp)

As a side note, just so I understand it, are you looking to create a dashboard that shows logs for a given day between 5:50am and 6:30am local time? If so, you may get some inconsistent responses using bin() format based on the timezone.

I've attached a copy of an example dashboard using this functionality and showing some differences between bin() and @d.

[cwinkler109]

Perfect. AustinSable, you are a DQL hero, and saved my dashboard. Thank you!!!

[AustinSabel]

Glad I could help!