# Set Up Custom SQL Monitors

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](#set-up-monitoring-pop-up-scheduled-normal) or one that has a [`Group-by`](#set-up-monitoring-for-scheduled-group-by-monitor) column selected as a segmentation.

**How to Set Up a Custom SQL Monitor**

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

<figure><img src="https://1779874722-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FTw0qpCVzfrIXqS4FEg4T%2Fuploads%2Fgit-blob-d5014c204a18cbf4a94d5857e8bb4fe2c5627883%2Fimage.png?alt=media" alt=""><figcaption><p>Selection for Custom SQL card</p></figcaption></figure>

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

{% hint style="info" %}
The form fields will become available as you select the mandatory options.
{% endhint %}

### 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`

{% hint style="info" %}
To set your monitor as grouped by you can refer to [Grouped-By](https://docs.decube.io/data-quality/set-up-grouped-by-monitors#configure-on-demand-schedule-monitor-grouped-by-for-custom-sql) documentation.
{% endhint %}

* Click **“Proceed to Monitor Setup”** to continue.

<figure><img src="https://1779874722-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FTw0qpCVzfrIXqS4FEg4T%2Fuploads%2Fgit-blob-4d103cf5b8ef0845e7433080d4c1d120f5929535%2Fimage%20(271).png?alt=media" alt=""><figcaption><p>Grouped-by diabled</p></figcaption></figure>

<figure><img src="https://1779874722-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FTw0qpCVzfrIXqS4FEg4T%2Fuploads%2Fgit-blob-d362620fe46aeb1e918daf120a8e5408fe0e535c%2Fimage.png?alt=media" alt=""><figcaption><p>Grouped-by enabled</p></figcaption></figure>

### Step 2: Configure: Schedule Monitor

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

<figure><img src="https://1779874722-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FTw0qpCVzfrIXqS4FEg4T%2Fuploads%2Fgit-blob-0e3dc2f15af433a701544eb7ce5fb730c8cd0372%2Fimage.png?alt=media" alt=""><figcaption><p>Overview from previous selection</p></figcaption></figure>

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:

{% hint style="info" %}

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

* Able to add a test name to differentiate monitors created
  {% endhint %}

* **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.

{% hint style="info" %}
Note that if you modify the query after validating it, you will need to re-validate the query again.
{% endhint %}

<figure><img src="https://1779874722-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FTw0qpCVzfrIXqS4FEg4T%2Fuploads%2Fgit-blob-ed7985b3849bbd22851c9c284a79103869721c6f%2Fimage.png?alt=media" alt=""><figcaption><p>Overview for Custom SQL Query</p></figcaption></figure>

### 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**

<figure><img src="https://1779874722-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FTw0qpCVzfrIXqS4FEg4T%2Fuploads%2Fgit-blob-3d64196506fc41e74ea7482fe0c14ce8cf2b0f77%2Fimage%20(285).png?alt=media" alt=""><figcaption><p>Setting-up custom alert/notification</p></figcaption></figure>

* 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`

<figure><img src="https://1779874722-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FTw0qpCVzfrIXqS4FEg4T%2Fuploads%2Fgit-blob-faa54adcbc2599b545c0c19e02e1726cfc740bf9%2Fimage.png?alt=media" alt=""><figcaption><p>Set-up On demand monitor</p></figcaption></figure>

* 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.

<figure><img src="https://1779874722-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FTw0qpCVzfrIXqS4FEg4T%2Fuploads%2Fgit-blob-068680c5c482db394f3b2ed02cdd5582e0fe2380%2Fimage.png?alt=media" alt=""><figcaption><p>Selection from previous page</p></figcaption></figure>

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:

{% hint style="info" %}

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

* Able to add a test name to differentiate monitors created
  {% endhint %}

* **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.

<figure><img src="https://1779874722-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FTw0qpCVzfrIXqS4FEg4T%2Fuploads%2Fgit-blob-17603e05a7dd8f1d48d44948aa86f454aeee8912%2Fimage.png?alt=media" alt=""><figcaption><p>Overview for SQL Query validation</p></figcaption></figure>

{% hint style="info" %}
**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](#get-notified-custom-alert)

{% endhint %}

* 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](https://docs.decube.io/data-quality/set-up-grouped-by-monitors#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

```sql
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

```sql
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

```sql
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
```