Splunk® DB Connect

Deploy and Use Splunk DB Connect

Download manual as PDF

This documentation does not apply to the most recent version of DBX. Click here for the latest version.
Download topic as PDF

Troubleshooting

This topic describes how to troubleshoot common Splunk® DB Connect issues.

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.

RPC server certificate expiration

When you install Splunk DB Connect and the RPC server is set to use SSL, DB Connect generates an RPC server SSL certificate with a validity period of two years at $SPLUNK_HOME/etc/apps/splunk_app_db_connect/certs/default.jks. It's a good idea to periodically check the validity of the certificate by running the following command:

   $JRE_Installation_Path$/bin/keytool -list -v -keystore $SPLUNK_HOME/etc/apps/splunk_app_db_connect/certs/default.

To renew the certificate, delete the default.jks file just before or after it is set to expire, and it will be regenerated.

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:

  • 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.

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.

Format-varchar-datetime-updated.jpg

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 re-use 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) do not use the connection pool mechanism. Instead, each command execution establishes a new connection to the database server. If this behavior causes performance degradation, you can limit access to the affected connections or configure the database to limit the number of connections allowed for the Splunk DB Connect service account. Please refer to the documentation of your database engine provider to configure this setting.

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 on poor performance when exporting data events to MySQL database, 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

PREVIOUS
settings.conf.spec
  NEXT
SQL tips and tricks

This documentation applies to the following versions of Splunk® DB Connect: 3.0.0, 3.0.1, 3.0.2, 3.0.3, 3.1.0, 3.1.1, 3.1.2


Comments

Thanks Rdagan-- I've added a new subheading in the topic to capture the issues and resolution you described.

Jrevell splunk, Splunker
March 24, 2017

How to debug HEC port issues.
In DBX 3 the architecture changed: The JDBC driver returns the results to a Java thread. The Java thread calls the HEC. HEC sends the results to SplunkD and populates the Indexers.

First hint that HEC is not working = Normal Query (found under Data Lab - SQL Explorer) returns results (that means the JDBC driver is working), but the DB Input does not populate the Indexer.  Second hint = Found under Health -> DB Input -> You see an Error.

Once you are suspecting HEC issues, go to your Splunk search and type: index=_internal 8088 (or whatever the HEC port is).  
The search will return one of these 2 events: FATAL HTTPServer - Could not bind to port 8088 (comes from splunkd.log), or [QuartzScheduler_Worker-1] ERROR .. org.apache.http.conn.HttpHostConnectException: Connect to 127.0.0.1:8088.. (comes from splunk_app_db_connect_server.log)

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

Rdagan splunk, Splunker
March 21, 2017

Was this documentation topic helpful?

Enter your email address, and someone from the documentation team will respond to you:

Please provide your comments here. Ask a question or make a suggestion.

You must be logged into splunk.com in order to post comments. Log in now.

Please try to keep this discussion focused on the content covered in this documentation topic. If you have a more general question about Splunk functionality or are experiencing a difficulty with Splunk, consider posting a question to Splunkbase Answers.

0 out of 1000 Characters