Blog

Hello fellow Splunkers! In this blog post, we will outline how to utilize the Splunk Add-on for Microsoft SQL Server to ingest the binary trace log data that is generated by SQL Server. 

In order to accomplish this objective, we must first establish a connection to the SQL Server instance using DB Connect. This of course involves creating an "Identity" that can authenticate to the database instance, and then creating a "Connection" via that Identity. This connection needs to be facilitated by a database driver, and one pitfall to watch out for here is to make sure to choose the correct driver. Depending on the environment that you will be deploying to, you will need to make a choice on operating system (Windows or Linux), type of driver (MS Generic or jTDS), and authentication protocol (SQL, Windows, or Kerberos). Please refer to the following link for instructions on how to properly setup the correct driver:

http://docs.splunk.com/Documentation/DBX/3.1.1/DeployDBX/Installdatabasedrivers#Microsoft_SQL_Server

Once we have established a connection to the SQL Server instance via DB Connect, we can focus on the fun part: integrating the SQL Server Add-on with DB Connect in order to read the binary trace log data. First, we will take advantage of a template that the Add-on ships with, named "/default/db_input_templates.conf":

[mssql:trclog]

description = Collect system trace log for troubleshooting

interval = 60

mode = rising

index_time_mode = current

query = SELECT * \

FROM fn_trace_gettable('C:\\\\Program Files\\\\Microsoft SQL Server\\\\MSSQL11.MSSQLSERVER\\\\MSSQL\\\\Log\\\\log.trc',default) \

WHERE StartTime > ? \

ORDER BY StartTime ASC

sourcetype = mssql:trclog

rising_column_index = 14

You can see that the sourcetype of this stanza is "mssql:tracelog", and that's exaclty what you should filter on so you can find the template. The most important part of the template is the SELECT query; in particular, there are two aspects of the query to pay close attention to: the "fn_trace_gettable" function and its first parameter, which is the path to a trace log file. You will of course need to adjust this file path so that it corresponds to your custom environment.

The "fn_trace_gettable" function is the entity that's actually doing all the work behind the scenes, as it converts the binary trace file data into readable raw events for Splunk to index. However there is an inherent issue with this function; if the initial trace file (passed in as the first parameter) is removed, the function will stop working. This scenario can occur for many reasons, for example if there is a reboot of the SQL Server.

In order to get around this problem, we simply have to tweak the query that utilizes the "fn_trace_gettable" function, with some special SQL code that your DBA will be familiar with: