Wrapping TimeRangePicker around DB Connector

[Lazarix]

Hi all,

I'd like to wrap a TimeRangePicker around a set of graphs which are pulled out from a DB connector query.
I have a field which is being pulled called "ValidFrom" which is a date and time column. This is in every graph.
I'd like to match up the TimeRangePicker so that when a range is chosen (last 24 hours, last 7 days, last 4 hours or whatever) that it uses the value taken from the "ValidFrom" field in order to visualise the data.
This data is not indexed, it is pulled from the DB connector each time the page is loaded (This is fine for now, as the usage is low, and I understand the data is better indexed in the long term).

Is there any way to get this to do what I want?
I'm already using advanced XML in this view.

Thanks in advance!

[Dan · Accepted solution]

If you want to post-filter the results from the DB, you can use http://splunk-base.splunk.com/answers/75999/

To pre-filter, you'll have to use a variation of that technique. In other words, you want to use the TimePicker variables in the SQL query itself. You can do that with the following search language:

| dbquery mydatabase [ stats count | head 1 | addinfo | eval query="SELECT * FROM mytable WHERE ValidFrom >= from_unixtime(".info_min_time.") and ValidFrom < from_unixtime(".info_max_time.")" | fields query | format "" "" "" "" "" ""]

Note:

* from_unixtime is a mysql specific function.

* If the timepicker is set to "all time" this will generate an error because info_max_time will be "+Infinity". To control for that, you can use an if statement in the eval. i.e.:

eval query=IF(info_max_time="+Infinity","SELECT WHERE ValidFrom >= info_min_time","SELECT WHERE ValidFrom >= and ValidFrom <")

[Lazarix]

Does anyone know how to do this?