Comment on page

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.

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 located to the left of the "Sort By Button".
Once clicked, a setup pop-up for Custom SQL monitors will emerge.
In the Set Up Monitoring pop-up, users are required to fill in the following required fields:
  • Custom SQL Test name.
  • Add Your Custom SQL Query
  • Validated Custom SQL Query
  • Incident Levels
After filling up the required fields, press the Save Preferences button and you are all good to go!

Add Your Custom SQL Query

Input the custom sql in the ADD YOUR CUSTOM SQL QUERY text box and press Validate query, this will issue a query validation on the database. Note that only a single read query will work in the validation.
Be aware that after validating your SQL Query and it becomes eligible for saving preferences, any subsequent edits to that validated query will automatically disable the ability to save preferences unless it is being validated again.

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
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
WHERE created_at BETWEEN
NOW() - '30mins'::INTERVAL AND NOW()
HAVING SUM(price * quantity) > 10000


The discrepancy between 2 tables
WITH sale AS (
SELECT SUM(price * quantity) as amount
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 amount FROM sale)
- (SELECT amount FROM in_flow)
AS discrepancy
SELECT discrepancy
FROM base
WHERE discrepancy > 10000