topic Re: DB Connect TimeStamp Format in Splunk Search

DB Connect TimeStamp Format

dan60201 — Thu, 27 Jun 2013 17:01:00 GMT

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'

Re: DB Connect TimeStamp Format

sowings — Tue, 06 Aug 2013 15:31:38 GMT

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.

Re: DB Connect TimeStamp Format

jswanson — Wed, 07 Aug 2013 23:44:52 GMT

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.

Re: DB Connect TimeStamp Format

cpbridges — Thu, 08 Aug 2013 09:49:35 GMT

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

Re: DB Connect TimeStamp Format

jswanson — Thu, 08 Aug 2013 15:46:24 GMT

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.

Re: DB Connect TimeStamp Format

nunoaragao — Fri, 05 Apr 2019 14:38:43 GMT

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 .... )