Splunk DB Connect 2: Why does a timestamp column pulled from MSSQL sometimes show negative values in indexed data?

[Ananton]

Hey,

I'm using the Splunk DB Connect App 2 to index data from an MS SQL Server.
The data was indexed as a Rising Column Input, with the Rising Column "AlarmLogItemID" and the Timestamp Column "AssertTime".
There was a second Timestamp named "RemitTime".
In the indexed Data, errors occur in the "RemitTime"-Column. Sometimes the epoch-timestamp has the value "-2208988800000".

Has someone an Idea, why this error occurs?!

[Ananton]

Is it possible that the value (-2208988800000) is an Wildcard/Placeholder for an unwritten Timestamp?

[einkebil]

It looks like an overflow of the data .

if it is the case :

The format in MSSQL of time is not like unix timestamps (see below).

You may try convert (bigint) and divide in the select query by 1000 the values to get seconds for the "Long" Part (left side of the comma|dot ) and the milliseconds will be dropped out.

---

Remark form https://technet.microsoft.com/en-us/library/aa260631(v=sql.80).aspx

The Transact-SQL timestamp data type is not the same as the timestamp data type defined in the SQL-92 standard. The SQL-92 timestamp data type is equivalent to the Transact-SQL datetime data type. A future release of Microsoft® SQL Server™ may modify the behavior of the Transact-SQL timestamp data type to align it with the behavior defined in the standard. At that time, the current timestamp data type will be replaced with a rowversion

[rtunstal]

Really struggling with this also. Converted the time by diving by 1000 and converting to a big int but db connect2 refused to index when I specify a timecolumn - changing this to index time works fine.

Query as follows now and Using the column Time

SELECT AllocationHistoryID, CONVERT(bigint,(DATEDIFF(s, '1970-01-01 00:00:00',StartTime ))) As Time, StartTime,EndTime, JobID, NodeName, UniqueCoreID FROM "HPCReporting"."dbo"."AllocationHistoryView"