Managing TSQL Monitoring Statements

Automate your database monitoring, and get alerts using your custom coded TSQL statements with your logic! Also, gain insights and perform administrative tasks executing pre-defined TSQL scripts.

The following two major features exist:

1. Monitoring - Get alerts from logic in custom-built TSQL statements
2. Management - Allow end-users to execute pre-defined TSQL statements from one or more TSQL Script collections

To use the Monitoring feature, you must provide either Stored Procedures or Text/AdHoc TSQL statements. Implement these with the logic from the examples on this page.

• Text/AdHoc
• Stored Procedures

Each SQL Statements entry in the Nodinite Database Monitoring Agent manifests as one Resource to manage. Assign these Resources to one or more Monitor Views to trigger alerts according to the evaluated monitor state. Use the Nodinite Alarm Plugins to push alerts.

In the Monitor Views, you can group the specified TSQL Statements Resources by the SQL Statements Category;.

Example of a Monitor View with filter by the 'SQL Statements' category

• Your custom-built SQL Statements for Microsoft SQL Server presents in Nodinite as resources where the name of the SQL Statement configuration becomes the Resource name

Each entry manifests as one Resource to monitor.

What are the key features for Monitoring SQL Statements?

• State Evaluation - Monitors and evaluates the state based on your custom coded TSQL logic; review the TSQL/Stored procedure code examples further down in this guide.
• Actions - Support for the execution of Remote Actions

How do SQL Statements evaluate?

The following monitoring states exist:

TIP: You can reconfigure the evaluated state for a Resource using the Expected State feature.

How do I add my custom coded TSQL Statements?

The Database Monitoring Agent automatically monitors each configured SQL Statement. The Nodinite Administrator can add new, and modify existing SQL Statements using the Remote Configuration dialogue for the Database Monitoring Agent.

There are two different ways to configure content for the category SQL Statements:

1. Global configuration
2. Specific configuration available on Resource level within applicable Monitor Views using the Remote Action Edit SQL Statement

1. Global configuration

From the SQL databases tab, the global settings are configured. You then need to click on the Size check tab.

The Nodinite Administrator can add new SQL Statements from the Remote Configuration dialogue, in detail here.

From the SQL Statements tab in the SQL Connection configuration, click on the Add button.

You can add any number of SQL Statements; Make sure that each one executes fast, or you may end up hogging the agent, and the result is probably various timeouts.

Example to add a new configuration for SQL Statements using the Add button.

General tab

To manage the essential configuration, click on the accordion to expand the details:

Example of essential settings for a SQL Statements configuration.

You can manage the following properties:

• Enabled - A flag to set if monitoring for this configuration is enabled or not.
• Display Name - You can manage the user-friendly Name. This setting must be unique for each SQL Statement configuration.
• Description

Monitoring Script tab

To manage the SQL Statement to use, clock on the Monitoring Script tab.

Example configuration from the Monitoring Script tab.

• ApplicationId - Use the Application Id to group Resources (SQL Statement entries).
• Command Type - The Type of 'SQL Command', select one of the following:
  • Text - For AdHoc TSQL Statements
  • Stored Procedure - For existing parameterless stored procedures
• Command Text
• Execute Command - the SQL command(s) to execute

The use of this feature should be evaluated according to your security policy. For example, use a connection string with an account with read-only grants.

Stored Procedure

Execute one or more stored procedures in the SQL Server database provided by the connection string. For example, enter [YourCustomProcedure] or the full path.

[Database].[dbo].[YourCustomProcedure]

Example of stored procedure call using a full path.

If you are using a Stored Procedure, you MUST enter the name with just one (1) line of code in the text field; Otherwise the evaluation will fail.

Actions

The following Actions exists:

• Details
• Execute SQL Script
  

Edit SQL Statement

The Nodinite Administrator can manage the SQL Statements using the Global Configuration.

Execute SQL Script

You can execute pre-defined TSQL scripts and immediately see the result. The Nodinite Administrator manages the collection of scripts is using the Global Configuration.

First, click on the Action button, and then click on the Execute SQL Script menu item.

Example of the Execute SQL Script menu item.

This opens a modal with the pre-defined script collection for this SQL Statement monitoring configuration.

Click the Execute button to execute the query. The result displays in the bottom of the modal.
Example with the result from executing your custom TSQL Script on demand.

Save

You must click on the Save button or click on the Save and close button for any changes to be written to the agent and take effect on the next synchronisation.

NOTE: Depending on the synchronisation interval set for the agent, there might be a delay before the Web Client reflects the change. You can choose to force the agent to synchronize from the configuration of the Monitoring Agents.

Save and close, save, and close the dialogue.

Cancel, close the dialogue without saving any changes.

Examples

There are two ways to provide the outcome (Monitoring state of the Resource) based on the logic in your custom-built SQL Statements:

Use a supported implementation according to the use of either Text/AdHoc or Stored Procedures.

Internally, the Database Monitoring Agent executes the Execute Scalar method. Doing so, Nodinite picks up data from the first column from the first returned row. The extracted value is set as the Log Text unless you raised an exception.

1. Select + return code

The LogText presented for the SQL Statement configuration (The Resource) comes from your last SELECT statement (1st row, 1st column). If you do not select anything, the Log Text is empty.

2. Select + throw (raiserror)

The LogText presented for the SQL Statement configuration (The Resource) comes from your last SELECT statement (1st row, 1st column) or from the Exception. If you do not select anything, the Log Text is empty.

Text Example

Example how to use the SQL Statement with Command Type set to Text. This example counts the rows in the actual table. Depending on your logic, different states can be returned.

DECLARE @rowCount INT,  
        @log VARCHAR(256),  
        @errorLimit INT = 3,  
        @warningLimit INT = 1  
  
SELECT @rowCount = COUNT(1) FROM [ConnectionFailures]  
            
IF (@rowCount > @errorLimit)  
   BEGIN  
      SET @log = 'ERROR Number of connection failures exceeded ' + CAST (@errorLimit AS VARCHAR(18));  
      THROW 51000, @log, 1;  
   END  
ELSE IF (@rowCount > @warningLimit)  
   BEGIN  
      SET @log = 'WARNING number of connection failures exceeded ' + CAST (@warningLimit AS VARCHAR(18));  
      THROW 51000, @log, 2;  
   END  
ELSE  
   BEGIN  
      SELECT 'OK'  
   END  

NOTE: THROW is a command that is only available in SQL Server 2012 and later. In SQL Server 2008 R2, you can use RAISERROR (@log, 16, 1); RAISERROR(Message, Severity, State). The Severity should be set to 16. Such a high value is required to stop the execution of the query.

Stored Procedure example

Example how to use the SQL Statement with Command Type set to Stored Procedure

[SP_CheckConnectionFailures]

Example of the Stored Procedure (existing in the actual database):

USE [MyDatabase]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[SP_CheckConnectionFailures] 
AS

BEGIN
   SET NOCOUNT ON;
            
   DECLARE  @rowCount INT,
            @errorLimit INT = 3,
            @warningLimit INT = 1,
            @ret INT = 0,
            @log VARCHAR(256)
            
  SELECT @rowCount = COUNT(1) from [dbo].[ConnectionFailures]
            
  IF (@rowCount > @errorLimit)
     BEGIN
        SET @log = 'ERROR: The number of connection failures was exceeded: ' + CAST (@errorLimit AS VARCHAR(18));  
        SELECT @log
        SET @ret = -10
     END
  ELSE IF (@rowCount> @warningLimit)
     BEGIN
        SET @log = 'WARNING: The number of connection failures was exceeded: ' + CAST (@warningLimit AS VARCHAR(18));  
        SELECT @log
        SET @ret = 10
     END
  ELSE
     BEGIN
        SELECT 'OK'
     END
  RETURN @ret

END -- Procedure

NOTE: There is currently no way to provide arguments to the Stored Procedures. Use Command Type set to Text and pass arguments to the Stored Procedure from your Text/AdHoc TSQL code.

Next Step

Add or manage Monitor View

Related

Azure SQL Statements
PostgreSQL SQL Statements
Database Monitoring Agent
SQL Categories
Resources
Monitoring Agents
Monitor Views