topic Re: DB Connect not parsing timestamp properly in Getting Data In

DB Connect not parsing timestamp properly

adityapavan18 — Thu, 19 Sep 2013 13:47:49 GMT

Hi ir-respective of what timestamp is present in timestamp column of my Oracle DB, the timestamp in the event is replaced to "1970-01-01 00:59:59".

In oracle DB the timestamp column contains data like 2013-09-19T14:31:12

and the configuration in my inputs.conf is

    [dbmon-tail://SAMPLE/SPLUNK_USER.SAMPLEINPUT]
host = testhost
interval = * * * * *
output.format = kv
output.timestamp = 1
output.timestamp.column = PAYLOADDATE
output.timestamp.parse.format = yyyy-MM-dd HH:mm:ss
sourcetype = test_st
table = SPLUNK_USER.SAMPLEINPUT
tail.rising.column = PAYLOADDATE
index = default
query = SELECT * FROM SAMPLEINPUT {{WHERE $rising_column$ > ?}}
output.timestamp.format = yyyy-MM-dd HH:mm:ss

Note: PAYLOADDATE column is a varchar2 type

Can you please highlight what i am doing wrong?

Re: DB Connect not parsing timestamp properly

lukejadamec — Thu, 19 Sep 2013 13:56:40 GMT

For your output and output parse formats, have you tried:

yyyy-MM-dd'T'HH:mm:ss

Re: DB Connect not parsing timestamp properly

adityapavan18 — Thu, 19 Sep 2013 14:19:36 GMT

then nothing gets indexed

Re: DB Connect not parsing timestamp properly

lukejadamec — Thu, 19 Sep 2013 14:23:42 GMT

What is the error in the dbx log?

Re: DB Connect not parsing timestamp properly

adityapavan18 — Thu, 19 Sep 2013 14:32:41 GMT

there are no errors in dbx logs

Re: DB Connect not parsing timestamp properly

adityapavan18 — Thu, 19 Sep 2013 14:39:38 GMT

My mistake i used only T changed it to 'T' its working now thnx

Re: DB Connect not parsing timestamp properly

lukejadamec — Thu, 19 Sep 2013 14:41:03 GMT

??? When you save the input configuration it should reset the connection and create log entries. If there are no errors, then you should see something like:
INFO:TailDatabaseMonitor - Database monitor - your input - finshed with status=true resultCount=xxx in duration=xxxxms
and
Scheduler - Execution of input= your db input finished in duration=xxx with resultCount=xxx success=true continuemonitoring=true.
But that means it is working.

Re: DB Connect not parsing timestamp properly

lukejadamec — Thu, 19 Sep 2013 14:56:31 GMT

You're welcome.

Re: DB Connect not parsing timestamp properly

jcoates_splunk — Thu, 19 Sep 2013 16:27:09 GMT

what is the column type in your database? have you tried adding a SQL cast or convert to your SQL statement?