How to use time token in dbxquery?

[Julia1231]

Hi everyone,

I am doing a dashboard in which I'm getting date from Postgresql using dbxquery.

Currently I'm filtering date range by putting it in WHERE of SQL query inside the dbxquery.

However I want to let users choose date range by themselve, for example by creating 2 input boxes in the dashboard and let them enter the time start and end that they want, the data then will be updated corresponding. 

The datetime format that I'm getting from postgresql is yyyy-mm-dd HH:MM:SS

I created an input with a token, but I don't know how to use that token inside my query.

How can I do it please? Or if you have any suggestion for this case, feel free to tell me.

Thanks,

Julia

[bowesmana]

To get the correctly formatted date into a token that you can use in you SQL you need to add the following global search to your dashboard

<search>
  <query>
| makeresults
| addinfo
| eval StartDate = strftime(info_min_time, "%F %T")
| eval EndDate = strftime(info_max_time, "%F %T")
  </query>
  <earliest>$time_picker.earliest$</earliest>
  <latest>$time_picker.latest$</latest>
  <done>
    <set token="start">$result.StartDate$</set>
    <set token="end">$result.EndDate$</set>
  </done>
</search>

Then in your SQL you can use the tokens $start$ and $end$ to denote the start/end time for the SQL search.

Note that the above assumes you name the time picker token as "time_picker"

[Julia1231]

Hi @bowesmana ,

I'm using dbxquery, something like this: | dbxquery connection="database" query="SELECT created_date, machine_id
FROM table"

when I use |makeresults inside the query, Splunk tells that 

Error in 'dbxquery' command: This command must be the first command of a search.

or

Error in 'makeresults' command: This command must be the first command of a search.

What can I do?

[bowesmana]

The search fragment is a separate search inside your dashboard - put it after the <form> element.

However, you format your SQL query to use start and end dates, just use $start$ and $end$ to represent the start and end dates in your dbxquery, e.g.

| dbxquery connection="database" query="SELECT created_date, machine_id..

WHERE created_date>$start$ AND created_date<$end$

 NB: I don't know the SQL syntax, so you will have to manage that.