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.

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

Important

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.

Here's an example of Edit Modal for the SQL Statements

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

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 Web Client reflects upon the change. You can force the agent to synchronize the Configuration for all Nodinite Monitoring Agents.

Save and close, save, and close the dialogue.

Cancel, closes 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