Skip to main content
Sumo Logic

Collect Logs for Microsoft SQL Server

This page has instructions for configuring a local file source to collect SQL Server ERRORLOG data, and a script source to collect SQL Server performance metrics.

The script source uses a VB script to query SQL Server database tables. The script was developed, deployed, and tested on Windows Server 2012 R2 Standard with SQL Server 2014.

The script connects to SQL Server using the database user specified in the provided configuration file. Then it runs the SQL queries specified in the configuration file and outputs each record of the SQL query result as a key-value pair, such as:

{fieldname1:fieldvalue1} {fieldname2:fieldvalue2} {fieldname3:fieldvalue4}....

Install a collector for Windows

If you have not already done so, install and configure an installed collector for Windows, using the instructions in Install a Collector on Windows.

Configure a local file source

The ERRORLOG typically uses UTF-16LE encoding, but you should confirm the file encoding used on your SQL Server for this configuration.

To check your ERRORLOG encoding on a Mac, use the following commands:

MacBookPro:Downloads npande$ file -I ERRORLOG*ERRORLOG*
ERRORLOGERRORLOG: text/plain; charset=utf-16le
ERRORLOG.1: text/plain; charset=utf-16le
ERRORLOG.2: text/plain; charset=utf-16le

You can also check ERRORLOG encoding with Notepad ++.

To configure a local file source to ingest the ERRORLOG, using the instructions in Local File Source, with the following differences:

  1. In Step 4, for the File Path, use something like, C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\LOG\ERRORLOG, depending on your system.
  2. Set the Source Category to DB/MSSQL/ERROR.
  3. In Step 5, for Encoding select UTF-16LE.

Download scripts and configuration file

Download these files:

Copy the files to your Windows system into a folder that you create at C:\MSSQL_Perf_Monitor_Script.

Configure database login parameters

Make the following updates to the MSSQL_Perf_Monitor.cfg file: 

In the [dbLogin] section, supply values for each parameter:

Server=CompleteServerName
Database=DatabaseName
UserId=DatabaseUserId
Pwd=DatabasePassword

For example:

Server=WIN-Server-prod-01
Database=master
UserId=sa
Pwd=sa123

Enable and disable queries

The [queries] section of MSSQL_Perf_Monitor.cfg contains nine SQL queries.  

To comment out a query, prefix it with a pound sign (#).

To add a query, type or paste it into the file in this format:

SQLQuery = Your query on a single line (no newlines)

Test the scripts

To test the scripts:

  1. Go to the command prompt and execute the .bat file:

    C:\MSSQL_Perf_Monitor_Script>MSSQL_Monitor.bat
    
  2. The .bat file executes the .vbs file. The VB script runs the SQL queries specified  in the .cfg file. 
  3. The output of each SQL query result is displayed on the console.

Once the script runs correctly, configure the Sumo script source, as desribed in the following section.

Configure the script source

  1. In Sumo Logic, go to Manage Data > Collection.
  2. Find the name of the installed collector to which you'd like to add a Source. Click Add... then choose Add Source from the pop-up menu.
  3. Select Script for the Source type.
  4. For Name enter any name you like, for instance SQL Server Script.
  5. The Description is optional.
  6. For Source Category, enter the desired category. It can be any value you like, for example  DB/MSSQL/DBQueryScript.
  7. For Frequency, select desired frequency. 
  8. For Specify a timeout for your command, select a value that is long enough that long-running queries can complete.
  9. For Command, select Windows Script.
  10. For Script, select Type a path to the script to execute, then enter: C:\MSSQL_Perf_Monitor_Script\MSSQL_Monitor.bat.
  11. For Working Directory, enter: C:\MSSQL_Perf_Monitor_Script.
  12. Click Save.