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
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.
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
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,
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.