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!

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