All Apps and Add-ons
Highlighted

Splunk DB Connect 2: How to troubleshoot why data is not getting indexed from an Oracle database using dbinputs?

Communicator

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)


inputs.conf

_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

Highlighted

Re: Splunk DB Connect 2: How to troubleshoot why data is not getting indexed from an Oracle database using dbinputs?

Splunk Employee
Splunk Employee

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.

Highlighted

Re: Splunk DB Connect 2: How to troubleshoot why data is not getting indexed from an Oracle database using dbinputs?

Contributor

What he said ^ . Also try simply using epoch time, the UI can be misleading

0 Karma
Highlighted

Re: Splunk DB Connect 2: How to troubleshoot why data is not getting indexed from an Oracle database using dbinputs?

Communicator

No @ktugwell , there are no errors in splunkd.log or dbx.log.

0 Karma
Highlighted

Re: Splunk DB Connect 2: How to troubleshoot why data is not getting indexed from an Oracle database using dbinputs?

Communicator

Apologies for my ignorance, but can you please show an example of your use of CONVERT ?

I tried
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 ...

0 Karma
Highlighted

Re: Splunk DB Connect 2: How to troubleshoot why data is not getting indexed from an Oracle database using dbinputs?

Splunk Employee
Splunk Employee

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.

http://stackoverflow.com/questions/12105691/convert-timestamp-datatype-into-unix-timestamp-oracle

0 Karma
Highlighted

Re: Splunk DB Connect 2: How to troubleshoot why data is not getting indexed from an Oracle database using dbinputs?

Communicator

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

0 Karma
Highlighted

Re: Splunk DB Connect 2: How to troubleshoot why data is not getting indexed from an Oracle database using dbinputs?

Path Finder

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

0 Karma
Highlighted

Re: Splunk DB Connect 2: How to troubleshoot why data is not getting indexed from an Oracle database using dbinputs?

Motivator

Do you have any / (slash) in your search? If so that's a bug.....

https://answers.splunk.com/answers/396880/dbconnect-213-divide-by-operator-is-not-working.html

0 Karma