Splunk DB Connect - MySQL - NotFoundException: Can not find object of type connection on Query

[joepjisc]

I am trying to test Splunk DB Connect (on a DEVTEST instance CentOS 8, Splunk 8.0.2, Splunk DB Connect 3.3.0, openjdk 11.0.6, mysql-connector-java-5.1.48-bin.jar) to connect to a remote MariaDB 5.5.5 server.

I think I followed the install instructions correctly; except I cannot do the "Check DB Connect installation health" steps as Health Check doesn't seem to be available on my DEVTEST instance (Browser Console shows: GET http://splunk:8000/en-US/splunkd/__raw/services/search/distributed/groups?output_mode=json&_=1585579... 402 (Payment Required).)

Using SQL Explorer in Splunk DB Connect, I can select my Collection, Catalog and Table from the drop downs on the left, which auto-creates a SELECT statement, the connection must be up and properly authenticated to populate the Catalog and Table dropdowns, but when I click Run no results are returned and the following is written to splunk_app_db_connect_dbxquery.2020-03-30.log:

2020-03-30 15:32:58.031  [main] INFO  com.splunk.dbx.command.DbxQueryServer - operation= connection_name= stanza_name= action=dbxquery_server got request
2020-03-30 15:32:58.063  [main] INFO  com.splunk.dbx.command.DbxQueryServer - operation= connection_name= stanza_name= action=dbxquery_server got request
2020-03-30 15:32:58.093  [main] INFO  com.splunk.dbx.command.DbxQueryServer - operation= connection_name= stanza_name= action=dbxquery_server got request
2020-03-30 15:32:58.121  [main] INFO  com.splunk.dbx.command.DbxQueryServer - operation= connection_name= stanza_name= action=dbxquery_server got request
2020-03-30 15:32:58.156 24727@splunk [DBX-QUERY-WORKER-60] ERROR com.splunk.dbx.command.DbxQueryCommand - operation= connection_name= stanza_name= action=dbxquery_command failed to get connection
com.splunk.dbx.exception.NotFoundException: Can not find object MISPReader of type connection.
        at com.splunk.dbx.command.DbxQueryCommand.lambda$getConnection$1(DbxQueryCommand.java:180)
        at java.base/java.util.Optional.orElseThrow(Optional.java:408)
        at com.splunk.dbx.command.DbxQueryCommand.getConnection(DbxQueryCommand.java:180)
        at com.splunk.dbx.command.DbxQueryCommand.generate(DbxQueryCommand.java:359)
        at com.splunk.search.command.GeneratingCommand.process(GeneratingCommand.java:183)
        at com.splunk.search.command.ChunkedCommandDriver.execute(ChunkedCommandDriver.java:110)
        at com.splunk.search.command.AbstractSearchCommand.run(AbstractSearchCommand.java:50)
        at com.splunk.search.command.GeneratingCommand.run(GeneratingCommand.java:15)
        at com.splunk.dbx.command.DbxQueryCommand.runCommand(DbxQueryCommand.java:256)
        at com.splunk.dbx.command.DbxQueryServer.lambda$handleQuery$1(DbxQueryServer.java:144)
        at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
        at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
        at java.base/java.lang.Thread.run(Thread.java:834)

I've installed MySQL client on the Splunk server and can use this to connect and run the same SELECT query successfully, so permissions on DB are correct.

When I try to set dbxquery log level to debug the UI reports ('%s stanza in commands conf file is not valid because there must exist one and only one attribute with a value -DDBX_COMMAND_LOG_LEVEL=${LOG_LEVEL}', 'dbxquery')

I've been search for a few hours trying to find an answer but haven't been able to, any suggestions welcome.

Thanks
Joe

[wittenst1234]

This is more of a side note for this case, but we did have the following issue here when trying to set debug:

('%s stanza in commands conf file is not valid because there must exist one and only one attribute with a value -DDBX_COMMAND_LOG_LEVEL=${LOG_LEVEL}', 'dbxquery')

After upgrading to Splunk DB Connect 3.3.1 from 3.3.0, we didn't have the above issue and could set debug.

Oddly, the fix is not mentioned anywhere in the release notes: https://docs.splunk.com/Documentation/DBX/latest/ReleaseNotes/Releasenotes

[joepjisc · Accepted solution]

While I'm unsure what the root cause of this was, I have managed to fix it by deploying a new VM and running the below setup steps, after doing so I was able to set up my database connection correctly and run dbxquery commands successfully as well as set up hourly Splunk Alerts to update some lookups; all this continued to work after applying my Splunk DEVTEST License.

#!/bin/bash
sudo yum install splunk-8.0.3-a6754d8441bf-linux-2.6-x86_64.rpm -y
sudo dnf install java-11-openjdk-devel -y
JAVA_HOME=`dirname $(dirname $(readlink -f $(which javac)))`
export JAVA_HOME
sudo su -c 'echo 'JAVA_HOME="'"'dirname $(dirname $(readlink -f $(which javac)))\'"'"'>> /etc/environment'
tar xfz splunk-db-connect_330.tgz
tar xfz mysql-connector-java-5.1.48.tar.gz
cp mysql-connector-java-5.1.48/mysql-connector-java-5.1.48-bin.jar splunk_app_db_connect/drivers/
sudo cp -r splunk_app_db_connect /opt/splunk/etc/apps/
sudo chown -R splunk:splunk /opt/splunk
sudo /opt/splunk/bin/splunk restart
sudo /opt/splunk/bin/splunk enable boot-start
echo JAVA_HOME $JAVA_HOME

[joepjisc]

Adding the below to $SPLUNK_HOME/etc/apps/splunk_app_db_connect/local/dbx_settings.conf and restarting Splunk has not produced any further entries in logs.

[loglevel]
dbxquery = TRACE
dbxoutput = INFO
dbxlookup = INFO
dbinput = DEBUG
dboutput = DEBUG
connector = TRACE
processor = DEBUG

[joepjisc]

The SELECT statement never reaches the DB Server.

Enabling query logging on MariaDB, restarting Splunk, and, building and running the query through SQL Explorer writes the following to the query log:

200331 15:05:08    40 Connect   MISPReader@192.168.18.10 as anonymous on misp
                   40 Query     /* mysql-connector-java-5.1.48 ( Revision: 29734982609c32d3ab7e5cac2e6acee69ff6b4aa ) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
                   40 Query     SET NAMES utf8mb4
                   40 Query     SET character_set_results = NULL
                   40 Query     SET autocommit=1
                   40 Query     SET sql_mode='NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES'
                   40 Query     SET autocommit=1
                   40 Query     SELECT @@session.tx_isolation
                   40 Quit
                   41 Connect   MISPReader@192.168.18.10 as anonymous on misp
                   41 Query     /* mysql-connector-java-5.1.48 ( Revision: 29734982609c32d3ab7e5cac2e6acee69ff6b4aa ) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
                   41 Query     SET NAMES utf8mb4
                   41 Query     SET character_set_results = NULL
                   41 Query     SET autocommit=1
                   41 Query     SET sql_mode='NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES'
                   41 Query     SET autocommit=1
                   41 Query     SHOW DATABASES
200331 15:05:17    41 Query     SHOW FULL TABLES FROM `misp` LIKE '%'

[joepjisc]

The issue still persists after upgrading Splunk to 8.0.3, yum update reports nothing to do.