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

[evelenke]

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?

[woodcock]

You do this with datetime.xml:

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

[jcoates_splunk]

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

1. If your column is already datetime format in the database, you can just click it and we should be good.
2. If it's not, you can click it and specify an override format in Java style
3. Or you can modify your SQL to issue a friendly format: http://docs.splunk.com/Documentation/DBX/2.3.0/DeployDBX/SQLtipsandtricks
4. 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).

[evelenke]

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.