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.

Set Up Monitoring

Begin by selecting "Custom SQL" card from our config module landing page.

Upon selecting the "Custom SQL" card, it's compulsory to first choose the data source. After selecting your data source, a list of active/inactive Custom SQL monitors that have been previously set up will be loaded.

To set up custom SQL Monitoring for the first time, click the "Add New Monitor" button.

Once clicked, a setup pop-up for Custom SQL monitors will emerge.

Example of the Set up monitoring modal.

Users can switch the monitoring mode by changing the toggles to either "On Demand" and "Scheduled". Creating a On Demand monitor lets you run this Custom SQL monitor upon triggering the run. Monitor on Schedule mode will allow you to run this monitor on a regular frequency.

After that, you can select the Grouping mode. The grouping mode allows you to toggle between the Normal monitor or Group-by monitor. To run a check where you can define a specific segment in your data that has failed, you can subdivide it via a column you can set in your monitor by selecting the values of which to add the segments by.

Adding your custom query

Input the custom test to be run in the text box and click on Validate query. Once the query is validated that it is a valid SQL statement and can be run, you can save the configuration.

You can use the "Quick look-up" box to search for the table or column names to quickly copy and paste it into the text box.

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

Set Up Monitoring for Scheduled, Normal monitor

In the Scheduled Set Up Monitoring pop-up, users are required to fill in the following required fields:

• Custom SQL Test name.

• Custom SQL Query

• Incident Levels

• Frequency (Learn more about Custom Scheduling below)

pageCustom Scheduling For Monitors

After filling up the required fields, press the Save Preferences button and you are all good to go!

Set Up Monitoring for On Demand, Normal monitor

In the On Demand Set Up Monitoring pop-up for Custom SQL test is slightly different from the Scheduled Custom SQL test, users are required to fill in the following required fields:

• Custom SQL Test name.

• Custom SQL Query

• Incident Levels

Upon completing the required fields to create an On-Demand monitor for Custom SQL, users can complete the setup process by choosing either of the following confirmation buttons based on their preferred use case: Save Preferences: 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 & 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 page and select "Run manually" through the ellipsis (︙) menu.

Set Up Monitoring for Scheduled, Group-by monitor

Adding a Group-by monitor will allow you to run a custom SQL test with the ability to define a specific segment in your data that has failed. You can select a column during the setup process and values from this column can be selected as segments for the monitor.

Example of a notification from a custom SQL monitor with a group-by column selected with segment which triggered the incident.

First, add your custom query into the text box and click on Validate query. If the query is a valid statement that can be run, Query successfully validated will show up below the text box.

Write a valid SQL statement and validate the query.

In the Result Set Group By Column section, the set of resulting columns returned by the query will be listed. Within this dropdown, you will need to select the column from the query result that contains the values to group by.

List of columns from the result set.

In the next section Result Set Mapping, you will need to select the table from the data source that you want the distinct values to be pulled from and used in the group-by. After selecting the table, you can select the column. Then, click on Fetch values to get a list of values in the next section.

Select the column and click on Fetch values.

A list of distinct values will be retrieved from the selected column. Select the values you want to add the monitor for.

Note that if the distinct values in your selected column exceeds more than 100, you will not be able to add that as a group-by column. Please reach out to us if support is required on this.

Select the values to used in the group-by monitor.

After that, select the incident level and notification setting that you want for the monitor.

Set Up Monitoring for On-demand, Group-by monitor

The workflow for the on-demand monitor will be similar to the one for the group-by monitor, except the below.

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

• Save Preferences: 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 & 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 page and select "Run manually" through the ellipsis (︙) menu.

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