Managing TSQL Monitoring Statements

Gain full control over your SQL Server monitoring with the Nodinite Database Monitoring Agent. This page shows you how to leverage custom TSQL scripts for proactive monitoring, instant alerts, and self-service management—empowering both integration experts and business users.

✅ Monitor SQL Server health and business data using your own TSQL logic
✅ Instantly receive alerts and automate remediation with Remote Actions
✅ Empower self-service for business users to execute pre-defined scripts
✅ Centralize monitoring and management for all your SQL Server resources

Automate your database monitoring and get alerts using your custom coded TSQL statements! Gain actionable insights and perform administrative tasks by executing pre-defined TSQL scripts—all from a single platform.

The Nodinite Database Monitoring Agent provides two major features for SQL Server:

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

Here's a visual overview of how monitoring and management features are organized in Nodinite.

To use the Monitoring feature, provide either Stored Procedures or Text/AdHoc TSQL statements. Implement your business logic using 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 appear 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 – Monitor and evaluate the state based on your custom TSQL logic. See 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, configure global settings. Then click on the SQL Statements tab.

The Nodinite Administrator can add new SQL Statements from the Remote Configuration dialogue, detailed 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 quickly, or you may end up hogging the agent, resulting in 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 – Set if monitoring for this configuration is enabled or not.
• Display Name – 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, click 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

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.

Example of the User Action Scripts tab for on-demand script execution.

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
  
  Example of a script entry for on-demand execution.

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.

Example of the Authentication tab for script execution.

• 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 saves and closes the dialogue.
Cancel closes the dialogue without saving any changes.

Actions

The following Remote Actions are available:

• Details
• Execute SQL Script

Example of available Remote Actions for SQL Statements.

Details

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

Example of viewing script details.

A modal opens with the information.

Example of the script details modal.

Execute SQL Script

You can execute pre-defined TSQL scripts and immediately see the result. The Nodinite Administrator manages the collection of scripts 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.

Example of the script collection modal.

Click on the Execute button to execute the script.
You must first confirm the intent to proceed with the operation.
Confirmation dialog for script execution.

The result displays at 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. 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 of using 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 available in SQL Server 2012 and later. In SQL Server 2008 R2, 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 of using 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 Topics

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