Managing PostgreSQL Statements

Bring your own PSQL logic and Nodinite will monitor and alert you whenever there is a problem detected!

To use this feature, you must provide AdHoc PSQL statements with logic as described with examples on this page. Then, the Database Monitoring Agent creates one Resource for each configuration. Each SQL Statement returns its evaluated state which therefore can be monitored from the use of Monitor Views and external alerts can be pushed using any of the Alarm Plugins.

Microsoft SQL Server based PSQL Statements are grouped by the Category PostgreSQL - SQL Statements

Example of a Monitor View filtered by the 'PostgreSQL - SQL Statements' category

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

Each configuration is manifested as a Resource

What are the key features for Monitoring SQL Statements?

• State Evaluation - Monitors and evaluates the state based on your own logic, see PSQL/Stored procedure code examples further down in this guide
• Actions - Support for the execution of Remote Actions

What is evaluated for SQL Statements?

The different possible evaluated states for your custom coded SQL Statements are provided in the table below:

TIP: The evaluated state may be reconfigured using the Expected State functionality that exists on every Resource within Nodinite.

How do I add my own SQL Statements?

The Database Monitoring Agent automatically monitors each configured SQL Statement. You 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 PostgreSQL - 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 PostgreSQL databases tab the global settings are configured. You then need to click on the Size check tab.

New SQL Statements are added by a Nodinite Administrator from the Remote Configuration dialogue detailed here.

From the PostgreSQL statements tab for the PostgreSQL Database Connection configuration, click on the Add button.

Any number of SQL Statements can be provided, but make sure that each one is fast to execute or you may end up hogging the agent and probably resulting in various timeouts.

Example configuration for SQL Statements with the Add button

The accordion is then expanded and you can then edit available properties.
Example of configuration of SQL Statements

You can manage the following properties:

• Enabled
• ApplicationId
• Display Name
• Command Type
• Command Text
• Description

Enabled

Flag to set if monitoring for this configuration is enabled or not

ApplicationId

Select the Id of which Application to associate this Resource (SQL Statement configuration) with.

Display Name

You can manage the user-friendly Name. This setting must be unique for each SQL Statement configuration.

Command Type

The Type of 'SQL Command', select one of the following:

• Text - For AdHoc PSQL Statements

Command Text

Enter your Command text, see examples further down in this guide.

• Execute Command - the SQL command(s) to execute

An AdHoc PSQL query with your own custom logic.

This feature can be dangerous and the connection string used should be configured with a user with read-only access

Description

• Description - user-friendly description for SQL Statement resource

Actions

The following Actions are currently implemented:

• Edit SQL Statement

Edit SQL Statement

You can manage your SQL Statements directly from Monitor Views where Remote Actions are allowed.

Click the Edit SQL Statement to open the Edit Modal for the selected Resource

The Edit Modal will then open.

Example of Edit Modal for the SQL Statements

The same properties as described in the Global configuration are available.

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 next synchronisation.

NOTE: Depending on the synchronisation interval set for the agent, there might be a delay before the Web Client reflects upon 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

The outcome (State) from the logic in your PostgreSQL - SQL Statements is based on whether you raise an exception or not.

Internally, the Database Monitoring Agent executes the Execute Scalar method. This will pick up data from the first column at the first row, and set this as Log Text unless you raised an exception.

Select + throw (raiserror)

The LogText presented for the PostgreSQL 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 simply becomes 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.

DO $$ BEGIN
DECLARE counter integer;
BEGIN
counter:= 0;
 SELECT count(*) into counter FROM public."LogEvents" WHERE "ValidationFailed"=true;
    IF (counter=0) THEN -- OK
		--return query SELECT 'OK';
	ELSEIF (counter<10) THEN -- WARNING
		RAISE EXCEPTION SQLSTATE '01000' 
      	USING HINT = 'Make sure to clean these up manually after inspection of the content within the ValidationFailedText column';
	ELSE -- EXCEPTION
		RAISE EXCEPTION 
      	USING HINT = 'Make sure to clean these up manually after inspection of the content within the ValidationFailedText column';
	END IF;
END;
END $$;

Next Step

Add or manage Monitor View

Related

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