topic Re: Splunk DB Connect 1: Why are events from our Oracle database getting indexed with a default timestamp of "31-DEC-1970"? in Deployment Architecture

Splunk DB Connect 1: Why are events from our Oracle database getting indexed with a default timestamp of "31-DEC-1970"?

bharathkumarnec — Tue, 28 Jun 2016 11:18:05 GMT

Hello All,

I have configured Oracle DB with Splunk DB Connect 1, and most of the inputs that I am using are with tail.

I observed that events are applied with default time 31 DEC 1970, and this is causing an issue while indexing.

I have enabled output timestamp with timestamp column as table column name (XYZ) and the timestamp format is dd-MMM-YYYY HH:mm:ss.

Below are the column details:

XYZ
28-JUN-2016 06:17:27
28-JUN-2016 06:18:19

Kindly correct me if I am missing anything here.

Thanks for your reply!

Re: Splunk DB Connect 1: Why are events from our Oracle database getting indexed with a default timestamp of "31-DEC-1970"?

richgalloway — Tue, 28 Jun 2016 11:55:46 GMT

What is your query? Have you set $rising_column$ to XYZ?

Re: Splunk DB Connect 1: Why are events from our Oracle database getting indexed with a default timestamp of "31-DEC-1970"?

bharathkumarnec — Tue, 28 Jun 2016 11:57:20 GMT

Hi,

Here is my query:

select * from tablename {{WHERE to_date($rising_column$,'DD-MON-YYYY HH24:MI:SS') > to_date(?,'DD-MON-YYYY HH24:MI:SS')}}

Yes, I have set!

Re: Splunk DB Connect 1: Why are events from our Oracle database getting indexed with a default timestamp of "31-DEC-1970"?

kbrown_splunk — Tue, 29 Sep 2020 10:03:47 GMT

I have done this a couple of different ways in the inputs.conf file within the local directory of the db connect app.

input_timestamp_column_name = RecordTime
input_timestamp_format=
to let Splunk handle the conversion automatically

also
input_timestamp_column_name = WHENGMT
input_timestamp_format=yyyyMMddHHmmss

I always have
output_timestamp_format = yyyy-MM-dd HH:mm:ss

It takes some trial an error to get certain data sets to work. I suggest sending the events (records) to a test index that you can delete. Then set the tail_rising_column_checkpoint_value back to 0 to re-import the events. Use the 'All Time' search so you can see future event timestamps in case you have the GMT offset wrong.

Re: Splunk DB Connect 1: Why are events from our Oracle database getting indexed with a default timestamp of "31-DEC-1970"?

tmuth_splunk — Tue, 28 Jun 2016 13:40:07 GMT

I know this doesn't actually answer your question, but I think it's important to note that DBX 1.x is no longer supported in 1 month:

https://splunkbase.splunk.com/app/958/

Note: This Add-on will reach the end of its support lifecycle on July 29, 2016. Please see DB Connect v2 at https://splunkbase.splunk.com/app/2686/ .