How Splunk DB Connect works

Splunk DB Connect is an add-on that bridges Splunk Enterprise with relational databases via 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.

Use the add-on to configure database queries and lookups in minutes via the Splunk Enterprise interface. By installing Splunk DB Connect, you can broaden the range of data inputs available to Splunk Enterprise, because they can now include your relational databases. Splunk DB Connect can also do the reverse—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.

This topic provides an overview of how Splunk DB Connect works.

Setup

Before you can get started, you need to set up Splunk DB Connect. Download the Splunk DB Connect add-on, 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, you should install the add-on on a search head.

All DB Connect instances require Java Runtime Environment (JRE) version 8 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 overview.

Identities

After installing prerequisites and Splunk DB Connect, you must create an identity. An identity is what defines the database user through which Splunk Enterprise will connect to your database(s). It is comprised of the username and password that you use to access your database(s). 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 will be able to use the identity.
• Read-write access means that Splunk Enterprise roles will be able to use and modify the identity.
  

By default, the Splunk Enterprise admin and db_connect_admin roles have read-write access to a new identity (sc_admin role for the cloud customer), 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

Once you've created the necessary identities for your database environments, you'll need to create a connection. A connection is the information necessary to connect to a specific database. It is comprised 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 Splunk Enterprise roles have read, read-write, or no access to the connection. Read access means that Splunk Enterprise roles will be able to use the connection. Read-write access means that Splunk Enterprise roles will be able to use and modify the connection. By default, the Splunk Enterprise "admin" and "db_connect_admin" roles have read-write access to a new connection, the "db_connect_user" role has read access, and all other roles have no access.

It's important to remember that, 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; it will use whatever identity you assigned it. This enables Splunk Enterprise users to work with database contents without knowledge of 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). DB Connect also enables you to 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 should be a batch input (everything dumped in at once), or whether the input has a rising column (a column that is continuously rising, 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 execute the query.

Once 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 to searches, reports, and alerts.

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

Search

Once you've set up identities, connections, and database inputs, and Splunk Enterprise has indexed your data, you are ready to search. Indexed data obtained via Splunk DB Connect from relational databases is searchable just like the rest of your Splunk Enterprise data. To get started, see Searching and Reporting.

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 includes functionality for you to enrich and extend the usefulness of your Splunk Enterprise data through interactions with your external database. Database lookups give you real-time contextual information from a database during ad hoc explorations of data in Splunk.

An example of this functionality would be 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. Therefore, if you have an event where customer_id="24601", the lookup would add customer_name="ValJean, Jean" to that event.

DB Connect V3 provides the dbxlookup command for performing lookups by using remote database tables as lookup tables. Use dbxlookup to enrich your indexed events with the information stored in external databases.

• 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 database connection health.