I'm using Splunk DB Connect in a Splunk Forwarder to collect data from a MySQL database. My Splunk version is 6.3.
The indexed data date refers to current date instead of the date from the input_timestamp_column_name (bbp_date column in the db in this case), although it did follow the time. I can see that Splunk has managed to create an additional field with the correct date and time as the bbp_date, however, the _time just does not follow. For example:
8/2/16
4:03:52.000 PM
"2005-12-05 16:03:52" BBP_SWASTA="BBP_SWASTA", bbp_trx_id="3784", bbp_date="2005-12-05 16:03:52", companyno="191484", swastano="J301", bbp_no="J301/S200512/000039", season="200101", gross_paddy_weight="5920",
Current Setting in inputs.conf:
[mi_input://splunk_purchase_swasta]
connection =doa_db
description = purchase data
index = bbp_purchase
input_timestamp_column_name = bbp_date
interval = 60
max_rows = 10000
mode = tail
output_timestamp_format = yyyy-MM-dd HH:mm:ss
query = SELECT * FROM splunk_bbp_swasta
source = xxx.xxx.xxx.xxx:3306
sourcetype = purchase_swasta
tail_follow_only = 1
tail_rising_column_checkpoint_value = 2005860
tail_rising_column_name = bbp_trx_id
ui_query_catalog = bbpdev
ui_query_mode = advanced
ui_query_schema = NULL
ui_query_table = NULL
*splunk_bbp_swasta is a virtual table created by db admin.
*the data type of bbp_date column is datetime
*the raw data of bbp_date is in format "2005-12-05 16:03:52.0"
I have tried the solutions below but none of them work:
1) Solution mentioned in https://answers.splunk.com/answers/71485/splunk-db-connect-timestamp-not-working.html
2) Put in the input_timestamp_format (nothing will be index if put in this setting)
3) Convert bbp_date column data type to character myself or ask db admin to change the data type during this virtual table creation.
4) Convert to bbp_date column to epoch time during sql elect query.
5) Try put bbp_date as the first or end in the sql select query.
Appreciate that someone can help to solve this, it is important for me. Thanks a lot.
I don't have that much experience with DBConnect... but I notice the event you have there is timestamped for 2005... Are you getting warnings from DateParserVerbose in _internal like https://answers.splunk.com/answers/8230/what-does-this-splunkd-log-dateparserverbose-message-mean.ht... ?
I don't have that much experience with DBConnect... but I notice the event you have there is timestamped for 2005... Are you getting warnings from DateParserVerbose in _internal like https://answers.splunk.com/answers/8230/what-does-this-splunkd-log-dateparserverbose-message-mean.ht... ?
Hi acharlieh, your suggestion could be the answer because I did see this error. The server is somehow inaccessible now, let me try on it and confirm with you soon. Thanks.
Other than the answer of acharlieh, the problem can be solved without the MAX_DIFF_xxx settings by upgrading the db connect to latest version (as of today date) v2.3.0.
Cool! I converted my comment to an answer then.