topic Re: Wrapping TimeRangePicker around DB Connector in Splunk Search

Wrapping TimeRangePicker around DB Connector

Lazarix — Fri, 03 May 2013 12:43:31 GMT

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!

Re: Wrapping TimeRangePicker around DB Connector

Lazarix — Tue, 07 May 2013 12:56:21 GMT

Does anyone know how to do this?

Re: Wrapping TimeRangePicker around DB Connector

Dan — Mon, 28 Sep 2020 14:02:23 GMT

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 <")