Common issues for Splunk DB Connect

Troubleshoot common Splunk DB Connect issues using the Troubleshooting Tool for DB Connect. 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, go to 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 let's you 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 have an assigned 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 you assign a user to has permission to either search _internal or view data with sourcetype="dbx_health", the dashboard displays the "Permission denied" error.

DB Connect logging

Splunk DB Connect has extensive logging options, which you can configure 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 logs DB Connect activity 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 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 Splunk ran.

Troubleshoot driver connections

If you're having trouble connecting to your database using Splunk DB Connect or loading your database's JDBC driver, run 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 correctly by repeating the steps in "Install database drivers".
• Verify that Splunk supports the driver and the driver version by looking for them in Supported databases. If necessary, download a newer version of the driver.
• Are you loading the correct JDBC driver? To find out, in DB Connect, select 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

Splunk line break heuristics is what causes this problem. Typically, log file data includes event timestamps, which Splunk understands. If you have timestamps in your database rows, there won't be any line break issues. Be sure to set output timestamp and confirm 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, select the Choose Column setting. Then, select the column from the drop down menu. Next, select the Datetime Format option, and then select 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 enter 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

Splunk supports IBM DB2 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:

• Host: To enter 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, select the 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. Confirm 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. Confirm 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 by using the Service Name or the Oracle SID. Typically, most connectivity issues with DB Connect and Oracle Databases come from a misconfiguration of the TNS Listener.

Splunk does not honor settings in local .conf files

If you notice that Splunk is not honoring the custom settings you've specified in .conf files in your DB Connect /local directory, 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 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.

Splunk extracts incomplete field values 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 "

Splunk ignores the rest of the value.

This occurs because auto-kv extraction doesn't handle quotation marks inside fields. If you have quotation marks inside fields, you 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.

Splunk skips rows with invalid timestamp value during indexing

You might encounter two scenarios in which the input fails 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.

==Upgrade on Windows fails

If during upgrade of DBX on Windows errors like "The process cannot access the file because it is being used by another process", disable the application first and then run the upgrade.

Unicode decode errors

If while using Splunk DB Connect you see Unicode decoding errors, you might not have set up the database 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 you to set up your database connection 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 open DB Connect, the RPC service attempts to bind to port 9998 by default. If port 9998 is already in use by another service, Splunk displays 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 restarts 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 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 might 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.
Using this workaround might 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. Select the Edit JDBC URL check box, 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.

DB Connect search stuck at 0% if user does not have Splunk Admin role

Verify that your Splunk license is valid.

Missing events in Splunk even though there are no errors in logs

Verify your input count, limit congestion and try scaling horizontally on other instances.

DB Connect can't communicate with task server

Verify your firewall, antivirus and proxy settings are correct and run these diagnostics:

curl -k "https://<ip_address>:<mgmpt_port>/servicesNS/nobody/splunk_app_db_connect/db_connect/java" -v -u <Splunk user>
    ps -aux | grep java OR ps -ef | grep java
    java -jar $SPLUNK_HOME/etc/apps/splunk_app_db_connect/jars/server.jar --scheme 

Unable to process JSON from the Splunk user interface

Navigate to $SPLUNK_HOME/etc/apps/splunk_app_db_connect/config/dbx_task_server.yaml and update the yaml file by adding this line: "org.glassfish.jersey": TRACE

You can add it below this line: "com.splunk.dbx.connector": ${CONNECTOR_LOG_LEVEL:-INFO}

Unable to find valid certification path to requested target

Verify you imported the public certificate of the target instance using the certificate input on the connection edit page. Verify that the certificate has not expired. From the Configuration > Settings > General page, add the -Djavax.net.debug=all option to Task Server JVM Options and Query Server JVM Options to enable more detailed logging of the validation process.

Differences between Time and Event dates during the searches

If the Time and Event dates from the search are different than you expect, it might be because DB Connect is running on a server where daylight savings time is active.

JVM extracts the time zone from the operating system based on the following:

1. JVM uses the environment variable TZ if it is set.
2. If TZ is not set, then JVM looks for the file /etc/sysconfig/clock and finds the ZONE entry.
3. If neither TZ nor ZONE is set, JVM compares the contents of /etc/localtime to the files in /usr/share/zoneinfo looking for a match. The matching path and filename under /usr/share/zoneinfo provides the time zone.

How to solve it

For Unix systems, to apply time zone rules correctly, such as daylight savings time for Java applications ( like DB Connect), make sure to apply one of the following options:

Option 1

Set the TZ environment variable to the proper time zone: $ export TZ=<Timezone>

Note: You need to restart Splunk or DB Connect after the timezone change.

Option 2

Specify the time zone for the Java application (in this case, DB Connect), by adding a system property: -Duser.timezone=<Timezone> For DB Connect, access the menu from Configuration > Settings > General, and add -Duser.timezone=<Timezone> to Task Server JVM Options.