topic Re: Splunk DB Connect upgrade: SQL Query for pulling Oracle audit trails stopped working in All Apps and Add-ons

Splunk DB Connect upgrade: SQL Query for pulling Oracle audit trails stopped working

smcbride27 — Tue, 03 Oct 2017 13:26:45 GMT

After upgrading to DBX 3.1.1 from DBX 2.3.x My SQL query for pulling Oracle audit trails no longer works. I'm using a converted time stamp based off of the event time stamp for the rising column, and I've added in the additional WHERE statement to the query, per the guidelines. The query works in batch mode, but errors out when I select rising column. Any help would be greatly appreciated.

Re: Splunk DB Connect upgrade: SQL Query for pulling Oracle audit trails stopped working

smcbride27 — Tue, 03 Oct 2017 13:41:01 GMT

This is the error that I'm getting: java.sql.SQLException: Missing IN or OUT parameter at index:: 1

Re: Splunk DB Connect upgrade: SQL Query for pulling Oracle audit trails stopped working

Venkat_16 — Mon, 16 Oct 2017 13:00:37 GMT

Any updates on the resolution please?

Re: Splunk DB Connect upgrade: SQL Query for pulling Oracle audit trails stopped working

smcbride27 — Mon, 16 Oct 2017 13:21:35 GMT

It turns out that I found a bug with support. They went back to the developers and we have a work around.

Re: Splunk DB Connect upgrade: SQL Query for pulling Oracle audit trails stopped working

richgalloway — Mon, 16 Oct 2017 14:35:38 GMT

So others might benefit, please share the workaround.

Re: Splunk DB Connect upgrade: SQL Query for pulling Oracle audit trails stopped working

smcbride27 — Tue, 29 Sep 2020 16:15:47 GMT

To work around the rising column issue non indexable columns need to be skipped in the sql query. (For me this was specifically for the audit trails on an Oracle Exadata). This was done by editing the sql queries manually in the $SPLUNK_HOME/etc/apps/splunk_app_db_connect/local directory in the db_inputs.conf file

Skipping columns causes a shift in the rising column index, so the checkpoint files need to be cleaned up as well. These files are under $SPLUNK_HOME/var/lib/splunk/modinputs/server/splunk_app_dbconnect

Note: Some of the above files and locations do NOT exist until the app is upgraded, and the migration completed.

Re: Splunk DB Connect upgrade: SQL Query for pulling Oracle audit trails stopped working

Venkat_16 — Mon, 16 Oct 2017 14:55:17 GMT

We have the similar issue on DB Connect 2.4.x as well.
Could you please share the workaround?