DB Connect: Why datetime field in mssql is imported as epoch time and how to convert it to a time-searchable value?

[krwinters11]

I have been unable to find a working solution to my problem. My datetime field in my mssql database looks like it is imported as epoch time. This results in me only being able to search via indexed time, when I would like to search by my datetime field. I can convert it to ctime, which makes it look correct to the user, but this is a string, and not a time-searchable value.

The closest answer I have found looks like my datetime field should be of type timestamp. I am unable to change my data types, so a solution/work-around would be great!

Thank you in advance 🙂

[musskopf · Accepted solution]

Hello, I used to have this problem and my workaround was to convert the Time to varchar in the SELECT statement and, at same time, use the option output.timestamp.parse.format in the DB Connect - This option is not available on the GUI, only via config file.

Here how the $SPLUNK_HOME/etc/apps/dbx/local/inputs.conf looks like:

[dbmon-tail://XXX_ACS/ACS Account Lockout]
index = ws_xxx_acs
interval = 300
output.format = kv
output.timestamp = 1
output.timestamp.column = CreationTimeUTC
output.timestamp.parse.format = yyyy-MM-dd' 'HH:mm:ss' 'Z
output.timestamp.format = yyyy-MM-dd' 'HH:mm:ss' 'Z
query = SELECT convert(varchar,dvA.CreationTime,120) + ' +0000' AS CreationTimeUTC, dvA.EventId, dvA.EventMachine AS Server, dvA.CollectionTime FROM AdtServer.dvAll5 AS dvA WHERE dvA.EventId IN (4740,644) {{ AND dvA.$rising_column$ > ? }}
sourcetype = xxx_acs_lockout
table = dvAll5
tail.rising.column = CollectionTime
disabled = 0

I normally configure everything via GUI, disable it via GUI and after all edit the inputs.conf to add the output.timestamp.parse.format. After edited the file I use the command $SPLUNK_HOME/bin/splunk reload monitor, get back to the GUI to double check and enable to input.

Note: If you need to use the Timestamp as the rising column, you'll need to point it to the original column and not to the converted one, as per my example.

Ps.: One more tip, if you add the DB Input, it'll start enabled by default. To prevent start collecting data before you change the inputs.conf, just schedule it to run using cron style to something like 1 1 1 1 1, so you'll have enough time do disable it.

[krwinters11]

Thanks! I'll give this a try.

[krwinters11]

It doesn't seem to change my format.

[musskopf]

Have you updated the query as well to output the timestamp as varchar?