DB Connect 2.0.3: Pulling MSSQL data using a database tail input, how do I convert epoch date fields in milliseconds?

[declan727]

I have a couple of DB Connect Inputs using DB Connect v2.0.3 with MS Integrated Authentication pulling data using tail input mode from different servers and databases and they are pulling data with Date fields which look like epoch without the period. I have a date field with 13 digits so when I convert them in a Splunk search, the date comes back as 12/31/9999 23:59:59. I have tried putting %s%3N in the "Input Timestamp Format" with no luck. This is what I am using to convert the date in the search: convert ctime(CREATED) as local_time. I was not able to get DB Connect v1 to work in our environment and v2 was a struggle to get working with Integrated Authentication, but it is now working, just having trouble with that date field. The kicker is its every Date field so at least splunk is being consistent.

Any help or thoughts would be appreciated.

[achuman]

MSSQL actually stores DateTime in the millisecond epoch format(13 digits), not the second epoch format(10 digits).

You need to add the following section to the DBConnect v2 props.conf:
[source::YourSourceHere]
TIME_PREFIX = .{0,}DateModified=
TIME_FORMAT = %s%3N
TZ = ZA

The source needs to correspond to the source specified in the inputs.conf.

TIME_PREFIX:
In my case the date is in a column called DateModified which also happens to be the last column in my query(I'm doing a straight select of the date, no casting). I've found that if you don't force Splunk to include the entire import before your field it sometimes doesn't import the date correctly so the .{0,} in front of the column name is very important.

The TIME_FORMAT tells Splunk that its epoch format with 3 additional millisecond digits included and lastly the TZ indicated the time zone of the import.

If your DateTime column is not the last one in the select I would also add MAX_TIMESTAMP_LOOKAHEAD = 13 to force Splunk to only use the first 13 characters after the Regex is found.