All Apps and Add-ons

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

samwatson45
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

jcoates
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

jcoates
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.

samwatson45
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

jcoates
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

samwatson45
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

jcoates
Communicator

no problem!

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...