Splunk DB Connect 2: How to use dbxquery to take a time field from an external db table and use that as _time for a drop-down time picker?

[snalonzo]

I have a requirement to query from a table in a database on dashboard load, and display those results on a dashboard, but also still use the relative date/time picker.

Trying to use dbxquery (Splunk DB Connect 2) to achieve this, but I can't seem to find how to take a time field from the external db table and utilize that time AS _time at search-time (or get the drop-down picker to interact in the right way with an epoch time from another field in the search).

The nature of this table prevents me from indexing this data and going from there, it only makes sense to query it on dashboard load (so using other features of db connect haven't met the need as far as I know, there is no logical rising column and batch jobs would be inefficient).

[cspires64]

I wasn't able to get jtrujillo's answer to work, but after much testing was able to convert my db query macros to dbx macros. Note, this is for a mysql database, if you have an oracle database or a different time format in the database, then you'll have to adjust the strftime format to match the time format in the database.

Simplified Macro Search Query:
dbxstart SELECT * FROM Database.Table WHERE timefield>= %earliest_time% AND timefield< %latest_time% dbxend("dbConnectName") maxrows=1000

Macro `dbxstart'
index=XXX | stats count | addinfo | eval et=round(info_min_time,0) | eval lt=if(info_max_time='+Infinity','now',round(info_max_time,0)) | convert timeformat='%Y-%m-%d" "%H:%M:%S' ctime(et), ctime(lt) | eval search="

Macro 'dbxend(1)' - with 1 argument for your connection name
" | eval search=replace(search, "%earliest_time%",et) | eval search=replace(search, "%latest_time%", lt) | map search="| dbxquery connection="$connection$" query=$search$ "

So this is what the Full Search looks like without Macros:
index=XXX | stats count | addinfo | eval et=round(info_min_time,0) | eval lt=if(info_max_time='+Infinity','now',round(info_max_time,0)) | convert timeformat='%Y-%m-%d" "%H:%M:%S' ctime(et), ctime(lt)
| eval search="SELECT * FROM Database.Table WHERE timefield>= %earliest_time% AND timefield< %latest_time% "
| eval search=replace(search, "%earliest_time%",et) | eval search=replace(search, "%latest_time%", lt)
| map search="| dbxquery connection="dbConnectName" query=$search$ " maxrows=1000

[jtrujillo]

Well.... we did it, but i don't like it...

The basics, are that you use the mintime and maxtime being cast from the picker into some search with data in it.... convert the time (strftime) and then compare that value to a date field in the DB query...

|dbxquery query=[search index=<some_index_w_data> | head 1 | addinfo | eval mintime=strftime(info_min_time,"%Y-%m-%d %H:%M:%S") | eval maxtime=strftime(info_max_time,"%Y-%m-%d %H:%M:%S") | eval query=IF(info_max_time="+Infinity", "SELECT..........FROM...... WHERE [Open Date & Time] >= ".mintime."

[jlemley]

I know this is a little older, but still relevant today. We developed an updated solution similar to @jtrujillo:

| dbxquery 
[| makeresults 
| addinfo 
| eval earliest=strftime(info_min_time,"%Y-%m-%d"), latest=strftime(info_max_time,"%Y-%m-%d") 
| eval query="SELECT * FROM MY_TABLE
    WHERE DATE_COL >= '".earliest."' AND DATE_COL < '".latest."'"
| return query] connection="dbconn" 

I hope it helps someone.

[SnackasaurusRex]

Sure did. You saved my lunch break, and possibly my hair. 🙂

[araitz]

The time picker on Splunk Simple XML dashboards operates on the earliest_time and latest_time aspects of a search. It would be non-trivial customization to get this to work with dbxquery and the data in your database.