I am trying to use Splunk DB Connect 2's dbinput feature to index data. All specifications such as connection and preview table all work fine, but data does not get indexed. When I try using DB Connect `, I am able to index data, but since data size is huge, there is some inconsistency and that's the reason I want to use DB Connect 2.
Database is Oracle, dump works fine, but there is an issue with the rising column.
rising column sample data :2016-04-16 18:30:23(yyy-mm-dd H:M:S)
_rcvbuf = 1572864 allowSslCompression = true allowSslRenegotiation = true connection = yanrep dedicatedIoThreads = 2 disabled = 1 enableSSL = 1 host = scompsprdrk1v index = sterlingdb1 input_timestamp_column_name = MODIFYTS interval = 05 * * * * maxSockets = 0 maxThreads = 0 max_rows = 10000000 mode = tail output_timestamp_format = yyyy-MM-dd HH:mm:ss port = 8088 query = select sourcetype = YFS_ORDER_LINE sslVersions = *,-ssl2 tail_follow_only = 1 tail_rising_column_name = MODIFYTS ui_query_catalog = NULL ui_query_mode = advanced useDeploymentServer = 0 source = dbmon1
Thanks in advance
Do you have any errors in your dbx.log or splunkd.log?
I have found using dbxv2 it sometimes does not play very nicely with timestamps as a rising column. So to get around this I usually CONVERT the values in the SQL query first.
What he said ^ . Also try simply using epoch time, the UI can be misleading
Apologies for my ignorance, but can you please show an example of your use of CONVERT ?
SELECT DATE(dt) as date FROM ...
but that is not working. The dbx log reports
[INFO] [dbxquery.py] Executing query: SELECT * FROM (SELECT DATE(dt) AS date FROM ...
I'm not that familiar with Oracle, but from a quick bit of research, it looks like you need to build a function to convert to Epoch(Which is probably reccomended). Take a look at the answer to this question and see if that helps you.
I'm actually connecting to Informix, I was able to convert to epoch using
((DATE(dt) - DATE('1970-01-01')) * 24 * 60 * 60) AS epoch_time
where dt is the date field. Still not having much luck creating a db input, though
I have not had luck using timestamps as row identifiers for DBX myself, so I always use row numbers added to the table upon data insert. That said, you may want to review the rising column value stored in dbx state.xml file in persistent storage ( $SPLUNK_HOME\var\lib\splunk\persistentstorage\dbx\state.xml ) to see make sure you should be getting a result back. You should ensure that this value is RESET and DOES not contain your most recent value. Your index should also be cleaned if you are testing the data indexing via dbx or dbx2. This is the file which keeps track of your rising column. Also, this link may be related: https://answers.splunk.com/answers/203656/splunk-db-connect-dbmon-tail-input-not-working-as.html