Set Up Custom SQL Monitors

Write your own SQL scripts to set up tests specific to your needs.

Custom SQL Monitor provides and easy and flexible interface for monitoring the database based on custom SQL filter. This feature takes in the select query provided by the user and wraps it in a subquery which does a row_count on the result and record incident if row_count > 0.

You can choose either to set up a Normal Custom SQL monitor or one that has a Group-by column selected as a segmentation.

How to Set Up a Custom SQL Monitor

• Select the “Custom SQL” card from the Config module.

Selection for Custom SQL card

• You’ll be directed to the “Create a New Monitor” form with Setup and Configure steps.

The form fields will become available as you select the mandatory options.

Step 1: Set-up

• Select a Data Source.

• Choose Monitor Mode (Scheduled or On-Demand).

• If applicable you can set your monitor as Grouped by, you can turn on the toggle

To set your monitor as grouped by you can refer to Grouped-By documentation.

• Click “Proceed to Monitor Setup” to continue.

Grouped-by diabled

Grouped-by enabled

Step 2: Configure: Schedule Monitor

• Once you proceed to setup, you’ll reach the “Configure” page, where you can review your previous selections.

Overview from previous selection

Within the "Configure" popup, users must complete the necessary fields to save their preferences and successfully set up their Custom SQL monitor. These required fields include:

• Can create multiple tests for each test type per column/table

• Able to add a test name to differentiate monitors created

• Monitor Name

• Monitor Description is Optional.

• Frequency

• Add your Custom SQL query: Enter the query and Click on Validate, Once query is validated success message (Query successfully validated) will be displayed.

Note that if you modify the query after validating it, you will need to re-validate the query again.

Overview for Custom SQL Query

Get Notified/Custom Alert

• To set custom alerts, you must first turn on the "Notify default channel" toggle. Activating this will allow users to specify their desired alert channels, be it via emails or Slack channels.

  • You can select the desired alert channels in the dropdown.

  • Mention the address or channel name in the field.

• At last select the Incident Levels

Setting-up custom alert/notification

• Click on Submit and your monitor is created successfully.

• Once you click on Submit you’ll be redirected to All Monitor tab.

Configure: On-demand Monitor

• Set-up

• Select the data source and Monitor Mode

• If applicable you can set your monitor as Grouped by, by turning on the toggle

Set-up On demand monitor

• Once you click on Proceed to monitor setup ,you will reach the next page (Configure).

• Here, on the top you can see the previous page selection overview.

Selection from previous page

Within the "Configure" popup, users must complete the necessary fields to save their preferences and successfully set up their Custom SQL monitor. These required fields include:

• Can create multiple tests for each test type per column/table

• Able to add a test name to differentiate monitors created

• Monitor Name

• Monitor Description is Optional.

• Add your Custom SQL query: Enter the query and Click on Validate, Once query is validated success message (Query successfully validated) will be displayed.

Overview for SQL Query validation

Custom Notifications: On the Configure Monitoring pop up for both On Demand and Scheduled monitors, can also toggle the notification settings as well as custom alerts for each monitor modes.

Get Notified/Custom Alert

• Upon completing the required fields to create an On Demand monitor for Custom SQL, users can complete the set up process by choosing either of the following confirmation buttons based on their preferred use case:

  • Save: This is an applicable option for users who wish to set up an on demand monitor without running the monitor scan immediately after creation.

  • Save an run : This option is applicable for users who wish to run the On Demand Monitor immediately upon creation. For the next time users wish to run the on demand monitor again, users can navigate to the All Monitor's.

  • After selecting the above option you will be redirected to ALL MONITORS tab.

• Modify Monitoring

To modify an existing monitor:

1. Go to All Monitors.

2. Click the ellipsis (︙) and select View Monitor.

3. Click on Run once to run the monitor manually.

For setting up On-demand Monitor/Schedule Grouped By you can refer to Configure: On-demand /Schedule Monitor Grouped By for Custom SQL

Tips and Tricks

In this section, we will show a few ways you can configure the monitor using the power of SQL!

Label invalidation

Filtering records with specified label to trigger an incident

SELECT *
FROM public.sale
WHERE created_at BETWEEN 
	NOW() - '30mins'::INTERVAL AND NOW()
    AND status in ('pending', 'rejected')

Value Sum

Filtering sum of value over a threshold of a specified interval

SELECT 1
FROM public.sale
WHERE created_at BETWEEN 
	NOW() - '30mins'::INTERVAL AND NOW()
HAVING SUM(price * quantity) > 10000

Discrepancy

The discrepancy between 2 tables

WITH sale AS (
    SELECT SUM(price * quantity) as amount
    FROM public.sale
    WHERE created_at BETWEEN 
        NOW() - '30mins'::INTERVAL AND NOW()
)
, in_flow AS (
    SELECT SUM(amount) as amount
    FROM public.in_flow
    WHERE created_at BETWEEN 
        NOW() - '30mins'::INTERVAL AND NOW()
)
, base AS (
    SELECT 
        (SELECT amount FROM sale) 
        - (SELECT amount FROM in_flow) 
    AS discrepancy
) 
SELECT discrepancy 
FROM   base 
WHERE  discrepancy >  10000