Connecting Splunk Enterprise to SQL Server via JDBC Driver

Splunk is a great operational intelligence and data processing platform that offers a great log analysis. It’s a powerful engine that lets you monitor, search, investigate, visualize and report on what’s happening with your IT infrastructure in real time.

There are times where an organization might want to connect to external database from Splunk to get data from a database or write Splunk enterprise data in to databases etc. Fortunately, Splunk has a DB Connect app that you can install in your Splunk Enterprise, which uses a JDBC driver in the background, to perform any operations against the database.

In this tutorial, we will be going through on how you can connect your SQL Server database using Splunk DB Connect app that leverages Progress DataDirect SQL Server JDBC driver. Use this driver when you need advanced features such as improved performance or Windows Authentication from Unix/Linux.

Make sure you have following installed and configured properly, before you go ahead with the tutorial.

1. Have Splunk Enterprise installed and running.
2. Installed the Splunk DB Connect app in Splunk Enterprise.

By the end of this section, you would have installed the Progress DataDirect SQL Server JDBC driver. Let’s get started with it.

1. Download the SQL Server JDBC driver from Progress website.
2. After the download has completed, unzip the package PROGRESS_DATADIRECT_JDBC_SQLSERVER_WIN.zip to extract the Setup.exe.
3. Double click on the Setup.exe to start the installer. Follow the prompts on the installer and when prompted about type of installation, choose Evaluation Installation.
4. If you would like to change the installation folder, you can do so during the installation process.
5. Complete the installation, by click on Next and on the Install Summary page, click on Install.

In this section, we will go through how to configure the DataDirect JDBC SQL Server to work with Splunk DB Connect.

1. Go to \path\to\Splunk\etc\apps\splunk_app_db_connect\default directory.
2. Make a backup of db_connection_types.conf file anywhere on your machine and open the file for edits.
3. Add the following configuration to the file, to add support for Splunk DB Connect app to use Progress DataDirect SQL Server JDBC driver.

    

   [datadirect_mssql]

   displayName = Progress DataDirect Microsoft SQL Server Driver

   serviceClass = com.splunk.dbx2.DefaultDBX2JDBC

   jdbcDriverClass = com.ddtek.jdbc.sqlserver.SQLServerDriver

   supportedVersions = 1.0

   jdbcUrlFormat = jdbc:datadirect:sqlserver://<;host>:<port>;DatabaseName=<database>

   port = 1433

    

4. Save the file and close it.
5. Now head over to \install\path\to\Progress\DataDirect\Connect_for_JDBC_51\lib and copy the sqlserver.jar file to \path\to\Splunk\etc\apps\splunk_app_db_connect\bin\lib
6. Reload the drivers in Splunk DB Connect settings or restart Splunk Enterprise to make sure the changes are reflected and you should see the Progress DataDirect Microsoft SQL Server Driver listed on the drivers’ page along with the status saying that the driver has been installed as shown below.

1. Go to Splunk DB Connect Explorer and create a new identity by clicking on (+) on the left side bar across Identities tree.
2. Fill in the details on the form as shown, where username and password are the credentials for SQL Server database and name the Identity as you like.
3. Create a new connection, and fill in the details as below

    

   Name: <Any Name>

   Identity: <Identity created in previous step>

   App: Splunk DB Connect

   Port: <Port for your database>

   Host: <IP Address/Hostname of SQL Server>

   Database Types: Progress DataDirect Microsoft SQL Server Driver

   Default database: <database name>

   JDBC URL Format: <No change needed, will be automatically populated from configuration file>

    

4. Here is a screenshot of the configuration that I have done for your reference.                                                                                                                                                                                                                                                               

    

5. Scroll down and click on validate button, to check if you are able to successfully connect to your SQL Server instance. You should see a Valid Connection notification as shown below once you have successfully connected.

    

   

    

6. Click on Save, to the save the connection.

Now that you have successfully connected your database with Splunk, feel free to connect more of your databases using Progress DataDirect JDBC drivers to upgrade the connectivity experience. In addition to database access, you can reference your machine data with structured data in cloud applications with Progress DataDirect JDBC connectors for Salesforce, Google Analytics, Oracle Eloqua, Marketo, and more. 

Learn more on what Progress DataDirect can do for your SQL Server connectivity needs and also learn the unfair advantage that you get by choosing Progress DataDirect connectivity solutions.