Splunk® Supported Add-ons

Splunk Add-on for Microsoft SQL Server

Acrobat logo Download manual as PDF


Acrobat logo Download topic as PDF

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
Last modified on 06 November, 2020
PREVIOUS
Configure DB Connect v3 inputs for the Splunk Add-on for Microsoft SQL Server
  NEXT
Upgrade the Splunk Add-on for Microsoft SQL Server

This documentation applies to the following versions of Splunk® Supported Add-ons: released


Was this documentation topic helpful?

You must be logged into splunk.com in order to post comments. Log in now.

Please try to keep this discussion focused on the content covered in this documentation topic. If you have a more general question about Splunk functionality or are experiencing a difficulty with Splunk, consider posting a question to Splunkbase Answers.

0 out of 1000 Characters