topic Splunk DB Connect 2: I configured Input\Output timestamp format=Epoch, but why am I getting a corrupted timestamp? in All Apps and Add-ons

Splunk DB Connect 2: I configured Input\Output timestamp format=Epoch, but why am I getting a corrupted timestamp?

evelenke — Thu, 11 Aug 2016 15:43:08 GMT

Hi Splunkers

I have a MySQL table with column 'dateTimeOrigination' where each event records its time of occurrence in Linux time (epoch) like this: 1470925285 (standard 10 symbols). In Splunk DB Connect 2, I configure Output Timestamp Format=Epoch Time, Rising Column='dateTimeOrigination'. Metadata sourcetype is JSON.

What I have in output (indexed) is 1470925 (7 symbols), so Splunk converts it to 1/18/1970 and neither Output format works nor Java date can be established.

How this could happen (suddenly milliseconds are expected?) and how can I fix it?

Re: Splunk DB Connect 2: I configured Input\Output timestamp format=Epoch, but why am I getting a corrupted timestamp?

jcoates_splunk — Thu, 11 Aug 2016 15:54:51 GMT

Hi, using 2.3.0 improves the UI of this area, so it's worth going there... here's the docs section for doing inputs:
http://docs.splunk.com/Documentation/DBX/2.3.0/DeployDBX/Createandmanagedatabaseinputs

If your column is already datetime format in the database, you can just click it and we should be good.
If it's not, you can click it and specify an override format in Java style
Or you can modify your SQL to issue a friendly format: http://docs.splunk.com/Documentation/DBX/2.3.0/DeployDBX/SQLtipsandtricks
note that you might need to use props.conf to deal with timezone (for instance the database is in UTC and has global times, but your Splunk is in local and therefore sees some of the records as coming from the future).

Re: Splunk DB Connect 2: I configured Input\Output timestamp format=Epoch, but why am I getting a corrupted timestamp?

evelenke — Thu, 11 Aug 2016 17:07:40 GMT

Hi, jcoates.
Thank you for quick reply and suggestion, we will update ASAP.
But I'm afraid there's something misconfigured or that I can configure so Splunk will not cut off last 3 symbols of original timestamp record. The events are indexing well, the problem it has wrong input for time.

Re: Splunk DB Connect 2: I configured Input\Output timestamp format=Epoch, but why am I getting a corrupted timestamp?

woodcock — Mon, 15 Aug 2016 13:13:08 GMT

You do this with datetime.xml:

http://www.function1.com/2013/01/oh-no-splunking-log-files-with-multiple-formats-no-problem