Skip to main content
Sumo Logic

Collect Logs for the Microsoft SQL Server App

  1. Last updated
  2. Save as PDF

This page provides instructions for configuring a local file source to collect SQL Server ERRORLOG data, a script source, and a sample log message. Click a link to jump to a topic.

Collection overview

The purpose of the script is to query database tables to collect MS SQL server performance metrics. The script was developed, deployed, and tested on MS Windows Server 2012 R2 Standard with MS SQL Server 2014.

The script connects to the SQL Server using the authentication mechanism specified in the provided configuration file. The script then 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:fieldvalue3}...

Step 1: 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.

Step 2. 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, use 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\MSSQL.n\MSSQL\LOG\ERRORLOG (For Example, it will look like C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\LOG\ERRORLOG depending on your system.) For further details on ERRORLOG location refer: https://docs.microsoft.com/en-us/sql/tools/configuration-manager/viewing-the-sql-server-error-log?view=sql-server-ver15 
  2. Set the Source Category to DB/MSSQL/ERROR
  3. In Step 5, for Encoding select UTF-16LE

Step 3. Download scripts and configuration file

Download the following files:

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

Step 4. Configure database login parameters

This section shows you how to configure the database login parameters by updating the MSSQL_Perf_Monitor.cfg file.

Configure the DB Login Section of the file based on the authentication method to be used. We recommend using one of the following Windows Authentication methods: 

  • Windows Authentication with ODBC Provider
  • Windows Authentication with sqlClient Provider
  • SQL Server Authentication with User Credentials

To configure database login parameters, do the following:

Choose the MSSQL_Perf_Monitor.cfg file [dbLogin] section configuration for your method of authentication, and modify the parameters shown in the following table.

  • Windows Authentication with ODBC Provider 
Parameter Description
Server Provide the server name, for example, WIN-Prod-ABC
Database Provide the database name, for example, master
UserId Database user credential not required
Pwd Database user credential not required
WinAuthODBC Set to true
WinAuthSqlClient Set to false

For example:

Server=WIN-Prod-ABC
Database=master
UserId=
Pwd=
WinAuthODBC=True
WinAuthSqlClient=False

  • Windows Authentication with sqlClient Provider 
Parameter Description
Server Provide the server name, for example, WIN-Prod-ABC
Database  Provide the database name, for example, master
UserId Database user credential not required
Pwd Database user credential not required
WinAuthODBC Set to false
WinAuthSqlClient Set to true

For example:

Server=WIN-Prod-ABC
Database=master
UserId=
Pwd=
WinAuthODBC=False
WinAuthSqlClient=True

  • SQL Server Authentication with User Credentials
Parameter Description
Server Provide the server name, for example, WIN-Prod-ABC
Database Provide the database name, for example, master
UserId = user_id Provide the user ID, for example, sa
Pwd = user_password Provide the user password, for example, sa123
WinAuthODBC Set to false
WinAuthSqlClient Set to false

For example:

Server=WIN-Prod-ABC
Database=master
UserId=sa
Pwd=sa123
WinAuthODBC=False
WinAuthSqlClient=True

Step 5. Enable and disable queries

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

Work with your database administrator to decide which queries to use, and have the database administrator review the queries before deployment in production. The amount of information system tables varies from organization to organization

To enable and disable queries, do the following:

  1. In the MSSQL_Perf_Monitor.cfg file, go to the [queries] section.
  2. To comment out a query, prefix it with a pound sign (#).
  3. To add a query, type or paste it into the file in this format:
SQLQuery = Your query on a single line (no newlines)
Perform queries on multiple database schemas on the same server

With Windows Authentication (ODBC Provider), you can perform queries on multiple database schemas that are on the same server. Refer to the following example to see how you can change the database context by changing the USE statement prefix.

# Query 10
# Query from mydb1 database
SQLQuery = Use mydb1; Select * from NPPersons
# Query 11
# Query from mydb1 database
SQLQuery = Use mydb1; Select LastName from NPPersons
# Query 12
# Query from mydb1 database
SQLQuery = Use mydb1; Select FirstName from NPPersons
 
# Query 13
# Query from mydb2 database
SQLQuery = Use mydb2; Select * from NPEmployees
 
# Query 14
# Query from master database
SQLQuery = Use master; SELECT * FROM dbo.spt_monitor

Verify your config file setting:

[dbLogin]
Server=Servername
Database=
UserId=
Pwd=
WinAuthODBC=True
WinAuthSqlClient=False

Step 6. Test the scripts

To test the scripts, do the following:

  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.
  4. When the script runs correctly, configure the Sumo script source as described in the following section.

Step 7. Configure the Script Source

To configure the script source, do the following:

  1. In Sumo Logic, go to Manage Data > Collection > Collection.
  2. Find the name of the installed collector to which you'd like to add a Source, and click Add... then choose Add Source from the drop-down menu.
  3. Select Script for the Source type. Collectors using version 19.245-4 and later do not allow Script Sources to run by default. To allow Script Sources you need to set the Collector parameter enableScriptSource in user.properties to true and restart the Collector.
  4. Enter a Name, for instance SQL Server Script, the Description is optional.
  5. For Source Category, enter the desired category. It can be any value you like, for example  DB/MSSQL/DBQueryScript.
  6. Make the following selections:
  • For Frequency, select desired frequency. 
  • For Specify a timeout for your command, select a value that is long enough that long-running queries can complete.
  • For Command, select Windows Script.
  • For Script, select Type a path to the script to execute, then enter: C:\MSSQL_Perf_Monitor_Script\MSSQL_Monitor.bat.
  • For Working Directory, enter: C:\MSSQL_Perf_Monitor_Script.
  1. Click Save.

 

Sample Logs

2018-05-02 00:05:09,876 Logon       Login succeeded for user 'bob'. Connection made using SQL Server authentication. [CLIENT: 242.95.35.122]

2018-00-02 00:00:33,803 spid884s     SQL Server is now ready for client connections. This is an informational message; no user action is required.

2018-00-02 00:00:09,521 spid500     Could not allocate space for object 'dbo.SORT temporary run storage:  262898620895351' in database 'cache' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.