Query Monitors

Sundeck Query Monitors allow you to configure alerts against recent and currently-running query history (using Snowflake’s INFORMATION_SCHEMA.QUERY_HISTORY() table function), and take an action based on defined conditions.

Alerts run once per minute and only one alert is sent per query monitor per matching query.

Query monitors can only be created by Snowflake account administrators (i.e. users with the ACCOUNTADMIN role)

• Condition Valid WHERE clause that can be applied to a query of the INFORMATION_SCHEMA.QUERY_HISTORY() table function.

• Action When the condition evaluates to true, the action(s) defined on the query monitor are executed.

Query monitors support the following actions:

• Cancel Query Cancels the query which matched the Condition.

• Notify Author Send a notification to the user who issued the query.

• Notify Other Send a notification to a list of users other than the user who authored the query.

Notifications can be sent either as email or slack message. When enabling “Notify Author”, Sundeck will send a direct message to the user who ran the query which matched the query monitor condition. To link the Snowflake user to the Slack user, the Snowflake user’s email address must be the same as the email address of the Slack user. If the Snowflake user does not have an email set, the query monitor will not attempt to send a Slack message. Please note that user_email user property in Snowflake’s user object is optional.

No configuration is necessary to start using email alerts. However, sending Slack notificatons requires enabling additional features. Please see Slack Notifications for detailed explanation on how to do it.

Sundeck provides two ways to create query monitors: Sundeck SaaS User interface and SQL stored procedures.

Sundeck provides the following stored procedures for creating, using and managing query monitors:

• CREATE_QUERY_MONITOR
• UPDATE_QUERY_MONITOR
• DELETE_QUERY_MONITOR

Sundeck comes with a set of predefined query monitors and you can start taking advantage of them immediately. Predefined monitors can be modified and/or deleted.

To create a query monitor, please follow the steps below.

1. Click on “Query Monitors” link in the left-hand navigation menu and then “Create Monitor” button in the right most corner of the screen.
2. “Create Query Monitor” form will appear once you’ve clicked on “Create Monitor” button.
3. Fill in the form and when done click the “Save” button.
4. After a few seconds, you’ll be taken back to the list of Query Monitors, and you’ll see your new query monitor listed.

Same query monitor created with SQL:

vicky#COMPUTE_WH@(no database).(no schema)>use database opscenter;
+----------------------------------+                                            
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
1 Row(s) produced. 

vicky#COMPUTE_WH@OPSCENTER.(no schema)>call ADMIN.CREATE_QUERY_MONITOR(
                                        'cancel_long_running_queries',
                                        'EXECUTION_TIME > 300000 AND NOT query_text ilike \'%execute streamlit\'',
                                        True,
                                        'EMAIL',
                                        'robert@sundeck.io',           
                                        'EMAIL',                       
                                        True
                                       );

+----------------------+                                                        
| CREATE_QUERY_MONITOR |
|----------------------|
| NULL                 |
+----------------------+
1 Row(s) produced.