Sending Data from Database To Splunk Using DB Connect ( DBX – Part 2)

Sending Data from Database To Splunk Using DB Connect ( DBX – Part 2)

In this post we are covering how to to create an input in the “Splunk DB Connect” application and get the most out of your database with Splunk.

Creating an “Input” in “Splunk DB Connect” application

Step-1: Navigate to the “Inputs” page, and click on the “New Input” button.

Step-2: Set SQL Query,

Connection :  Select the connection you want to use for this
              input.
Catalog : Select the Catalog (if available).
Schema : Select the Schema from your database.
Table : Search for the table/click on the table name, contents
         of which you want to index in Splunk.

SQL Editor : You can use this to directly write SQL queries, as per your requirement.

NOTE: The templates help you get rid of repeatedly recreating basic
      content of inputs. The template of the add-ons will be listed
      under the template field of DB Connect, below is the list of
      template add-ons available on the splunkbase.com.

Input Type:

• Splunk Add-on for Microsoft SQL Server

• Splunk Add-on for Oracle Database

• Splunk Add-on for McAfee

• Splunk Add-on for Nagios Core

We are not using any templates in this example.
Input Type : It offers two input types-

Batch : The SQL query runs at its schedule and collects all the 
        outputs of that query, this happens every time the SQL 
        query runs, due to which there are high chances of
        indexing duplicate data.

Rising : To overcome the duplicate data issue, you can select 
         a column from your table which can be used for 
         incremental data ingestion. Splunk keeps a track of 
         this column to understand how far it has ingested the
         data already. For example you can have the “timestamp”
         column as a rising column, since timestamps always 
         keep increasing.

Timestamp: You can decide whether you  want to have the current timestamp or a timestamp column from your table to be shown as the event timestamp in Splunk.
Click on the “Next” button to move forward in the input configuration.
Step-3: Set Properties,

Name: Provide a unique name for this input.
Description (Optional) : You can put a small description about what this input does to help you/others.
Application : Select the app context for this input, by default “Splunk DB Connect”.
Max rows to retrieve : You can specify the maximum number of rows to index, each time the query runs, defaults to unlimited.
Fetch size : Specify how many rows you want to fetch from the database at a time, defaults to 300.

NOTE : A single query run can have multiple fetch cycles, depending on the number of rows to retrieve.

Execution Frequency : Set the time interval(s) in secs at which this query should run or you can write (CRON Expression).
Host (Optional) : Provide a host, if you want to override the default value.
Source (Optional): Provide a Source, if you want to override the default value.
Sourcetype : Provide the sourcetype for this input.
Index : Provide the index name, where the data from this input should be stored.

You should get the “Done” screen as shown below,

Step: 4 

Now open Search Head. In the search box type “index=oracle_db”  ( this is the index name which we had mentioned while creating the input on the DB Connect ) to see the data, which is coming from the database. Now you can see the database table name ( Demo_customers) as source of this data.

Additionally, you can use the “SQL Explorer” to test/experiment with your SQL queries before you use them to create inputs/outputs etc.
Hope, this post was worth a read. If you want to know more about the DB connect configuration and operations please visit the below links.

How To Configure Splunk DB Connect ( DBX – PART 1 )
Sending Data from Splunk To Database Using DB Connect ( DBX – Part 3)
How TO Lookup With Database Using DB Connect ( DBX – Part 4 )

Happy Splunking!!