All Apps and Add-ons

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

Path Finder

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

0 Karma
1 Solution

Communicator

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.

View solution in original post

Communicator

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.

View solution in original post

Path Finder

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?

0 Karma

Communicator

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?

0 Karma

Path Finder

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 🙂

0 Karma

Communicator

no problem!

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!