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;.

Here's an 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:

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

Global Configuration

From the SQL databases tab, the global settings are configured. You then need to click on the SQL Statements 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 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.

Then, expand the accordion.

The following tabs exist:

• General tab
• Monitoring Script tab
• User Action Scripts tab

General tab

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

Here's an 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 Statement tab

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

Example configuration from the Monitoring Script tab.

• 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

Info

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]

Here's an example of stored procedure call using a full path.

Info

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.

User Action Scripts tab

You can add entries in the 'User Action Scripts' tab to allow your end-users to execute scripts on demand. You do not need to have anything but a comment in the Monitoring Script tab.

Each entry is one Resource. Each entry may have any number of scripts.

Click the Add button to add a script.

Then, expand the accordion.

You can then fill out the following properties:

• Enabled - When checked, this SQL Action Script is visible in the end-user list of executable scripts
• Display Name - The user friendly name of this action script
• Description - User friendly description for this SQL Action Script
• Command Type - The type of SQL command
  • Text
  • Stored Procedure
• Execute Command - The SQL command to execute
  

Authentication

:new 6.2.0.0:
You can override the default connection string in use and impersonate the user account executing the script on behalf of the user.

• ConnectionString - The connection string in use for this SQL Statement.
• Domain User - The name of the Windows user account. Format: DOMAIN\User
• Password - The password for the provided domain user

Save

You must click 'Save' or 'Save and close' for changes to persist. The new settings/thresholds are evaluated on the next synchronization.

Here's an example of the Save options.

Note

Depending on the synchronization interval set for the agent, there might be a delay before the Nodinite 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, closes the dialogue without saving any changes.

Actions

The following Remote Actions exists:

• Details
• Execute SQL Script

Details

You can view some details about the 'User Action Scripts' entry. Click the Details menu item in the Actions button.

A modal opens with the information.

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.

Here's an 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 on the *Execute button to execute the script.
You must first confirm the intent to proceede with the operation.

The result displays in the bottom of the page.
Example with the result from executing your custom TSQL Script on demand.

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 Nodinite 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.

Important

Ensure your logic does not perform any processing or have side effects. We recommend that all TSQL commands execute within a second or so. This means you may need to adjust the background process and involve SQL Jobs (or other scheduled background tasks) to perform processing and then the logic here merely gets the result.

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