Passing date tokens to dqxquery SQL search

[Svill321]

Hello,

I'm working on a dashboard that uses dbxquery, and I am wondering. Is it possible to pass dashboard tokens to the SQL query? I know I can use the WHERE clause to find events between 2 dates:

| dbxquery query="SELECT * FROM table WHERE (Event_Date>'2017-05-01 00:00:00.0' AND Event_Date<'2017-05-30 23:59:59.0')" connection="connection_name" wrap=t 

This query works fine. But if I set up let's say two text inputs, like such:

| dbxquery query="SELECT * FROM table  WHERE ($datetok1$ AND $datetok2$)" connection="connection_name" wrap=t

I get no results. In this case, I would set the token prefix to be Submit_Date<' or Submit_Date>' and the suffix to be 00:00:00.0' or 23:59:59.0', so the user would only need to enter the year, month, and days they want to search between.

If this has been asked before, can someone link me to an answer? Also, is it possible to pass the time inputs into the SQL statement? I don't think it is, due to the differing formats (for example, 'yesterday', 'today', etc), but I'd like to be sure.

[bkilday]

I believe it is preferable to limit the results returned rather than post filtering a large data set. The below fragment will work for all relative and absolute values from a date picker sans real-time in a dashboard where the date range token is named $date_range_token$. It works by returning a query string to the dbxquery command from a pseudo-subsearch.

| dbxquery connection="your-db-connection" 
    [| makeresults 
    | eval time1 = tostring("$date_range_token.earliest$") 
    | eval time2 = tostring("$date_range_token.latest$") 
    | eval time2 = if(time2=="", now(), time2) 
    | eval time1 = if(time2=="now", relative_time(now(), time1), time1) 
    | eval time2 = if(time2=="now", now(), time2) 
    | eval time1 = if(match(time1,"[@|y|q|mon|w|d|h|m|s]"), relative_time(now(), time1), time1) 
    | eval time2 = if(match(time2,"[@|y|q|mon|w|d|h|m|s]"), relative_time(now(), time2), time2) 
    | eval time1 = strftime(time1, "%m/%d/%Y") 
    | eval time2 = strftime(time2, "%m/%d/%Y") 
    | eval query = "SELECT * FROM [catalog].[schema].[table] WHERE Date_To_Filter BETWEEN '" . time1 . "' AND '" . time2 . "'" 
    | return query] 

[dstaulcu]

you are my hero. now I need need to go back and update about 20 inefficient dashboards I have made over time knowing that this should have been a thing all along. #SmartAnswers

[zsteinkamp_splu]

To have the earliest/latest values used in your SQL statement, you can do something like this:
(NOTE: The UNIX time conversion below is Postgres-specific. See your DB documentation for converting UNIX timestamps to your database's date/time column type.)

| dbxquery connection=myconnection query="SELECT * FROM \"user\" WHERE \"createdAt\" >= to_timestamp(?::int) AND \"createdAt\" <= to_timestamp(?::int)" [ search index=main | head 1 | addinfo | eval params=round(info_min_time,0).",".round(info_max_time,0) | fields params | format "" "" "" "" "" "" ]

dbxquery takes a "params" argument whose comma-separated values are used to replace question marks in the SQL. In this case, we are using a subsearch to generate the params argument to dbxquery. To make the min/max times from the time picker available as fields, you have to use the "addinfo" command in your subsearch.

[Svill321 · Accepted solution]

| dbxquery query="SELECT * FROM table" connection="connection_name" wrap=t| rename "datetime_col" AS submit_date | eval submitdate_epoch=strptime(submit_date, "%Y-%m-%d %H:%M:%S.%N")  | eval time_late=mktime($timetok1.latest$) | search submitdate_epoch>=$timetok1.earliest$ AND submitdate_epoch<=$timetok1.latest$

I was able to make this as a work around that uses the time picker, and it works properly. The only issue is that I can't get it to work properly for relative time, like "Last 7 days". I assume that is because it is not in epoch, so I need to figure that out.

[azamir_splunk]

Your second query seems wrong, the text inputs if they don't contain query conditions are errors (also, this is not a dynamic query, so it just seems wrong) try the following query instead:

| dbxquery="SELECT * FROM table WHERE (Event_Date BETWEEN '2017-05-01 00:00:00.0' AND '2017-05-30 23:59:59.0')" connection="connection_name" wrap=t

If you want to use text inputs instead of the dates you can do the following (assuming $dateok1$ = '2017-05-01 00:00:00.0' and $dateok2$ = '2017-05-30 23:59:59.0'):

| dbxquery="SELECT * FROM table WHERE (Event_Date BETWEEN $dateok1$ AND $dateok2$)" connection="connection_name" wrap=t

or as you tried at first

| dbxquery query="SELECT * FROM table WHERE (Event_Date>$dateok1$ AND Event_Date<$dateok2$)" connection="connection_name" wrap=t

I recommend the first option I mentioned (The BETWEEN method is more efficient) but all three should work.

[Svill321]

That didn't work. I'm trying a different work around now, by seeing if I can pipe the results into a filter with a subsearch that uses the time picker input. However, this also does not seem to be working:

| dbxquery query="SELECT * FROM table" connection="connection_name" wrap=t | rename "datetime_col" AS submit_date | eval submitdate_epoch=strptime(submit_date, "%Y-%m-%d %H:%M:%S.%N") | search submitdate_epoch>=$timeetok1.earliest$ AND submitdate_epoch<=$timeetok1.latest$

The idea here is to use the time picker to pass epoch time to the subsearch.