Dashboards & Visualizations

Passing date tokens to dqxquery SQL search

Svill321
Path Finder

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.

1 Solution

Svill321
Path Finder
| 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.

View solution in original post

bkilday
Explorer

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
Builder

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

0 Karma

zsteinkamp_splu
Splunk Employee
Splunk Employee

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.

0 Karma

Svill321
Path Finder
| 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.

View solution in original post

azamir_splunk
Splunk Employee
Splunk Employee

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.

0 Karma

Svill321
Path Finder

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.

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!