Is there a way to use a different value as _time field?

[samwatson45]

Hi all,

I have a dataset which I am bringing in from a SQL database (via Splunk DB Connect) that is quite large.
From this have a dashboard with a whole bunch of panels, some tables, figures and charts.

What I would like to do is use the a date field from this dataset to work with the time selector at the top of the page, so that when someone selects a certain month the data from that month is shown, rather than data extracted in that month (definitely not the same thing as it is not a live feed).

The date comes in the SQL format such as '2018-08-06', and I tried to change it but converting this timestamp to an unix/epoch format and renaming it as _time. This works for timecharts but for tables and figures it does not correspond to the time selector menu.

I also tried creating the unix timestamp in the SQL database, naming it _time and then bringing it in that way, but Splunk renames it as just 'time'. Then when I rename that as _time again it does not correctly correspond to the time selector for the page.

I am out of ideas, is there any way to get Splunk to use a foreign value as the _time field that works with the date selector on the dashboard?
Or some other way I can easily allow someone to manually select dates from another form of dropdown?

Many thanks,
Sam

[jcoates · Accepted solution]

that sounds like it's using current index time to set _time, instead of your column. http://docs.splunk.com/Documentation/DBX/3.1.3/DeployDBX/Createandmanagedatabaseinputs#Choose_input_...

It sounds like you're doing something like SELECT CONVERT(foo,epoch) AS _time FROM bar which isn't the same thing. The SQL convert trick is handy for avoiding Java dateline formatting, but I wouldn't overload the Splunk field. Something more like SELECT foo,CONVERT(foo, epoch) AS newfoo FROM bar.

[samwatson45]

Thanks for responding!

I have done the conversion in my SQL data harvest rather than the DB Connection conversion.
Basically no matter how i bring it or calculate the timestamp I cannot make it work with the time/date selector. Is there any way to change which field this looks at or manually overwrite the _time field for this data source?

[jcoates]

I suspect you're running into trouble because it's using current index time to set _time instead of your column, even though you're doing something to name a column _time which isn't the same thing. Can you check the setting referenced in the doc?

[samwatson45]

Oh I see what you mean now, there's a setting called 'Specify Timestamp Column' within the 'Set Parameters' part where you can enter what column to use.

That's awesome and solved my problem perfectly ^^ Thanks for pointing this out 🙂

[jcoates]

no problem!