Splunk DB Connect: Why are events from before a certain date coming in with a timestamp as today's date instead?

[sanjay_shrestha]

Hi,

I have configured a Database Input as below:

[dbmon-tail://MIMAS/DI-MBT-LAB_ScanBadge]
host = plxs_a_sb
index = mbt-lab-database
output.format = kv
output.timestamp = 0
output.timestamp.format = YYYY-MM-DD HH24:MI:SS
query = select * from MBT_LAB_BadgeScan_Vw  {{WHERE $rising_column$ > ?}}
sourcetype = DBConnect
tail.rising.column = BS_RISINGCOLUMN

All the events with date after October 22, 2014 are coming correctly, however rest of the events with the timestamp before that are coming as todays date but the time part is correct.

e.g.

    _time                              BS_TIMESTAMP
    1/29/15 11:19:21.000 PM  "2014-09-17 23:19:21"                 WRONG
    1/29/15 4:40:03.000 PM       "2014-09-16 16:40:03"                WRONG
    ....
    ...
    1/28/15 7:38:17.000 PM         "2015-01-28 19:38:17"
    1/28/15 6:12:46.000 PM     "2015-01-28 18:12:46"

I tried several combinations enabling timestamp, defining timestamp column etc... no luck.

Thanks,
Sanjay

[mchang_splunk]

Where is the BS_TIMESTAMP in the event?
You may want to increase MAX_TIMESTAMP_LOOKAHEAD for Splunk looks further for timestamp.

OR

you may set:
output.timestamp = 1
output.timestamp.column = BS_TIMESTAMP

[sanjay_shrestha]

I again created new data input but the issue remained same which means all the events earlier than Oct 22 is coming as today's date. That also means my guess about 100 days does not implies here.

We need to find out what is wrong with 10/21/2014 and earlier date. Most earliest date is 9/14/2014.

[sanjay_shrestha]

BS_TIMESTAMP is the first field in the query. I did try

output.timestamp = 1
output.timestamp.column = BS_TIMESTAMP

that but did not work.

Thanks,
Sanjay

Note: Since the broken timestamp is 100 or more days before today. So wonder if there is any configuration to is related to days.

[mchang_splunk]

You may try to increase:
MAX_DIFF_SECS_AGO:
default 3600 seconds but if you have events in a file with wide range of datetime, increase this value.

MAX_DAYS_AGO:
default is 2000 days unless specified.