How Splunk DB Connect works

Splunk DB Connect is an add-on that bridges Splunk Enterprise with relational databases through Java Database Connectivity (JDBC). It enables Splunk Enterprise to connect to and exchange data with databases such as MySQL, Microsoft SQL Server, Informix, DB2, and many others, enriching your Splunk Enterprise data by combining it with data that was previously only available to you directly from those databases.

Splunk DB Connect can also send Splunk Enterprise data back for storage in your relational database tables. Splunk DB Connect enriches and combines unstructured data with structured data, which allows users to cross-reference, augment, and correlate between events in machine logs and external databases.

Set up Splunk DB Connect

To set up Splunk DB Connect, download Splunk DB Connect from Splunkbase, and then follow the instructions in either the single-server or distributed deployment installation topics. You can use Splunk DB Connect on a heavy forwarder to support continual data gathering or output. For more interactive use, including lookups, install the add-on on a search head.

All DB Connect instances require Java Runtime Environment (JRE) version 11 or higher in order to enable JDBC. DB Connect uses a remote procedure call (RPC) server to manage communications with the Java subsystem. You must also install a Java Database Connectivity (JDBC) driver so that Splunk Enterprise can communicate with your databases. Review Install database drivers for more information and a listing of tested drivers.

A checklist of steps required for setting up Splunk DB Connect is available at Installation and setup overview for Splunk DB Connect.

Identities

After you set up Splunk DB Connect, you must create an identity. An identity, which consists of a username and password, defines the database user through which Splunk Enterprise connects to your database. A single identity can be used by many connections, so that service accounts can be easily shared across multiple systems. This makes regular password changes easier to support.

Be aware that these are database credentials, and are not the same as your Splunk Enterprise credentials. When you configure an identity, you can specify the Splunk Enterprise roles that have read, read/write, or no access to the identity.

• Read access means that Splunk Enterprise roles can use the identity.
• Read-write access means that Splunk Enterprise roles can use and modify the identity.
  

By default, the admin, sc_admin, and db_connect_admin roles have read/write access to a new identity, the db_connect_user role has read access, and all other roles have no access.

For more information about setting up and using identities, see Create and manage identities.

Connections

After you create the necessary identities for your database environments, you need to create a connection, which contains the information necessary to connect to a specific database. It consists of the address of your database (the host name), the database's type, and the name of the database.
When you configure a connection, you can specify which roles have read, read-write, or no access to the connection.

While an identity can be used by several connections, each connection can only be assigned a single identity. When you create a new connection, you specify which identity you want to use with the connection. As you use Splunk DB Connect, you'll only need to specify the connection to use, and it will use whatever identity you assigned it. Users can then work with database contents without knowing the database credentials stored in the identity.

For more information about setting up and using connections, see Create and manage database connections.

Database inputs

A database input enables you to retrieve and index data from a database using Splunk Enterprise. It's where you can start to narrow down the data you want to index by building a database query. You can either specify the catalog, schema, and table you want to access in Automatic Query Mode, or enter a custom SQL query against the database in Editor Query Mode. You can also preview the results of your query, so that you know that your query is working the way you expect.

Several parameters also help Splunk Enterprise retrieve your data efficiently and in exactly the way you want. For instance, you can specify whether the input is a batch input, or whether the input has a rising column, such as an identifier number or timestamp. You can also specify whether to retrieve all rows or a certain number of rows, identify a timestamp format, and set how often to run the query.

After you create your database input, Splunk Enterprise uses DB Connect to query your database, and then indexes your data given the parameters you specified. Indexed data is available for searches, reports, and alerts.

For more information about setting up and using database inputs, see Create and manage database inputs.

Search

After you set up identities, connections, and database inputs, and Splunk Enterprise has indexed your data, you are ready to search. Indexed data obtained through Splunk DB Connect from relational databases is searchable just like the rest of your Splunk Enterprise data. To get started, see Search and reporting in the Splunk Enterprise Overview manual.

Some data is not suitable for indexing, but can be searched directly from Splunk Enterprise. DB Connect provides the dbxquery command for querying remote databases and generating events in Splunk Enterprise from the database query result set. The dbxquery command supports SQL queries and stored procedures that have been defined in your database. See dbxquery for command documentation.

For more information about searching in Splunk Enterprise, see the Search Manual.

Database outputs

Splunk DB Connect also enables you to write Splunk Enterprise data back to your relational database using database outputs. You can do this interactively from a search head or by setting up an automatic output from a heavy forwarder. Both cases assume that you are connecting to the database using an identity with sufficient write permissions.
DB Connect V3 provides a dbxoutput search command for running database outputs that you've defined in DB Connect. There is also a predefined custom alert action for using the dbxoutput command.

• For directions on how to create outputs in DB Connect, see Create and manage database outputs.
• To learn more about Alert Actions in Splunk Enterprise, see Custom alert actions overview.

Database lookups

Splunk DB Connect allows you to interact with your external database. Database lookups give you real-time contextual information from a database during ad hoc search in the Splunk platform.

For example, you might create a lookup that takes a customer ID value in an event, matches that value with the corresponding customer name in your external database, and then adds the customer name to the event as the value of a new customer_name field. If you have an event where customer_id="24601", the lookup adds customer_name="ValJean, Jean" to that event.

Use the dbxlookup command to perform lookups by using remote database tables as lookup tables.

• For detailed description on how to use the dbxlookup command, see dbxlookup.

• For instructions on creating lookups in DB Connect, see Create and manage database lookups.

Health monitoring

Splunk DB Connect includes a health dashboard that allows you to monitor numerous aspects of your database connections and transactions with Splunk Enterprise.

For more information about using the health dashboard, see Monitor Splunk DB Connect health.