DB Connect TimeStamp Format

[dan60201]

Has anyone successfully provided TimeStamp.Format in DB Connect for DateTimeOffset type (SqlSever)? The time is in UTC and looks like this (2013-05-15 04:26:24.249 -05:00).

I know I can convert it into DateTime type with proper format and it will work, but I am curious if I can simply provide a format that will work with time zone. One thing I notice, if I don't provide any format and choose not to output timestamp, Splunk still parse it correctly (in _time) with warning. It could be it just ignore the rest of time zone info and leave date time part which looks right.

output.timestamp = 0
output.timestamp.column = TimeStamp

dbx2941:WARN:ResultSetOutputPayload - Unrecognized timestamp format:'2013-05-15 04:26:24.249 -05:00'

[nunoaragao]

After trying dozens on Java SimpleDateFormat around, once I got confirmation on this Post here that this timestamp.format would be recognized

timestamp.format = yyyy-MM-dd'T'HH:mm:ss.SSSZ

Managed to get UTC dates properly indexed with this snipped on TSQL:

FORMAT(Time,'yyyy-MM-ddTHH:mm:ss.fff')+REPLACE(RIGHT(CONVERT(datetimeoffset,Time,26),6),':','')

I'm on DB Connect 3.1.3, we still can't trust props.conf ( DBX-4019 and DBX-4021 .... )

[jswanson]

Can you post an example of the timestamp? The GUI seemed pretty helpful in setting it up.

Yes, usually this kind of thing is handled by props.conf, but in this case you'll need to edit the inputs.conf. In my case it's /opt/splunk/etc/apps/dbx/local/inputs.conf.

I found that some things in the Java SimpleDateFormat aren't implemented, so make sure to check your var/log/splunk/dbx.log for errors.

[jswanson]

I struggled with this today. I eventually made it work by using

convert(VARCHAR(35),TimeStamp,126) + '-0000' as TimeStampWtz

In addition, I think you have to make sure to output the timestamp. The pertinent components to inputs.conf:

output.format = kv

output.timestamp = 1

output.timestamp.column = TimeStampWtz

output.timestamp.format = yyyy-MM-dd'T'HH:mm:ss.SSSZ

The format is specified in Java SimpleDateFormat, though it doesn't seem to be completely implemented.

[cpbridges]

Hi JSwanson, I'm also having trouble with my SQLite database and db connect. My time format I enter is using:

CREATE TABLE bat0 (tm TEXT, value float, x TEXT, date TEXT);

First, I'm unsure if I need to do anything here but reading the notes, that should work. My format is YYYY-MM-DD HH:MM:SSS and was wondering if/how db connect can pick that up when I search?

And is this edited in the props.conf file?

Thanks in advance for your help! Chris

[sowings]

Caveat: I haven't tested this as I don't have a SqlServer instance with time stamps of this format.

The usual kind of props.conf entries should apply, though. I'd try

%Y-%m-%d %H:%M:%S.%3N %z

The %z may be tripped up by the : in the zone specification (though I haven't tried), so you may have to ignore the offset from the data, and apply a manual TZ setting in the props.conf entry for the sourcetype defined by this data input.