How to Configure Splunk DB Connect & Export Splunk Data to an External Database
By: Quinlan Ferris | Splunk Consultant
At some point you may find the need to export data from Splunk to an external database for data collection or to keep important information pulled from searches in a safe and secure place along with other assets. You do have a few options to achieve this; for example, downloading a .csv file from a search and manually copying it over or using a script to append to the existing database. However, the most streamlined and efficient way is to use a Splunk-supported app, Splunk DB Connect. Not only does this app allow you to write to an existing or newly created database and run on a cron schedule, but you can also run searches against your data in an existing database. For now, we will cover exporting Splunk data to an external database. Splunk supports a wide variety of databases ranging from MySQL to MongoDB; while some are not directly supported, there are ways to configure SQLite and more if needed.
Prerequisite: Installation of Splunk DB Connect, install DB drivers, secure DB connection.
First, we will want to download Splunk DB Connect.
There are a few different ways to install depending on your environment and how it is configured. If running a single instance, install on the search head. On a distributed environment with search head clustering, you will need to install on deployer and heavy forwarders.
Next, make sure you install a Java Database Connectivity (JDBC) database driver. Optionally you can download more drivers based on your database in use. Database drivers should be extracted into the folder $SPLUNK_HOME\etc\apps\splunk_app_db_connect\drivers\.
Finally, once the app is installed, click “new connection” and simply configure desired connection type, identity, and JDBC settings for your database.
Use Case: Customer has Splunk DB Connect installed with connection setup and would like to write to an existing database.
Data Tab & Outputs
Navigate to the Splunk DB Connect app. We will need to navigate to the Data Lab tab in the top left and click on “outputs.” This section should currently be empty unless there are already outputs configured. We will click on the add new outputs in the top right to begin the setup of our outputs.
Search
This is where the possibilities are endless for what you can write to your database. You can either create a search based on specific fields you would like to map to the database – for example, creating a custom search will allow you to map specific field-value pairs to an existing table in a database and either replace current values in the table or append new values – or use a saved search job that already has important information that can be pushed to the database. Either way, you can use the search results and put them in the existing table.
Choose Table
*Must have a connection already established in the app with an external database.*
In this section, you will choose the connection to the database you created when initially setting up Splunk DB Connect. Next, you will want to select the “catalog” you will want. This is essentially the database you want to connect and append results to. In some rare cases and testing purposes, you may have a database with only one table. In that case, the schema will not be an option since there is only one schema. If your database has multiple tables, you will be given the option to select a schema to map the fields to. Once the schema is selected, Splunk will query and show a preview of the database you will be working in and existing data in the table. Confirm this is the correct database and has the correct values and click next.
Field Mappings
Depending on your field names and table names, you will map accordingly. Click on “add new search field” and map based on search and table names.
For example:
Search query has a field named product_Id that shows product name with last 4 digits
Table has a field name productId that displays the productId ESIN number
This will allow you to push the new field, product_Id, to the database and append it.
Upsert: There is a box at the bottom that, if selected, will update any table rows that have the same unique ID specified in the dropdown. This means that if there is an ID # 22 and it is constantly changing, having this box selected will change ID # 22 each time. If this box is not selected, it will simply add ID # 22 to a new row each time it changes.
Set Properties
This section is the finalization and setting up basic information like name and description of output.
Under “parameter settings,” you are able to set up query timeout to increase or schedule this output on a frequency basis based on a cron schedule. If you use cron to schedule this every two minutes it will update your database if there is any new data created in Splunk based on your search and update it on the database.
Complete!
You have set up your first output with Splunk DB Connect. If you are running a scheduled output, check your database to see the updated results from your Splunk search in your database.
To learn more, or if you have any questions, contact us by filling out the form below: