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.
... View more