Configuring Splunk DB Connect

Prerequisites

Before installing Splunk DB Connect, it is important to consider the following:

• Splunk Enterprise version. Splunk DB Connect v3.6.0 is compatible with Splunk Enterprise 7.2.0 and above, while later versions of Splunk DB Connect only support Splunk Enterprise 8.1 and above due to the version of Python available. If you are a Splunk Cloud Platform customer, then you need Splunk DB Connect version 3.7.0 or above.
• Installation location. Splunk DB Connect can be installed on a heavy forwarder or a search head.

• OS and version being used. Splunk DB Connect can be installed on Windows or Unix/Linux as long as Splunk Enterprise supports the installation of a heavy forwarder there.
• Ports. The correct ports need to be opened between the Splunk DB Connect Splunk platform instance and the database server. For instance, Microsoft SQL Server’s default is 1433 and Oracle’s is 1521.
• Connection configuration for the database. You'll need to configure the correct type - for example, SQL Server allows for domain user authentication or DB-internal and name of instance or default instance. You'll also need to select the right listener details on the required resources, as appropriate for your environment.
• User configuration. Users need to be created with appropriate privileges to connect to the database. For example, if you want to read the data from a table/view into Splunk Enterprise, then users need read-only access to the table or view. If they need to output data from the Splunk platform to the database, then they need to have insert/update access on the required resources.
• Database receiving connections. Check that the database can receive connections from the computer that the Splunk platform is running on.

• Splunk Deployment Server compatibility. Splunk DB Connect does not support Splunk Deployment Server, and it is not possible to install it on a universal forwarder as it requires SplunkWeb and Python to run.
• Splunk DB Connect compatibility. Splunk Cloud Platform supports Splunk DB Connect on the search head, but there is no support for the dbxquery command. In most cases, you will not want to open a port from Splunk Cloud Platform to your database systems, so installing Splunk DB Connect in on-premises Splunk Enterprise heavy forwarders and then indexing the data up to Splunk Cloud Platform is the preferable method.
• Authentication. When using SQL Server, SQL Server authentication is recommended if the Splunk Enterprise instance is on Linux, but Windows authentication is advised if the Splunk Enterprise instance is on Windows, although SQL Server authentication can also be used in that case.

Components

The following are the necessary components required for setting up Splunk DB Connect:

• Splunk DB Connect. Download this app from Splunkbase and review the release notes on Splunk Docs. It's recommended that you download the latest version.
• Java Runtime Environment (JRE) or Java Development Kit (JDK). Download and install one of these. JDKs include a JRE and tools for Java development, such as compilers. JREs are the simplest component you'll need. Splunk provides a selection of JREs and JDKs for various operating systems. It's recommended to use OpenJRE, which is free and does not require licensing.

• Java Database Connectivity (JDBC) database driver. This is a Java library (.JAR file) used by Splunk DB Connect in the JRE to access the database. You can find a page with links to pre-packaged drivers for supported databases here. It is recommended to download the appropriate driver for your database from this source, as it can be challenging to find the correct version of the driver otherwise. For this example process, the SQL Server driver used can be downloaded from this Splunkbase page.

Creating a connection

The overall process for creating a connection on Splunk Enterprise is as follows:

1. Install the JVM and make sure that the JAVA_HOME and PATH are set up.
2. Install Splunk DB Connect.
3. Install the DB Driver.
4. Configure Splunk DB Connect with Identities and Connections.
5. Test.

The overall process for creating a connection on Splunk Cloud Platform is as follows:

1. Ask Splunk Cloud Platform support to install Splunk DB Connect, which should include a JRE and probably all of the drivers (depending on the request).
2. Install the DB Driver (if not already installed by Splunk Cloud Platform support).
3. Configure Splunk DB Connect with Identities and Connections.
4. Test.

Creating an input

1. In the Splunk DB Connect app, go to Data Lab > Inputs and click New Input to go to the New Input screen.
2. Select the Connection to use. In this case, SQLServer is used.
3. Select the Catalog dropdown to choose the database to work with.
4. Select the Schema. This is variable, but many applications use the “dbo” schema.
5. Select the Table to read from. This triggers the Splunk platform to read the database table and display the contents. This screenshot shows two demonstration rows:

   

6. If you already have a SQL query, you can insert it into the SQL Editor panel and make edits.
7. Click the Execute SQL button.

   You must do this after, and not before, any edits you make in the SQL Editor, otherwise you will not be able to progress to the next stage.

8. To the right side of the SQL Editor, as shown in the screenshot above, select the Input Mode. This is usually “Event” for results with any non-numeric values.
9. Select the Input Type, either “Batch” or “Rising”.
   • For Batch, you are importing all of the rows in a table within a time period. In the example shown in the screenshot under step 5, “Batch” is used. In the SQL, make sure that when the SQL is run, it captures only the data that is present in the table, indexed on the date (as shown in the example, where only yesterday’s data is being queried).
   • For Rising, there needs to be a rising column such as the IDField shown in the step 1. For the Rising input type, ensure that the SQL looks like the screenshot below. Here, the question mark is substituted with the value in the Checkpoint and Splunk keeps a record of the latest checkpoint value when it runs the SQL query.

     Make sure that you include a filter which includes the “?”. In this example, the Rising Column is set to “IDField” and Checkpoint Value as 0 to get this output. If you set the Checkpoint Value to be 3, then you would only have the single IDField = 4 row, and Splunk platform will start running the input from line 4.

   

10. Set the Timestamp. The best way to do this is by importing the date in a field and selecting which one to use. If there is no suitable date field, then you can use the Current Index time.
11. Change the Query Timeout, as shown in the screenshot below.

    Some databases can be subject to lag and performance issues under extreme circumstances, so increasing the default value of 30 seconds to a higher value, such as 120, is recommended.

    

12. Click Next. Give the output a name, an optional description, and select the Application, as shown in the screenshot below. When running on a heavy forwarder, you should keep the output in the Splunk DB Connect app so that the inputs are kept in one app. When running on a search head, the output can be placed into other apps so that they can be retained in the other app’s security context.

    

13. Add the Parameter Settings, as shown in the screenshot below.
    • The Max Rows to Retrieve can be used to limit the number of rows imported with each query. You should leave this at 0 (unlimited) in most cases. If an input is taking in too much data at a time, then you can set a limit.
    • The Fetch Size is by default 300 and that means that the data is brought back to the Splunk platform in pages of up to 300, which are then imported into the Splunk platform. This can be bulky, in the case of a long low, so if there are performance issues on the Splunk server, this can be reduced or increased.
    • The Execution Frequency can be set  in crontab format (in the screenshot below, this input runs on the hour, every hour of every day) or in the form of a number of seconds.

      

14. Set up the Metadata, as shown in the screenshot below. Host and Source are optional, but Source Type and Index are not.

    

    • Set the “Host” to be the name of the SQL Server machine, rather than the host that is running Splunk platform (which is the default), as this provides better indication of where the data is originating from.
    • Do not use built-in source types. Create a new one, so that any fields that are created for this source can be assigned only to this source and not to other sources.
    • Always assign an index and do not use the main/default index. This is because the main index is a default index for data that has nowhere else to go because the input/props/transforms is incorrectly configured. Also, if you place data in the main/default index, access to the data in that index might be mixed in with data from other data sources. Since access to data is run on a per-index basis, you will see both good and bad data, thus making your data results "dirty". Finally, if all data is placed into just the main/default index, you will either have access or not. There is no option to restrict general data from PII, HIPAA, or GDPR data sources, for example.
15. Click Finish to complete your input.
16. Now you can start searching for the data in the Splunk platform. Go to the Searching & Reporting app and run a search to see the data that has been brought in. Notice in the screenshot below that the data is in key-value pairs.

    

Creating an output

In this example, the changes to the Splunk DB Connect configuration are output to a configuration tracker (which is a new feature that was introduced in version 9, so this section will not work in version 8 and below) to a database table called “DBConnectChanges” on the SQL Server database.

CREATE TABLE [dbo].[DBConnectChanges](
[eventtime] [datetime] NOT NULL,
[action] [nchar](10) NOT NULL,
[stanza] [nchar](255) NOT NULL,
[old_value] [nchar](1024) NULL,
[new_value] [nchar](1024) NULL
) ON [PRIMARY]
GO

1. Construct the table to receive the data with this SQL script.

   Ensure that the table fields have the right data type for each output, such as datetime, not date, for instance. You'll also need to make sure that the table is completed before proceeding further, as you may need to restart the Splunk platform after the catalog of tables is ingested into Splunk DB Connect.

2. Construct a search that creates the data that you needed to output to the table. Make sure that the fields output are in the right format for the database (see eventtime outputting a SQL datetime format, for instance).

   index="_configtracker" "data.path"="*/splunk/etc/apps/splunk_app_db_connect/local/*"
   | fields + data.action, data.changes{}.properties{}.new_value, data.changes{}.properties{}.old_value, data.changes{}.stanza, datetime
   | eval eventtime = strftime(strptime(datetime,"%m-%d-%Y %H:%M:%S.%3N %Z"), "%Y-%m-%dT%H:%M:%S")
   | rename data.action as action, data.changes{}.properties{}.new_value as new_value, data.changes{}.properties{}.old_value as old_value, data.changes{}.stanza as stanza
   | sort +eventtime
   | table eventtime, action, stanza, old_value, new_value
   

3. Open Splunk DB Connect, go to Data Lab > Outputs and click New Output.
4. Add the search that you created above and make sure that you have data output, then click Next.
5. Select the Connection, Catalog, Schema, and Table to output to.

   Depending upon the speed and size of your environment, selection of the resources may take some time.

6. Click Next to open the Fields Mapping screen.
7. Map the fields in the search to the fields in the database.
8. If your data has a key and there is a change that you will update as well as insert data, then select Enable Upsert and then select the column in the search as the key. As this example data is generated by datetime, this isn't needed, so you can proceed to the next step.

   

9. Click Next. Here, give the output a name, an optional description, and select the Application.
   • When running on a heavy forwarder, you should keep the output in the Splunk DB Connect app so that the inputs are kept in one app.
   • When running on a search head, the output can be placed into other apps so that they can be retained in the other app’s security context.

     

10. Add the Parameter Settings. The default Query Timeout is 30 seconds and the Batch Size is 1000. If the output is to be run on schedule, then set it, using either the interval in seconds or a crontab expression.

    

11. Click Finish to save the output.
12. Wait for the output to run and then run a SQL command to look for the data:

    

Next steps

If you need additional help with this guidance, UK-based Somerford Associates can help. Somerford Associates is an award winning Elite Partner with Splunk and the largest Partner Practice of Consultants in EMEA. We protect data, demonstrate that it is being managed effectively and derive greater value, by providing real-time insights to support effective decision making. With our specialist knowledge, skills, experience and strong reputation for enabling digital transformation at scale and at pace, we provide full delivery, including design, implementation, deployment and support.

In addition, these Splunk resources might help you understand and implement the recommendations in this article:

• Splunk Docs: About Splunk DB Connect
• Splunk Docs: How Splunk DB Connect works
• Splunk Docs: Splunk DB Connect Release notes
• Splunk Docs: Splunk DB Connect Prerequisites and Java runtime environment requirements
• JDK 18 Releases: https://jdk.java.net/18/
• Splunkbase: Splunk DBX Add-on for Microsoft SQL Server JDBC

The user- and community-generated information, content, data, text, graphics, images, videos, documents and other materials made available on Splunk Lantern is Community Content as provided in the terms and conditions of the Splunk Website Terms of Use, and it should not be implied that Splunk warrants, recommends, endorses or approves of any of the Community Content, nor is Splunk responsible for the availability or accuracy of such. Splunk specifically disclaims any liability and any actions resulting from your use of any information provided on Splunk Lantern.