All Apps and Add-ons
Highlighted

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

Builder

Hi all.

I have the Splunk DB Connect 2 app working against a MySQL 5.x database. I have two columns with timestamps, intime and outtime 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 intime in epoch time, meanwhile outtime 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
Highlighted

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

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
Highlighted

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

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