Can you help me troubleshoot my problem bringing data to Splunk using DBConnect with Rising Column?

[przevas]

I´m new using DBConnect and I’m trying to create a new input to collect Data from a DataBase Oracle.

I installed the driver 12.2 and DBConnect detected the installation.

I created the Identities “sitef_db” and the Connection “sitefweb” using the identities “sitef_db” in DBConnect , Both OK.

I created a new input called “sitefweb_test” using the connection “sitefweb” with the following settings:

In the tab “Choose and Preview Table”

I selected in Input Type “Advanced”
Max Rows 100

Created the Query:
SELECT * FROM table WHERE abc > ? ORDER BY abc asc

In the Checkpoint Column I selected “abc” and Checkpoint Value. Then, I completed with date format abc column “yyyy-mm-dd HH:MM:SS.0”

When I execute the query, DBConnect returns the Data with the correct checkpoint applied.

In the tab “SET PARAMETERS” the options are:
Input Type Advanced
Max Rows to Retrieve 200.000
Fetch Size 1000
Timestamp Choose Column
Specify TimeStamp Column XYZ
Output Timestamp Format yyyy-MM-dd HH:mm:ss
Execution Frequency */10 * * * *

In the tab “Metadata”
Source sitefweb_source
Sourcetype sitefweb_sourcetype
Index idx_sitef
Select Resource Pool Local
After this I saved the input to begin collect data to Splunk but appear a diferente error from this input:

[ERROR] [dbxquery.py], line 235: action=dbxquery_execute_query_failure query="SELECT * FROM (SELECT * FROM "SITEFWEB"."INTERFACECMP_OUT" WHERE DAT_VENDA > ? ORDER BY DAT_VENDA ASC) t" params="None" cause="Exception(' java.sql.SQLException: Missing IN or OUT parameter at index:: 1.',)" Traceback (most recent call last): File "C:\Program Files\Splunk\etc\apps\splunk_app_db_connect\bin\dbxquery.py", line 228, in _execute_rpc_query self.timeout, self._handle_chunked_query_results, user) File "C:\Program Files\Splunk\etc\apps\splunk_app_db_connect\bin\dbx2\ws.py", line 94, in executeQuery self.ws.run_forever(timeout=self.timeout) File "C:\Program Files\Splunk\etc\apps\splunk_app_db_connect\bin\dbx2\websocket.py", line 841, in run_forever self._callback(self.on_error, e) File "C:\Program Files\Splunk\etc\apps\splunk_app_db_connect\bin\dbx2\websocket.py", line 852, in _callback callback(self, *args) File "C:\Program Files\Splunk\etc\apps\splunk_app_db_connect\bin\dbx2\ws.py", line 130, in on_error self.callback(None, error) File "C:\Program Files\Splunk\etc\apps\splunk_app_db_connect\bin\dbxquery.py", line 210, in _handle_chunked_query_results raise error Exception: java.sql.SQLException: Missing IN or OUT parameter at index:: 1.

[WARNING] [health_logger.py], line 162: Failed to run query: "SELECT * FROM (SELECT * FROM "SITEFWEB"."INTERFACECMP_OUT" WHERE DAT_VENDA > ? ORDER BY DAT_VENDA ASC) t", params: "None", caused by: Exception(' java.sql.SQLException: Missing IN or OUT parameter at index:: 1.',). Traceback (most recent call last): File "C:\Program Files\Splunk\etc\apps\splunk_app_db_connect\bin\dbx2\health_logger.py", line 160, in do_log return func(*args, **kwargs) File "C:\Program Files\Splunk\etc\apps\splunk_app_db_connect\bin\dbxquery.py", line 236, in _execute_rpc_query raise RuntimeError('Failed to run query: "%s", params: "%s", caused by: %s.' % (query, repr(params), repr(e))) RuntimeError: Failed to run query: "SELECT * FROM (SELECT * FROM "SITEFWEB"."INTERFACECMP_OUT" WHERE DAT_VENDA > ? ORDER BY DAT_VENDA ASC) t", params: "None", caused by: Exception(' java.sql.SQLException: Missing IN or OUT parameter at index:: 1.',).

WARN HttpListener - Socket error from 127.0.0.1 while accessing /servicesNS/nobody/splunk_app_db_connect/data/inputs/mi_input/sitefweb_test/disable: Winsock error 10054

ERROR DBX2Proxy:46 - Exception encountered for entity-name = mi_input://sitefweb_test and type = input java.lang.NumberFormatException: For input string: "2018-09-25 00:00:00" at java.lang.NumberFormatException.forInputString(Unknown Source) at java.lang.Long.parseLong(Unknown Source) at java.lang.Long.valueOf(Unknown Source)

[CRITICAL] [mi_base.py], line 195: action=modular_input_exited_after_maximum_failed_retries modular_input=mi_input://sitefweb_test max_retries=6 error=ERROR: java.lang.NumberFormatException: For input string: "2018-09-25 00:00:00". Traceback (most recent call last): File "C:\Program Files\Splunk\etc\apps\splunk_app_db_connect\bin\dbx2\mi_base.py", line 183, in run checkpoint_value=checkpoint_value) File "C:\Program Files\Splunk\etc\apps\splunk_app_db_connect\bin\dbx2\health_logger.py", line 283, in wrapper return get_mdc(MDC_LOGGER).do_log(func, *args, **kwargs) File "C:\Program Files\Splunk\etc\apps\splunk_app_db_connect\bin\dbx2\health_logger.py", line 160, in do_log return func(*args, **kwargs) File "C:\Program Files\Splunk\etc\apps\splunk_app_db_connect\bin\mi_input.py", line 207, in run _do_advanced_mode(input_name, inputws, self.db, params, self.user_name, splunk_service, output_stream) File "C:\Program Files\Splunk\etc\apps\splunk_app_db_connect\bin\mi_input.py", line 92, in _do_advanced_mode inputws.doAdvanced(db, params, user, stanza, callback=callback) File "C:\Program Files\Splunk\etc\apps\splunk_app_db_connect\bin\dbx2\ws.py", line 284, in doAdvanced self.doInput("dbinputAdvancedIterator", database, params, user, entityName, callback) File "C:\Program Files\Splunk\etc\apps\splunk_app_db_connect\bin\dbx2\ws.py", line 275, in doInput self.ws.run_forever(timeout=self.timeout) File "C:\Program Files\Splunk\etc\apps\splunk_app_db_connect\bin\dbx2\websocket.py", line 841, in run_forever self._callback(self.on_error, e) File "C:\Program Files\Splunk\etc\apps\splunk_app_db_connect\bin\dbx2\websocket.py", line 852, in _callback callback(self, *args) File "C:\Program Files\Splunk\etc\apps\splunk_app_db_connect\bin\dbx2\ws.py", line 328, in on_error raise Exception ("%s" % error) Exception: ERROR: java.lang.NumberFormatException: For input string: "2018-09-25 00:00:00".

[CRITICAL] [mi_input.py], line 96 : action=loading_input_data_failed input_mode=advanced dbinput="mi_input://sitefweb_test" error="ERROR: java.lang.NumberFormatException: For input string: "2018-09-25 00:00:00"."

[CRITICAL] [ws.py], line 327: [DBInput Service] Exception encountered for entity-name = mi_input://sitefweb_test and type = input with error = ERROR: java.lang.NumberFormatException: For input string: "2018-09-25 00:00:00"..

[ERROR] [ws.py], line 318: [DBInput Service] Exception encountered from server on_message for entity-name = mi_input://sitefweb_test and type = input with error = ERROR: java.lang.NumberFormatException: For input string: "2018-09-25 00:00:00"..

Why do I get these errors when I start these input?

[larmesto]

Hi,

As I understand it's better that the Checkpoint value be numeric. But first of all, using Rising Column ("abc " in your case) could you leave "Checkpoint Value" field empty ? This will tell DBConnect to load all the records during the first request. If that worked then the issue was the value within the checkpoint in the first place.

Thank you!