Configure DB Connect v2 inputs for the Splunk Add-on for Microsoft SQL Server

To gather trace logs, audit logs, and data from Dynamic Management Views, the Splunk Add-on for Microsoft SQL Server leverages Splunk DB Connect. Follow the instructions that correspond to the version of DB Connect that you have installed. This topic presents the instructions for DB Connect Version 2.x.

To prepare your environment and configure your inputs, follow these steps.

1. Set up the database connection
2. Configure the inputs
3. Adjust your auto KV extraction settings
4. Configure the database server lookup

Set up the database connection

Setting up the database connection involves three steps:

1. Download and install the Microsoft JDBC driver for SQL Server.
2. Create an identity in Splunk platform.
3. Use the Splunk DB Connect GUI to create a database connection or use db_connections.conf to create a database connection.

Download and install the Microsoft JDBC driver for SQL Server

To enable Microsoft SQL Server connections, download and install the Microsoft JDBC Driver for SQL Server as described in the Install database drivers section of the Deploy and Use Splunk DB Connect manual.

Create an identity in the Splunk platform

1. Restart the Splunk platform instance.
2. Create an identity for establishing a connection to the database. Make sure the user for this identity has the system role. You can use a username and password for authentication, or Windows Authentication. However, using DB Connect version 2.x with Windows Authentication and the JDBC driver for SQL Server requires additional steps. See Cannot connect to Microsoft SQL server in the DB Connect manual for more information.
3. Next, you need to create a database connection to the SQL Server using either the Splunk DB Connect GUI or the db_connections.conf file as described in the following sections.

Use the Splunk DB Connect GUI to create a database connection

To create a database connection to the SQL Server database using the Splunk DB Connect GUI, refer to Create and manage database connections in the Splunk DB Connect manual for step-by-step instructions.

Enter the following parameters:

Parameter	Value
Connection Name	Use SQLServer for the connection name. If you prefer to choose a different connection name, you need to manually edit your local inputs.conf file later to specify a non-default name.
Identity	Use the identity you created above.
App	Use the default app, Splunk DB Connect V2.
Port	The default port for SQL Server database is 1433.
Host	Enter the host IP address where the SQL Server database is running.
Database Types	Choose MS-SQL Server Using MS Generic Driver or MS-SQL Server Using jTDS Driver based on which driver you are using.
Default Database:	Enter the database name on SQL Server.

Use db_connections.conf to create a database connection

If you do not want to use the DB Connect GUI, you can create a database connection to the SQL Server database using the db_connections.conf file.

1. Create a file called db_connections.conf in the %SPLUNK_HOME%\etc\apps\splunk_app_db_connect\local directory.
2. Copy the stanza below to db_connections.conf and edit the values of each field to reflect your production environment.

   <connection name>
   connection_type = <connection type: generic_mssql or mssql>
   database = <database name>
   host = <host or ip address of the SQL Server database>
   identity = <identity name used for the connection>
   jdbcUseSSL = <enable SSL>
   port = <network port of the SQL Server database>
   

   When you create a database connection object for your Microsoft SQL Server, select the appropriate database and driver from the Database Types pop-up menu. There are two options: MS-SQL Server Using MS Generic Driver if you downloaded the Microsoft driver, or MS-SQL Server Using jTDS Driver if you are using the open source jTDS driver.

Configure the inputs

To configure the inputs, you can copy the input template provided in the add-on to your local inputs.conf file and enable the inputs that you want to collect there. Alternatively, you can configure the DB Connect inputs manually using the DB Connect GUI.

Use inputs.conf to configure your database inputs

1. Copy the contents of %SPLUNK_HOME%\etc\apps\Splunk_TA_microsoft-sqlserver\default\sqlserver_dbx2.conf to %SPLUNK_HOME%\etc\apps\splunk_app_db_connect\local\inputs.conf.
2. Change disabled = 1 to disabled = 0 in the input stanzas you want to use.
3. If you selected a custom connection name other than SQLServer, change that parameter here in each stanza to match the connection name that you configured in db_connections.conf or via the GUI.
4. Change the file path for [mi_input://mssql:trclog] and [mi_input://mssql:audit] based on the paths that you specified when you created the audit objects in Microsoft SQL Server.
5. (Optional) Select a custom index. The default is main.
6. Restart the Splunk platform instance for your changes to take effect.

Use the Splunk DB Connect GUI to configure your database inputs

Refer to Create and manage database inputs in the Splunk DB Connect manual for step by step instructions for using the GUI to configure your database inputs.

The following example shows the configuration instructions for the sys.processes Dynamic Management View. Refer to default/sqlserver_dbx2.conf for the source types and query statements.

Parameter	Setting Value
Status	Enabled
Name	mssql:processes
Description
App	Splunk DB Connect v2
Connection	Enter the database connection name you created when you set up the database connection. These instructions recommended SQLServer.
Query Mode	Advanced Query Mode
Query Statement	SELECT a.*, b.name,CONVERT(varchar(128),SERVERPROPERTY('ServerName')) AS ServerName, db_name() AS DatabaseName FROM sys.sysprocesses a JOIN sys.databases b ON a.dbid = b.database_id
Type	Batch Input
Max Rows to Retrieve	10000
Timestamp	Current Index Time
Output Timestamp Format	YYYY-MM-dd HH:mm:ss
Execution Frequency	300
Source	dbx2
Sourcetype	mssql:processes
Index	main
Select Resource Pool	local

Adjust your auto KV extraction settings

Some source types, such as mssql:execution:dm_exec_query_stats retrieve fields with multiple lines. To ensure that your fields show the full values that you expect, adjust your KV extraction settings.

1. Open %SPLUNK_HOME%\etc\system\local\limits.conf.
2. Add or change this stanza:

[kv]
maxchars = 20480