Skip to main content
Sumo Logic

Collect Logs for Microsoft SQL Server

The script queries database tables to collect SQL Server performance metrics. The script was developed, deployed, and tested on Windows Server 2012 R2 Standard with SQL Server 2014

To work, the script connects to database using database user, as specified in the .cfg file. Then it runs the provided SQL queries, as specified in .cfg file. Finally, it outputs each record of the SQL query result as key-value pair, such as:cfg file. Then it runs the provided SQL queries, as specified in .cfg file. Finally, it outputs each record of the SQL query result as 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: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

Next, configure a Local File Source to ingest the ERRORLOG, using the instructions in ERRORLOG, using the instructions in Local File Source, but make the following customizations:

  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. Define the Source Category as DB/MSSQL/ERROR.
  3. In Step 5, for Encoding select UTF-16LE.

 

Download and Configure the Scripts

There are three files to download:

  1. MSSQL_Perf_Monitor_Script.vbs
  2. MSSQL_Perf_Monitor.cfg
  3. MSSQL_Monitor.bat

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

Edit the Config file

Edit the Config file with the following changes:

  1. In the [dbLogin] section:
    1. Server = "server name"
      example "WIN-Server-prod-01"
    2. Database = "database name"
      example "master"
    3. UserId = "database user id"
      example "sa"
    4. Pwd = "user password"
      example "sa123"
  2. In the [queries] section:
    1. Nine example queries are provided.
    2. Comment out the queries by prefixing them with #.
    3. Add more queries if needed using the following format:
      SQLQuery = single line SQL query - no new lines

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
  1. The .bat file will execute the .vbs file. Internally, the  VBS script will execute all the SQL queries mentioned in the .cfg file. cfg file.

  2. The output of each SQL query result is displayed on the console.

Once the script runs correctly, you can configure the Sumo Logic Script Source.

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 SQL Server Script.
  5. The Description is optional.
  6. For Source Category, enter DB/MSSQL/DBQueryScript.
  7. For Frequency, select Every 5 Minutes.
  8. For Specify a timeout for your command, select 30 Seconds.
  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.