Configure DB Connect version 3.6.x 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 3.6 and above.
To prepare your environment and configure your inputs, follow these steps.
- Set up the database connection
- Configure the inputs
- Adjust your auto KV extraction settings
- Configure the database server lookup
Set up the database connection
Setting up the database connection involves three steps:
- Download and install the Microsoft JDBC driver for SQL Server.
- Create an identity in Splunk platform.
- Use the Splunk DB Connect GUI to create a database connection or use
db_connections.confto 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
- Restart the Splunk platform instance.
- 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 3.1 with Windows Authentication and the JDBC driver for SQL Server requires additional steps. See Can't use Windows authentication for Microsoft SQL Server with Microsoft JDBC Driver for SQL Server in the DB Connect manual for more information.
- Next, you need to create a database connection to the SQL Server using either the Splunk DB Connect GUI or the
db_connections.conffile 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:
|Connection Name||Enter a unique connection name.|
|Identity||Use the identity you created above.|
|Connection Type||Choose MS-SQL Server Using MS Generic Driver or MS-SQL Server Using jTDS Driver based on which driver you are using.|
|Host||Enter the host IP address where the SQL Server database is running.|
|Port||The default port for SQL Server database is 1433.|
|Default Database:||Enter the database name on SQL Server.|
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
- Create a file called
- Copy the stanza below to
db_connections.confand 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 database inputs using the Splunk DB Connect GUI
Refer to Create and manage database inputs in the Splunk DB Connect manual for step by step instructions configuring your database inputs in the GUI.
If you want to create Microsoft SQL Server input, choose the template created for Splunk Add-on for Microsoft SQL Server under Template field of DB Connect. .
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.
- Add or change this stanza:
[kv] maxchars = 20480
Configure the database server lookup
port values for each of your database servers. This step is required to integrate with Splunk IT Service Intelligence.
- On each of your search heads, open
- Edit this file to include correct
portvalues for each of the
database_serverin your event data.
- Save the file.
- Restart the search head.
Configuring MSSQL to collect data through Windows AD
All the data obtained via DB Connect are from Dynamic Management View or Dynamic Management Function including both server-scoped and database-scoped.
This requires a user with both
VIEW SERVER STATE permission and
VIEW DATABASE STATE permission.
For example, If a user called 'splunk' is created, admin user needs to grant the permission to the user using the following SQL command:
GRANT VIEW SERVER STATE TO splunk;
GRANT VIEW DATABASE STATE TO splunk;
For more information, check the oficial SQL Server Doc
Configure monitor inputs and Windows Performance Monitoring inputs for the Splunk Add-on for Microsoft SQL Server
Configure DB Connect v2 inputs for the Splunk Add-on for Microsoft SQL Server
This documentation applies to the following versions of Splunk® Supported Add-ons: released