All Apps and Add-ons

Splunk DB Connect 2: Why is a MySQL database time column in human readable format showing as epoch in DBX?

changux
Builder

Hi all.

I have the Splunk DB Connect 2 app working against a MySQL 5.x database. I have two columns with timestamps, in_time and out_time in human readable format YYYY-MM-dd HH:mm:ss (database side). In the db input configuration, the preview of the data (with SQL query) shows the column in_time in epoch time, meanwhile out_time is in human readable format. This is my first question: Why is the column shown as epoch time when it is stored from the source in YYYY-MM-dd HH:mm:ss format?
I checked this similar question:

http://answers.splunk.com/answers/183660/db-connect-why-datetime-field-in-mssql-is-imported.html?utm...

But the answer mentions a stanza that is not working in DB Connect 2. Any help?

Thanks!

0 Karma
1 Solution

emiller42
Motivator

I seriously doubt your timestamp is stored in the DB in a human-readable format. Typically, whatever tool you're using to query the DB will display DATE or TIMESTAMP columns formatted based on your locale settings. (Changing your locale settings would change the output)

So as you discovered, an eval to format the data is appropriate here. Another option is fieldformat:

... | fieldformat in_time=strftime(in_time, "%c")

The difference between eval and fieldformat is that the former actually changes the value of the field, and the latter just alters display. This means you can make things look like human-readable date/time stamps, and still do math on them:

... | fieldformat in_time=strftime(in_time, "%c") | fieldformat out_time=strftime(out_time, "%c") | eval duration=out_time-in_time | fieldformat duration=tostring(duration, "duration")

would give you three human-readable columns, but Splunk still treats them as their original decimal values.

View solution in original post

emiller42
Motivator

I seriously doubt your timestamp is stored in the DB in a human-readable format. Typically, whatever tool you're using to query the DB will display DATE or TIMESTAMP columns formatted based on your locale settings. (Changing your locale settings would change the output)

So as you discovered, an eval to format the data is appropriate here. Another option is fieldformat:

... | fieldformat in_time=strftime(in_time, "%c")

The difference between eval and fieldformat is that the former actually changes the value of the field, and the latter just alters display. This means you can make things look like human-readable date/time stamps, and still do math on them:

... | fieldformat in_time=strftime(in_time, "%c") | fieldformat out_time=strftime(out_time, "%c") | eval duration=out_time-in_time | fieldformat duration=tostring(duration, "duration")

would give you three human-readable columns, but Splunk still treats them as their original decimal values.

changux
Builder

Finally, i can't find an explanation. The workaround was use an eval function to convert to human readable format.

Thanks a lot.

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

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

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...