splunk db connect

< Back |Home| Next >

How to install and configure splunk db connect

splunk db connect

Splunk DB connect enables you to connect in real-time to many databases concurrently, and all the major database management systems: Oracle RDBMS, Sybase Adaptive Server, Microsoft SQL Server, MySQL, etc. You can bring in unstructured data to combine with machine data to add business context, and it enables importing data from the database in to the Splunk Enterprise product

How to install and configure DB connect app:

Splunk can also be used to index a database table using the help of 'Splunk DB Connect' app. In this tutorial, I am explaning how to install, configure and setup 'Splunk DB Connect' app to index an oracle table.

prerequisite:
JDK/JRE is a prerequisite for 'Splunk DB Connect' app. Please install JDK/JRE before you start the tutorial.

Install 'Splunk DB Connect' App

Download 'Splunk DB Connect' app from splunk website

Launch your splunk web console and go to Apps->Manage Apps,

Click Install app from file. Click choose file button and browse to downloaded file and click upload

Once the app has been installed, Restart splunk
Now that we have the 'Splunk DB Connect' app installed, we need to configure it.
Launch 'Splunk DB Connect' app from splunk web. Apps->Splunk DB Connect and click 'Continue to app setup page'
Assuming you have jdk/jre installed, Java Home and JVM Commandline options are picked up automatically click 'Save',

Install Oracle jdbc Driver

Now that we have the 'Splunk DB Connect' app installed, we need to install Oracle jdbc driver to configure the Oracle Database connection.

Download ojdbc6.jar

Copy ojdbc6.jar to $SPLUNK_HOME/etc/apps/dbx/bin/lib and restart splunk.

Setup The Oracle Database Connection using 'Splunk DB Connect'

'Splunk DB Connect' app is now ready to configure the Oracle Database connection.

Inside 'Splunk DB Connect' app, click 'Database connectinos in Splunk Manager' or Settings->Manage Database connections and click New.

Give a name for the connection.

Select Database Type as Orcale from the dropdown list.

I choose Transaction Isolation Level as DATABASE_SETTING (default one).

Enter the Host or ip address of oracle database server. This can be found from your tnsnames.ora.

Unless its changed from the default, Leave the port empty (default port is 1521).

Enter the username and password used to connect to the oracle database.

Enter the Database name. This can be found from your tnsnames.ora.

Select 'Read only' checked in if you dont want splunk to make any changed to the database.

Select 'Validate Database Connection' checked. This will validate the details entered. Click 'Save'

Setup The Database Input To Index The Table

Now we have the database connection setup, it is time to setup the input where we can define the table to be indexed.
Inside 'Splunk DB Connect' app, click 'Database inputs in Splunk Manager' or Settings->Manage Database inputs and click New

Give a name for the connection.

There are two types of inputs.
Tail - This means each time a new row is added to table that will get indexed. This is the most common option.
Other option is Dump - Irrespective of new row, the table get dumped to index in certain intervals.

Select the database from the dropdown. This is the one we added in previous step.

In next step either we can give a table or we can provide a custom query.
If you give a table name, the whole table get indexed. If the table is big (for eg. more 10 million rows). Input may not get saved. In this scenario custom querries come to help. We can restrict the rows to be indexed using the query.
eg. of custom queries are below,
SELECT * FROM my_table WHERE rising_column_name > value {{AND $rising_column$ > ?}}
SELECT * FROM my_table WHERE (rising_column_name > value AND condition) {{AND $rising_column$ > ?}}

We need to provide a Rising Column name. This coulmn should be an auto incremental column like id. Based on this column's value splunk decides what to index.

'Sourcetype' can be left blank.

If you leave 'Splunk Index' blank, it will be indexing to main index.

By leaving 'Host Field value' it will be default spunk server.

I prefer to leave 'Output Format' Key-Value format (default)

If you select 'Output timestamp' splunk indexing timestamp will be displayed

If the timestamp is not displaying in correct format in splunk, we can utilize 'Timestamp column' and 'Timestamp format'

'Interval' can be left blank and splunk will decide it based on the table size. If you decide to set it manual, we need to give a valid linux cron expression. eg * * * * * will run it evey minute

Troubleshooting

If the indexing is not working or stopped look at $SPLUNK_HOME/var/lib/persistentstorage/dbx Each input has its own directory, which is a hash of its name and a 32-character hexadecimal string. This directory typically contains these files: manifest.properties has meta-information, such as the input name. state.xml has the actual state in XML format. state.xml look like below

------------------------------------------
<list> 
<value key="latest.record_update"> 
<value class="id">5064</value> 
</value> 
</list> 
--------------------------------------------------------------

In above example id is the rising column and splunk indexed till 5064. You can reset the indexing by changing the value to a previous one.

$SPLUNK_HOME/etc/apps/dbx/local dir has database connection, input information. These file can be manually edited to configure.

Comment Box is loading comments...