Installing Splunk Database Connect (DBX)

DBX.md

Splunk Database Connect (DBX) Installation Guide For Ubuntu and Centos

Introduction & Scope

This guide is meant to be a set of step by step instructions for installing Splunk Database Connect 3.3.1 (henceforth referred to as DBX) on a stock install of Splunk 7.3.x and 8.0.x. OpenJDK 8 on Ubuntu 16.04 and Centos 7, and OpenJDK 11 on Ubuntu 20.04 and Centos 8. This does not cover OS or Splunk initial installation and configuration, and only covers Microsoft SQL Server's JDBC driver v8.2.x. This guide also assumes some familiarity with Splunk, and whichever OS you choose.

Required Software

• Ubuntu Server, or Centos. Available from https://ubuntu.com/download/server or https://www.centos.org/download/ respectively.
• Splunk and DB Connect 3.3.x. Available from Splunk.com and Splunkbase.
• OpenJDK (Despite the name we're installing the Java Runtime Environment, not JDK) 8 or 11
• Microsoft JDBC Driver 8.2.x Microsoft.com Download

System Preparation1

Splunk - Installing DBX

DBX installs like other plugin, it's the configuration and prerequisites on the server itself that can be troublesome.

Prep - Ubuntu 16.04 - OpenJDK(JRE) 8

1. Run sudo apt install openjdk-8-jre-headless
2. Validate Java is installed and running in server mode with java -version It should look something like:

   $ java -version openjdk version "1.8.0_265" OpenJDK Runtime Environment (build 1.8.0_265-8u265-b01-0ubuntu2~16.04-b01) OpenJDK 64-Bit Server VM (build 25.265-b01, mixed mode)

3. Set the JAVA_HOME Environment Variable
   • OpenJDK 8 is located at /usr/lib/jvm/java-8-openjdk-amd64
   • Set the variable globally by adding JAVA_HOME="/usr/lib/jvm/java-8-openjdk-amd64" to /etc/environment.

Prep - Ubuntu 20.04 - OpenJDK(JRE) 11

1. Run sudo apt install openjdk-11-jre-headless
2. Validate Java is installed and running in server mode with java -version It should look something like:

   $ java -version openjdk version "11.0.7" 2020-04-14 OpenJDK Runtime Environment (build 11.0.7+10-post-Ubuntu-3ubuntu1) OpenJDK 64-Bit Server VM (build 11.0.7+10-post-Ubuntu-3ubuntu1, mixed mode, sharing)

3. Set the JAVA_HOME Environment Variable
   • OpenJDK 11 is located at /usr/lib/jvm/java-11-openjdk-amd64
   • Set the variable globally by adding JAVA_HOME="/usr/lib/jvm/java-11-openjdk-amd64" to /etc/environment.

Prep - Centos 7 - OpenJDK(JRE) 8

1. Run sudo yum install java-1.8.0-openjdk
2. Validate Java is installed and running in server mode with java -version It should look something like:

   $ java -version openjdk version "1.8.0_262" OpenJDK Runtime Environment (build 1.8.0_262-b10) OpenJDK 64-Bit Server VM (build 25.262-b10, mixed mode)

3. Set the JAVA_HOME Environment Variable
   • OpenJDK 8 is located at /usr/lib/jvm/jre-1.8.0-openjdk
   • Set the variable globally by adding JAVA_HOME="/usr/lib/jvm/jre-1.8.0-openjdk" to /etc/environment.

Prep - Centos 8 - OpenJDK(JRE) 11

1. Run sudo yum install java-11-openjdk
2. Validate Java is installed and running in server mode with java -version It should look something like:

   $ java -version openjdk version "1.8.0_262" OpenJDK Runtime Environment (build 1.8.0_262-b10) OpenJDK 64-Bit Server VM (build 25.262-b10, mixed mode)

3. Set the JAVA_HOME Environment Variable
   • OpenJDK 8 is located at /usr/lib/jvm/jre-1.8.0-openjdk
   • Set the variable globally by adding JAVA_HOME="/usr/lib/jvm/jre-1.8.0-openjdk" ash /etc/environment.

DB Connect

Install

1. Login to the Splunk instance
2. Download DB Connect
3. Install, and restart Splunk.

Database Configuration

When Splunk returns, browse to the DB Connect app and click "Setup." This will take you to the General setup page, where all you should have to do is click "Save" and DBX will setup the basics for you.

Driver - MS SQL

1. Download the latest supported MS SQL ODBC jar listed as compatible. This is linked at the start of the guide for MS SQL.
2. Extract the appropriate Jar file for your version of the JRE to /opt/splunk/etc/apps/splunk_app_db_connect/drivers and make sure it's owner group is the Splunk user.

Identity and Connection Configuration

In this example we will be using MS SQL Server 2016, with the Adventure Works 2016 Database.

1. Confirm that DBX has picked up the driver by visiting the Settings -> Driver page. It should show that "MS-SQL Server Using MS Generic Driver", "MS-SQL Server Using MS Generic Driver With Kerberos Authentication", and "MS-SQL Server Using MS Generic Driver With Windows Authentication" are installed.
2. Next, switch to the "Databases" tab instead of Settings, and then "Identities." These are the users you connect to the databases with. Create a new Identity nick name, then type in the SQL user and password to connect to the database.
3. After creating the Identity, you'll need to switch to "Connections" and add a connection to the database using that identity. Enter your information for the name Splunk will use for the connection then the address and which set of credentials you want to use. I've enabled SSL and Read Only on my connection, but this may be different for other connections.

Finished

Testing That Everything Works

The final step is to check that everything is working. To do this go to "Data Lab" in the main application menu and then select "SQL Explorer." Choose the connection you just created and and the settings for the user. Which, in my case was Connection: AdventureWorks, Catalog: AdventureWorks2016, Schema: db_owner. In the SQL Editor I used select * FROM [Person].[Address]; as the query and got back rows.

References

https://docs.splunk.com/Documentation/DBX/Latest/DeployDBX/AboutSplunkDBConnect https://docs.splunk.com/Documentation/DBX/Latest/DeployDBX/Createandmanagedatabaseinputs

(c) 2020 Jacob Carrell, Published Under Creative Commons Attribution-ShareAlike 4.0 International License.