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

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. Click the RPC Server status indicator in the top navigation bar and you'll be taken to a search with the following parameters:

   index=_internal `dbx_error`

DB Connect logging

Splunk DB Connect has some logging enabled by default. To see what is logged, see "What DB Connect logs," later in this topic.

Before contacting Splunk support, you might want to enable debug logging, in case you need to provide Splunk support with DB Connect debug logs. The instructions here explain how to enable debug-level logging for the DB Connect app itself and for the RPC server.

Once you enable logging, your DB Connect activity is logged to log files and indexed to _internal. Log files are kept at $SPLUNK_HOME/var/log/splunk. The relevant log files for DB Connect are health.log, dbx2.log, and rpc.log. To view DB Connect logging activity, use a search command such as the following:

   index=_internal source=*/dbx2.log

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.

What DB Connect logs

For details about what DB Connect logs and at what level, see $SPLUNK_HOME/etc/apps/splunk_app_db_connect/default/dbx_logging.conf.

By default, DB Connect logs all executed SQL queries at INFO level to dbx2.log. Depending on the type of database and driver used and the logging configuration, SQL queries may also be logged in rpc.log and health.log. All three log files are indexed into _internal. 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.

Change logging for DB Connect

To change the logging level for DB Connect:

  1. Create a new file named dbx_logging.conf at the following path: $SPLUNK_HOME/etc/apps/splunk_app_db_connect/local. If the local directory doesn't already exist, create it. If dbx_logging.conf already exists, open it in a text editor.
  2. Copy and paste the following into the file. If these stanzas already exist, change them as indicated.
    [logger_dbx2]
    level=DEBUG
    
    [logger_health]
    level=DEBUG
    Note: You can log at any of the recognized log levels, as listed here from lowest to highest severity:
    • DEBUG
    • INFO
    • WARN
    • ERROR
    • FATAL
    Replace DEBUG in the example above with the appropriate logging level from this list.
  3. Restart Splunk Enterprise.

Change logging for the RPC server

  1. From $SPLUNK_HOME/etc/apps/splunk_app_db_connect/default, make a copy of the log4j.properties file, and place it in $SPLUNK_HOME/etc/apps/splunk_app_db_connect/local.
  2. Open the copied log4j.properties file with a text editor.
  3. Locate the following two lines:
    log4j.logger.com.splunk.dbx2=INFO, RPCLOG
    log4j.logger.com.splunk.logger.HealthLogger=INFO, HEALTHLOG

    Replace INFO with DEBUG, so that the lines appear as follows:

    log4j.logger.com.splunk.dbx2=DEBUG, RPCLOG
    log4j.logger.com.splunk.logger.HealthLogger=DEBUG, HEALTHLOG
  4. Save the file, and then restart Splunk Enterprise.

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 Settings in the top navigation bar and then the Driver tab to view the driver status screen. You can also search the _internal index for your specific driver's .JAR file. For example:
    index=_internal source=*dbx2* mysql-*.jar
  • Are there any other Java-based connectors on the system that might be interfering with the JDBC driver that you've specified DB Connect to use?
  • 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.

Troubleshoot the dbxquery command

If you are having trouble using the dbxquery search command—for example, if the results returned are not what you expect—you should use a tool such as DBVisualizer to test your dbxquery queries. Be aware, however, that any queries that you test using a database query tool should first be wrapped in the following inline view SQL statement, where %s represents your query as used with dbxquery:

SELECT * FROM (%s) t

Wrapping your query in this inline view before running it with your query tool makes the query identical to what dbxquery sends to your database. For more information, see "Database inputs or lookups with inline views don't work as expected," below.

Database inputs or lookups with inline views don't work as expected

DB Connect v2 uses inline views, or query wrapping, to enable you to use an aliased column name in a WHERE, GROUP BY, HAVING, or ORDER BY clause. Inline views allow DB Connect to reference a complicated subquery as a table. The wrapped subquery executes prior to the containing query.

You should disable query wrapping if any of the following applies to your database inputs or lookups:

  • They include queries that use inline views.
  • You prefer to use a WITH clause in your queries.
  • Your database materializes inline views. For example, MySQL 5.6 does this.

To turn off query wrapping, first open the db_connections.conf file at $SPLUNK_HOME/etc/apps/splunk_app_db_connect/local/db_connections.conf. If either the local directory or the db_connections.conf file doesn't exist, create it.

To enable or disable query wrapping for all connections:

  • Add the following [default] stanza to local/db_connections.conf, setting enable_query_wrapping to 0 to disable, or to 1 to enable.
[default]
enable_query_wrapping = 0

To force query wrapping for a specific connection:

  • In the stanza in local/db_connections.conf for the connection you want to force-enable query wrapping, add the following setting:
enable_query_wrapping = 1

To disable query wrapping for a specific connection:

  • In the stanza in local/db_connections.conf for the connection you want to prevent from using query wrapping, add the following setting:
enable_query_wrapping = 0

Queries containing AS do not change column names as expected

When using the AS keyword in a query such as the following:

  SELECT xyz AS abc.xyz FROM jkl

DB Connect returns the column name as xyz instead of as abc.xyz. This is because the JDBC specification states that the AS keyword specifies the column's suggested title, not its actual name. DB Connect always returns the actual name of the column. This behavior, though correct according to the JDBC specification, differs from that of DB Connect v1.

To change the default behavior so that the column's suggested title appears instead of the column name, do the following:

  1. Open the file $SPLUNK_HOME/etc/apps/splunk_app_db_connect/local/db_connection_types.conf. If either the local directory or the db_connection_types.conf file does not already exist, create it.
  2. From $SPLUNK_HOME/etc/apps/splunk_app_db_connect/default/db_connection_types.conf, copy the stanza for the database type you're connecting to, and paste it into local/db_connection_types.conf.
  3. Inside local/db_connection_types.conf, append the corresponding UrlFormat parameter for your database with the following:
    ?useOldAliasMetadataBehavior=true
  4. Save the file, and then restart Splunk Enterprise for the changes to take effect.
Note: Because this flag causes behavior that deviates from the JDBC specification, it has not been tested extensively, and may not work for all databases.

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:

   $JAVA_HOME/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 two options:

  • Use the default sourcetype in the input config. Leave it blank because Splunk DB Connect uses dbmon:kv as the sourcetype (in the normal case where you're using the key-value output format). But, if you put something custom in the sourcetype field, you must tell Splunk Enterprise how to line break for that sourcetype. Copy the props.conf settings for the default stanzas; specifically, add:
    SHOULD_LINEMERGE = false

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 parameters step when creating or editing an input, next to Timestamp, choose the Choose Column setting. Then, click the + button next to Specify Timestamp Column. Next, click the Java Date option, and then specify the timestamp using a Java SimpleDateFormat pattern so DB Connect can obey the timestamp output format setting. For example, if the database column EVENT_TIME has 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 inputs.conf.

output.timestamp = true
output.timestamp.column = EVENT_TIME
output.timestamp.parse.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. If it does not come back cleanly, delete the state files and restart the Splunk system again:

$SPLUNK_HOME/var/splunk_app_db_connect/
session.bak  session.dat  session.dir 

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), nor does it support database connection pooling.
  • 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.

Can't use Windows authentication for Microsoft SQL Server with Microsoft JDBC Driver for SQL Server

To connect to Microsoft SQL Server with DB Connect using the Microsoft JDBC Driver for SQL Server and Windows authentication, follow these instructions.

Note: These instructions assume that you're running Splunk Enterprise on Microsoft Windows. Using Windows authentication to connect to SQL Server only works with Splunk Enterprise running on Windows.
  1. From the Microsoft JDBC Driver 4.0 for SQL Server download, locate the sqljdbc_auth.dll file. This file is at the following path, where <region_code> is the three-letter region code (for example, enu for U.S. English), and <architecture> is the processor type (x86 or x64): Microsoft JDBC Driver 4.0 for SQL Server\sqljdbc_4.0\<region_code>\auth\<architecture>\sqljdbc_auth.dll
  2. Copy the sqljdbc_auth.dll file to C:\Windows\System32 on your Splunk Enterprise server.
  3. From the Windows Control Panel, go to Services, and then get properties on Splunk Service.
  4. Click the Log On tab, and then change the Log on as setting from the Local System account to that of the logged on domain user.
    Note: The domain user should have sufficient privileges to access the SQL Server instance.
  5. Save your changes, and then restart the Splunk Enterprise server for the changes to take effect.

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, create a new file called db_connection_types.conf and place it in $SPLUNK_HOME/etc/apps/splunk_app_db_connect/local. If this file already exists, open the existing file.
  3. Add the following entry to the db_connection_types.conf file. Replace the <host>, <port>, and <database> placeholders with their actual values in your setup.
    [oracle]
    jdbcUrlSSLFormat = jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=<host>)(PORT=<port>))(CONNECT_DATA=(SERVICE_NAME=<database>)))
  4. Restart Splunk Enterprise.
  5. Create a new connection, as described in Create a database connection. Populate the Host, Port, and Database fields according to your database setup. From the Database Types pop-up menu, choose Oracle.
  6. Select the Enable SSL checkbox.
  7. Validate the connection by clicking Validate, and then save it.

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.

Database inputs are getting disabled

If the database inputs are getting disabled, one of the possible reason is that the Splunk DB Connect has exceeded the number of retries defined in max_retries. Splunk DB Connect will disable the database inputs if the number of retries has been exceeded.
If you want to change the auto-disable behavior, you need to set the auto_disable=false in inputs.conf under $SPLUNK_HOME/etc/apps/splunk_app_db_connect/local.

See inputs.conf.spec for the detailed description of max_retries and auto_disable.

Renaming rising_column breaks database input

Renaming the rising_column field value causes a paradox. If you rename rising column, DB Connect returns an exception stating that no such column exists in the original table. If you set rising_column to the unrenamed column name that is in the table, DB Connect returns an exception stating that there is no such field in the final output. The best workaround is simply to avoid renaming the rising_column field.

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.

Resource pooling isn't working

If, once you’ve configured resource pooling, it doesn’t seem to be working, try the following:

  • Is your input or output using the local RPC server instead of the pool? Check the resourcepool.conf file you created to make sure the syntax is correct according to the example in Step 4 of the documentation. Also check the Select resource pool pop-up menu in the final step of the input or output to make sure it is still set to the pool you expect.
  • Make sure the resource pool members that you configured in Steps 1 and 2 of the documentation are up and accessible in the distributed search interface.
  • In your browser or using a cURL command, try to access the following resource pool health endpoint, where https://yourserver:8089/ represents the URI of the resource pool master node:
    https://yourserver:8089/servicesNS/nobody/splunk_app_db_connect/db_connect/poolhealth 

    Each node in the pool should have a corresponding stanza, and they should each show alive=1.

DB Connect isn't bringing in records before a certain date

DB Connect abides by the MAX_DAYS_AGO setting in the props.conf file. When set, MAX_DAYS_AGO defines the number of days in the past, from the current date, that a date is considered valid. This means that any imported records with timestamps before today's date minus MAX_DAYS_AGO will not be indexed.

The default setting for MAX_DAYS_AGO is 2000 days, so if you are using DB Connect to consume database data that is older than that, you should override the default MAX_DAYS_AGO setting as follows: Modify or create a props.con stanza in the app where the dbinput is defined that matches the source or sourcetype for the input in question.

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 a timestamp value of null are skipped during indexing

If your database input has a timestamp column that has been specified as being in Java date format, and Splunk Enterprise encounters a row during indexing that has a null timestamp, Splunk Enterprise will skip that row.

The following screen shot is of the Configure Timestamp Column window from the third step of database input setup. The highlighted column is the timestamp column. Notice that the last row visible on this screen has a timestamp of NULL. When the input is complete and Splunk Enterprise indexes the data from the database, that row will not be indexed. However, all other rows, as long as their timestamps are set to a non-null value, will be indexed.

Dbx timestampnull.png

Searches using lookups are failing

If you use the name of a database lookup in a search query, it will fail unless you include the lookup prefix, db_connect_.

When you create a new lookup, the name you give your lookup is prefixed with db_connect_ when you save the lookup. When you use your lookup in a search query, you must reference it as: db_connect_<lookup_name>.

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.

Incorrect rising column highlighted in DB input UI

When viewing database input preview results within DB Connect, you might see the incorrect rising column highlighted. This can happen when you've created an input in DB Connect version 2.0.6 or earlier.

To fix this issue, edit the database input and re-save it.

DB Connect won't run when minified mode is not enabled

"Minified mode" describes the Splunk Enterprise mode wherein the static JavaScript files for modules are consolidated and minified. Minified mode is turned on by default using the minify_js attribute (set to True) in the web.conf file, and improves client-side performance by reducing the number of HTTP requests and the size of HTTP responses.

Setting minify_js to False turns off minified mode. DB Connect is not supported for use when the minify_js attribute has been set to False.

"No space left on device" error message

You can adjust the amount of space that DB Connect's cache file, the indexed disk auxiliary cache file rsCache.data, takes up on disk. Experimenting with the settings described here can be useful if you see an error message in rpc.log such as the following:

2015-11-04 10:30:09 ERROR IndexedDiskCache:561 - Region [rsCache] Failure updating element, key: 36e0e13c-237f-4076-9c74-4e370b9f4fe1 old: null
java.io.IOException: No space left on device
	at sun.nio.ch.FileDispatcherImpl.pwrite0(Native Method)
	at sun.nio.ch.FileDispatcherImpl.pwrite(FileDispatcherImpl.java:66)
	at sun.nio.ch.IOUtil.writeFromNativeBuffer(IOUtil.java:89)
	at sun.nio.ch.IOUtil.write(IOUtil.java:65)
	at sun.nio.ch.FileChannelImpl.writeInternal(FileChannelImpl.java:745)
	at sun.nio.ch.FileChannelImpl.write(FileChannelImpl.java:731)
	at org.apache.commons.jcs.auxiliary.disk.indexed.IndexedDisk.write(IndexedDisk.java:192)
	at org.apache.commons.jcs.auxiliary.disk.indexed.IndexedDiskCache.processUpdate(IndexedDiskCache.java:542)
	at org.apache.commons.jcs.auxiliary.AbstractAuxiliaryCacheEventLogging.updateWithEventLogging(AbstractAuxiliaryCacheEventLogging.java:64)
	at org.apache.commons.jcs.auxiliary.disk.AbstractDiskCache.doUpdate(AbstractDiskCache.java:750)
	at org.apache.commons.jcs.auxiliary.disk.AbstractDiskCache$MyCacheListener.handlePut(AbstractDiskCache.java:620)
	at org.apache.commons.jcs.engine.AbstractCacheEventQueue$PutEvent.doRun(AbstractCacheEventQueue.java:325)
	at org.apache.commons.jcs.engine.AbstractCacheEventQueue$AbstractCacheEvent.run(AbstractCacheEventQueue.java:255)
	at org.apache.commons.jcs.engine.CacheEventQueue$QProcessor.run(CacheEventQueue.java:280)

This error message is generated by the Java Caching System (JCS). The JCS' settings are stored in DB Connect's cache.ccf file, which is located at $SPLUNK_HOME/etc/apps/splunk_app_db_connect/default/cache.ccf. The JCS controls cache size by limiting either the cache's internal key count or its key size. To do this, adjust the values of the following keys:

  • To limit the count of keys, set DiskLimitType to COUNT, and then set MaxKeySize to the maximum number of keys you want the cache to store.
  • To limit the size of keys, set DiskLimitType to SIZE, and then set KeySize to the maximum size in kilobytes (KB) of keys stored in the cache file.
Note: Do not directly edit the cache.ccf file stored in the default directory. Copy the file to $SPLUNK_HOME/etc/apps/splunk_app_db_connect/local. Edit the copied cache.ccf file.

By experimenting with the maximum number or maximum size of keys, you can try to control the size of the cache file.

For more information on the JCS indexed disk auxiliary cache and all of its applicable settings, see Apache Commons JCS: Indexed Disk Auxiliary Cache.

Launching DB Connect results in a blank page

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 a blank page.

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

  1. Go to $SPLUNK_HOME/etc/apps/splunk_app_db_connect/local. If the local directory doesn't already exist, create it.
  2. Open the inputs.conf file. If it doesn't already exist, create it.
  3. Add the following stanza, if it doesn't already exist, setting the port as appropriate:
    [rpcstart://default]
    port = 9085
  4. Restart Splunk Enterprise.

Large cache folder is created

DB Connect 2.1.3 and earlier may create a cache directory within the main Splunk Enterprise directory, and may, in rare cases, cause the cache file within that directory to swell to tens of gigabytes in size. The directory is at the following path:

$SPLUNK_HOME/rs_swap

Upgrading to DB Connect 2.2.0 or later fixes this issue, and also negates the need for the rs_swap cache directory. Once you have upgraded to DB Connect 2.2.0 or later, you can safely delete the $SPLUNK_HOME/rs_swap directory.

Incorrect timestamp behavior

Consider the following scenario: You create a database input and set the Timestamp setting to Current Index Time and the Output Timestamp Format setting to Epoch time, but the input returns data with a timestamp column. Splunk Enterprise includes the index time when it indexes the database records as events, but it assigns them to a timestamp column, and not as _time, as expected.

This is a known issue. To work around it, create a database input as normal, but in the final step, "Metadata," specify a sourcetype field value to assign to indexed data by completing the Sourcetype field. Once you've saved the input, create a new props.conf file at $SPLUNK_HOME/etc/system/local/. (If one already exists, work inside the existing file instead.) Add a new stanza with the name of the sourcetype you assigned the input, and add the setting DATETIME_CONFIG = NONE to it. For instance, for sourcetype my_sourcetype, you'd add the following:

[my_sourcetype]
DATETIME_CONFIG = NONE
Setting DATETIME_CONFIG to NONE disables the timestamp extractor for this sourcetype, thereby enabling the indexer to correctly assign the index time to the _time attribute for each event.

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.

RPC server errors with search head clustering

When using DB Connect with search head clustering, you might see error messages such as the following logged in dbx2.log:

03/24/2016 16:16:15 [WARNING] [dbx_logging.py] action=fail_to_load_dbx_logging_conf cause=[Errno 2] No such file or directory: '/usr/local/eserv/splunk/etc/apps/splunk_app_db_connect/bin/dbx2/../../local/dbx_logging.conf'
03/24/2016 16:16:16 [CRITICAL] [rpcstart.py] RPC server has been terminated abnormally with error [org.eclipse.jetty.server.Server@1b68b9a4 - STARTED].

This is caused by an incompatibility between DB Connect 2.1.3 and earlier and Splunk Enterprise 6.4.0 and later.

To fix this issue, upgrade DB Connect to version 2.2.0 (or later).

Cannot query a stored procedure

Splunk DB Connect does not support querying stored procedures. For an unsupported workaround, see this Splunk Answers post. Be aware that this workaround has not been tested and is not supported.

PREVIOUS
settings.conf.spec
  NEXT
SQL tips and tricks

This documentation applies to the following versions of Splunk® DB Connect: 2.2.0


Comments

@drfonck: That is still true. Thank you for pointing out the omission.

Mtevenan splunk, Splunker
May 13, 2016

Earlier versions of this doc said

"Note: These instructions assume that you're running Splunk Enterprise on Microsoft Windows. Using Windows authentication to connect to SQL Server only works with Splunk Enterprise running on Windows."

Under "Can't use Windows authentication for Microsoft SQL Server with Microsoft JDBC Driver for SQL Server"

Dfronck
May 11, 2016

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