Common issues for Splunk DB Connect

This topic describes how to troubleshoot common Splunk DB Connect issues. If the Troubleshooting Tool for DB Connect does not solve your issue, try the following steps.

Answers

Have questions? In addition to these troubleshooting tips, visit Questions related to Splunk DB Connect on Splunk Answers to see what questions and answers the Splunk community has about using Splunk DB Connect.

Health dashboard

When you're trying to figure out the cause of degraded performance or figure out how failure rates correspond to transaction type or database user, the place to start is the health dashboard in Splunk DB Connect.

The health dashboard is a pre-configured dashboard that allows you to monitor and troubleshoot several aspects of your database connections from inside Splunk Enterprise. For more information about the health dashboard, see Monitor database connection health.

You can also see whether DB Connect is generating any internal errors, using a search with the following parameters:

   index=_internal sourcetype="dbx*" error

Health dashboard shows "Permission denied" error message

If the health dashboard displays a "Permission denied" error message instead of any data, the problem is likely a permissions issue with the logged-on user.

A logged-on user must be assigned a role that has access to both the _internal index and "dbx_health" source type in order to see the health dashboard. If none of the roles a user is assigned has permission to either search _internal or view data with sourcetype="dbx_health", the dashboard will display the "Permission denied" error.

DB Connect logging

Splunk DB Connect has extensive logging options, which can be configured in Settings. Before contacting Splunk support, you might want to enable debug logging, in case you need to provide Splunk support with DB Connect debug logs. Splunk DB Connect activity is logged to files in $SPLUNK_HOME/var/log/splunk and automatically indexed to _internal. The relevant log files for DB Connect are

• splunk_app_db_connect_server.log
• splunk_app_db_connect_server_access.log
• splunk_app_db_connect_job_metrics.log
• splunk_app_db_connect_health_metrics.log
• splunk_app_db_connect_dbx.log
• splunk_app_db_connect_audit_server.log
• splunk_app_db_connect_audit_command.*.log

To view DB Connect logging activity, use a search command such as the following:

   index=_internal sourcetype=dbx*

You can control access to logged events by limiting access to the _internal index using Splunk Enterprise roles. For example, by default non-administrators can't access _internal. Database and Splunk Enterprise administrators should work together to determine the optimal logging setup for their environment and decide how to handle special scenarios like troubleshooting. For more information about what Splunk Enterprise logs about itself, see "What Splunk logs about itself" in the Splunk Enterprise Troubleshooting Manual.

By default, DB Connect logs all executed SQL queries at INFO level. Logged along with each SQL query is the user that ran the query, the parameters, the number of results returned, and the name of the input, output, or lookup that was run.

Troubleshoot driver connections

If you're having trouble connecting to your database using Splunk DB Connect or loading your database's JDBC driver, please perform the following checks before contacting Splunk support:

• Is DB Connect running in Splunk Enterprise?
• Is Splunk Enterprise working correctly?
• Is the database working correctly?
• Verify you've installed the driver properly by repeating the steps in "Install database drivers."
• Verify that the driver and the driver version are supported by looking for them in Supported databases. If necessary, download a newer version of the driver.
• Is the right JDBC driver being loaded? To find out, in DB Connect, click Configuration in the top navigation bar, Settings, and Drivers to view the driver status screen. You can also search the _internal index for your specific driver's .JAR file. For example:

  index=_internal sourcetype=dbx_server mysql-*.jar

• Can you access the database with your browser or database client?
  • To test the connection to an Oracle database, use the Toad Java viewer.
  • To talk to a Postgres database, use the Postgres tool.
  • To talk to other types of databases, try DBVisualizer.

Use one of these tools to connect to the database and ensure that connectivity is good, host and port are correct, and that your credentials work. Then, copy those settings to DB Connect and try again.

Issues with bad line breaking/line merging

The problem is caused by Splunk line-break heuristics. Typically, log file data includes event timestamps, which Splunk understands. If you have timestamps in your database rows, you'll avoid line-break issues. Be sure to set output timestamp and specify that the timestamp column is the actual timestamp column.

If you don't have timestamps in your database rows

If you don't have timestamps in your database rows, you have to set the timestamp option to Current Index Time when creating an input.

If your timestamp is not of type datetime/timestamp

Splunk DB Connect expects the timestamp column in your database to be of type datetime/timestamp. If it is not (for example, it is in format char/varchar/etc.), you can first try to convert the SQL statement into the correct type using the CAST or CONVERT functions. If this method doesn't work, you can use the following workaround:

In the Set timestamp step when creating or editing an input, next to Timestamp, choose the Choose Column setting. Then, select the column from the drop down menu. Next, click the Datetime Format option, and then specify the timestamp using a Java DateTimeFormatter pattern so DB Connect can obey the timestamp output format setting. For example, if the database column EVENT_TIME which is the 5th columns from the result contains strings, such as CHAR, VARCHAR, or VARCHAR2, with values like 01/26/2013 03:03:25.255, you must specify the parse format in the appropriate copy of db_inputs.conf.

index_time_mode = dbColumn
input_timestamp_column_number = 5
input_timestamp_format = MM/dd/yyyy HH:mm:ss.SSS

Unexpected session key expiration

A system clock change or suspend/resume cycle can cause unexpected session key expiration. To remedy the problem, restart the Splunk Enterprise system using DB Connect.

Cannot connect to IBM DB2

IBM DB2 is supported only when the database is running on Linux. Splunk doesn't test or support DB Connect with DB2 on AS/400 or on Microsoft Windows.

Cannot connect to Microsoft SQL server

If you cannot connect to a Microsoft SQL server, ensure you've correctly followed the steps listed in Microsoft SQL Server. Specifically, verify that you are using the correct driver, host, and port:

• Driver: You can use either the Microsoft JDBC Driver for SQL Server or the jTDS driver. Be aware that, in contrast to the Microsoft driver, the jTDS driver does not support connecting over Secure Sockets Layer (SSL).

• Host: To specify a host for Microsoft SQL, use a fully qualified domain name, a short name, or an IP address. Do not use the Microsoft SQL convention of <SERVERNAME\DATABASE> for the host field.

• Port: Many Microsoft SQL Servers use dynamic ports instead of TCP/1433. Work with your database administrator to identify the correct port, or see "Verifying the port configuration of an instance of SQL Server" on the Microsoft website.

For more information about using Windows authentication with DB Connect and Microsoft SQL Server, see "Connecting to SQL Server" in the Install database drivers topic.

Cannot connect to Oracle SQL Server

If you cannot connect to a Oracle database, first ensure you've correctly followed the steps listed in Oracle database.

Connect to Oracle using SSL (for encryption only)

If you're having trouble connecting to Oracle using SSL for encryption:

1. First verify whether the connection works using an external tool that uses JDBC to connect to Oracle, such as DBVisualizer, with the following JDBC URL. Replace the <host>, <port>, and <database> placeholders with their actual values in your setup.

   jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=<host>)(PORT=<port>))(CONNECT_DATA=(SERVICE_NAME=<database>)))

   If the connection does not work, work with your database admin to correctly configure SSL for encryption.
2. If the connection works, click Edit JDBC URL button and enter the JDBC URL in the JDBC URL field. See more details about the JDBC URL settings, see create a database connection.
3. Restart Splunk Enterprise.

Oracle Error Codes

If you receive an error attempting to connect to an Oracle database, try the following. The most common error codes are:

ORA-12504: TNS:listener was not given the SID in CONNECT_DATA

This error means that the SID was missing from the CONNECT_DATA configuration. To troubleshoot, check that the connect descriptor corresponding to the service name in TNSNAMES.ORA also has an SID component in the CONNECT_DATA stanza.

ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

You are receiving this error because the listener received a request to establish a connection to the Oracle database, but the SID for the instance either has not yet dynamically registered with the listener or has not been statically configured for the listener. Typically, this is a temporary condition that occurs after the listener has started, but before the database instance has registered with the listener.

To troubleshoot, try waiting a few moments and try the connection again. You should also check which instances are currently known by the listener by executing: lsnrctl services <listener name>

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

This error occurs because the listener received a request to establish a connection to the database. The connection descriptor received by the listener specified a service name for a service that either has not yet dynamically registered with the listener or has not been statically configured for the listener.

To troubleshoot, try waiting a few moments and try the connection again. You should also check which instances are currently known by the listener by executing: lsnrctl services <listener name>

Explanation of Oracle TNS Listener and Service Names

TNS is a proprietary protocol developed by Oracle. It provides a common interface for all industry-standard protocols and enables peer-to-peer application connectivity without the need for any intermediary devices.

DB Connect uses Java (through JDBC driver) to connect Splunk Enterprise to a TNS Listener, which in turn connects to the Oracle Database. You can configure DB Connect to connect via the Service Name or the Oracle SID. Typically, most connectivity issues with DB Connect and Oracle Databases are caused by misconfiguration of the TNS Listener.

Settings in local .conf files are not being honored

If you notice that the custom settings you've specified in .conf files in your DB Connect /local directory are not being honored, here are a few things to try:

• First, be aware that settings specified within a stanza in a .conf file that resides in the /local DB Connect directory will take precedence over the corresponding entry in the same .conf file in the /default DB Connect directory. For more information, see Configuration file precedence.
• Stanza names and .conf file names must match exactly in both the /default and /local directories. The most common cause of this problem is misspellings in stanza names.

Missing instances of Microsoft SQL Server

If you have multiple instances of Microsoft SQL Server installed on your server, edit the JDBC connection string to add a parameter that explicitly references the instance you want to contact.

1. Follow the instructions in "Override db_connection_types.conf" to make a copy of the db_connection_types.conf file in the local directory and copy the stanza for the Microsoft SQL Server driver you're using into the file.
2. Edit the jdbcUrlFormat or, if you're connecting using SSL, jdbcUrlSSLFormat setting by appending it with the following:

   ;instanceName=

3. Set the instanceName parameter to the name of the instance you want to connect to. For example:

   jdbc:sqlserver://dbx-sqlserver.mydomain.com:1433;databaseName=master;instanceName=test

4. Save and close the file, and then restart Splunk Enterprise.

Incomplete field values are extracted when the value contains double quotes

When DB Connect encounters a column value during search-time field extraction that contains double quotation marks, it extracts the value, but stops extracting at the first quote. For example, consider this value named string:

   string="This is "an extraordinary" event, not to be missed."

DB Connect extracts this as follows:

   string="This is "

The rest of the value is ignored.

This occurs because auto-kv extraction will not handle quotation marks inside fields. If you have quotation marks inside fields, you will need to create field extractions for the datasource. For more information about search-time field extractions, see Create and maintain search-time field extractions through configuration files.

Rows with invalid timestamp value are skipped during indexing

You may encounter two scenarios in which the input will fail because of invalid timestamp values:

• The data type of the timestamp column is DATE, TIME or TIMESTAMP and it contains NULL.
• The data type of the timestamp column is not DATE, TIME or TIMESTAMP, and it returns invalid value after you convert it to Java date format.
  

The following screen shot is of the Configure Timestamp Column window from the third step of database input setup. The data format of the highlighted last_update column is VARCHAR. You need to set the data format in Datetime format under the table.

Windows fails to upgrade

When upgrading DBX on Windows, if you see errors such as "The process cannot access the file because it is being used by another process." you must disable the application first and then run the upgrade.

Unicode decode errors

If while using Splunk DB Connect you see Unicode decoding errors, your database might not be set up to return results encoded in UTF-8. For example, you might see errors in Splunk Enterprise that include the following line if your database is returning results that are not UTF-8-encoded:

UnicodeDecodeError: 'utf8' codec can't decode byte 0xe4 in position 30: invalid continuation byte

Splunk DB Connect requires that your database connection be set up to return results encoded in UTF-8. Consult your database vendor's documentation for instructions about how to do this.

DB Connect shows a task server communication error

When you launch DB Connect, the RPC service attempts to bind to port 9998 by default. If port 9998 is already in use by another service, you will see an error to load your configurations.

To work around this, you can change the port number to which the RPC service binds. To do this:

1. Go to the settings page from the configuration menu item
2. Edit the Task server port field to set an available port
3. Save your settings
4. The server will restart using this new port setting

Web interface timeouts

If you are experiencing timeouts while using the Splunk DB Connect interface, and hardware and network performance are not an issue, consider increasing the user session timeout values as described in Configure user session timeouts in the Splunk Enterprise Admin Manual.

DB Connect opens a lot of DB connections

Scheduled jobs such as inputs and outputs will use and reuse connections from a connection pool (this pool size is 8 by default, see maxTotalConn and useConnectionPool in db_connections.conf to change connection pooling behavior for scheduled jobs). Interactive DBX search commands (dbxquery, dbxlookup, dbxoutput) support the use of the connection pool mechanism.

Debug HTTP Event Collector port issues

In DB Connect 3.0.0, the architecture changed so that the JDBC driver returns the results to a Java thread. The Java thread calls the HTTP Event Collector (HEC) which then sends the results to splunkd and populates the indexers.

As a result of this change, you may find that the HEC is not working because of port issues. The following are examples of indicators that HEC is not working:

• Normal Query (in the Data Lab > SQL Explorer menu) returns results, but the DB Input does not populate the indexer. 
• In the Health > DB Input menu, you see an error.

To validate and resolve the HEC port issues:

1. Navigate to your Splunk search bar and enter index=_internal 8088 , replacing 8088 which whatever your HEC port is.  

2. The search returns one of these two messages:

• FATAL HTTPServer - Could not bind to port 8088 This error comes from splunkd.log
• [QuartzScheduler_Worker-1] ERROR .. org.apache.http.conn.HttpHostConnectException: Connect to 127.0.0.1:8088.. This errors comes from splunk_app_db_connect_server.log

3. To fix HEC port issues, go to Settings > Data Input > HEC > Global Settings and change the port.

4. Restart your instance of Splunk Enterprise.

The performance is slow when output data events from DB Connect to MySQL database

If you encounter issues of poor performance when exporting data events to MySQL databases, you need to check your network status. If the issue is due to network latency, you can workaround this by setting rewriteBatchedStatements=true when you edit JDBC URL. See more details about this workaround on JDBC batch insert performance.
Note that using this workaround may have some potential issues, see more on MySQL Configuration Properties for Connector

Oracle driver delaying database connection in Linux environments

When deployed in Linux environments, the Oracle driver sometimes causes connection delays. Program your Java Virtual Machine (JVM) to use /dev/urandom instead of /dev/random using one of the following options:

Option 1

1. On your JVM, navigate to Configuration > Settings.
2. Edit the JVM options to override the securerandom.source setting in your Java environment with /dev/urandom:

   -Djava.security.egd=file:/dev/./urandom

3. Navigate to $SPLUNK_HOME/etc/apps/splunk_app_db_connect/local/.
4. Open commands.conf and add an additional argument to dbxquery, dbxlookup, dbxoutput commands. For example:

   [dbxquery]
   command.arg.1 = -Dlogback.configurationFile=../config/command_logback.xml
   command.arg.2 = -Djava.security.egd=file:/dev/./urandom
   command.arg.3 = -DDBX_COMMAND_LOG_LEVEL=INFO
   command.arg.4 = -cp
   command.arg.5 = ../jars/command.jar
   command.arg.6 = com.splunk.dbx.command.DbxQueryCommand
   
   [dbxoutput]
   command.arg.1 = -Dlogback.configurationFile=../config/command_logback.xml
   command.arg.2 = -Djava.security.egd=file:/dev/./urandom
   command.arg.3 = -DDBX_COMMAND_LOG_LEVEL=INFO
   command.arg.4 = -cp
   command.arg.5 = ../jars/command.jar
   command.arg.6 = com.splunk.dbx.command.DbxOutputCommand
   
   [dbxlookup]
   command.arg.1 = -Dlogback.configurationFile=../config/command_logback.xml
   command.arg.2 = -Djava.security.egd=file:/dev/./urandom
   command.arg.3 = -DDBX_COMMAND_LOG_LEVEL=INFO
   command.arg.4 = -cp
   command.arg.5 = ../jars/command.jar
   command.arg.6 = com.splunk.dbx.command.DbxLookupCommand
   

5. Save your changes.

Option 2

1. Navigate to $JAVA_HOME/jre/lib/security/, and open the java.security file in a text editor.
2. Add the following line:

   securerandom.source=file:/dev/./urandom

3. Save your changes.

Connect Splunk DB Connect to Oracle Wallet environments using ojdbc8

1. Download the full ojdbc8 package.
1. Unpack the package.
2. In $SPLUNK_HOME/etc/apps/splunk_app_db_connect/drivers, create a directory named ojdbc8-libs.
3. Copy over all other jars in the package to $SPLUNK_HOME/etc/apps/splunk_app_db_connect/drivers/ojdbc8-libs/ except ojdbc*.jar
4. Copy over ojdbc*.jar to $SPLUNK_HOME/etc/apps/splunk_app_db_connect/drivers/
2. In Splunk DB Connect, navigate to Configuration > Settings > JVM Options, and prepend the following settings to the JVM options. Replace <wallet-directory> according to your environment. -Doracle.net.wallet_location=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=<wallet-directory>))) -Doracle.net.ssl_server_dn_match=false
3. In Splunk DB Connect, navigate to Configuration > Databases > Identities and create your Oracle database identity, if you have not done so already.
4. In Splunk DB Connect, navigate to Configuration > Databases > Connections and create a connection to the Oracle database.
5. Select Oracle as the connection type
6. Click the Edit JDBC URL checkbox, and update the text box with the following URL. jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=<oracle-database-hostname>)(PORT=2494))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=<oracle-database-service-name>))

   Replace oracle-database-host and oracle-database-service-name according to your environment.

7. In Oracle Wallet, create a TLS connection.
8. Navigate to $SPLUNK_HOME/etc/apps/splunk_app_db_connect/<os-arch>/bin/command.sh, and make the following updates. For example, <os-arch> is linux_x86_64.

   #!/usr/bin/env sh
    
   SCRIPT=$(readlink -f "$0")
   JAVA_PATH_FILE=$(dirname "$SCRIPT")/customized.java.path
    
   JVMOPTS="-Doracle.net.wallet_location=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=<wallet-directory>))) -Doracle.net.ssl_server_dn_match=false"
    
   if [ -f $JAVA_PATH_FILE ]; then
      JAVA_CMD=`cat $JAVA_PATH_FILE`
   elif [ ! -z "$JAVA_HOME" ];then
       JAVA_CMD="$JAVA_HOME/bin/java"
   else
       JAVA_CMD="java"
   fi
    
   exec $JAVA_CMD $JVMOPTS $@
   

9. Save your changes.
10. Create a data input, using the connection you created.