All Apps and Add-ons

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

nawneel
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

vasanthmss
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

V
0 Karma

andykuhn
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

ktugwell_splunk
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.

davebo1896
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

ktugwell_splunk
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

davebo1896
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

dolivasoh
Contributor

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

0 Karma

nawneel
Communicator

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

0 Karma
Get Updates on the Splunk Community!

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...