Documentation
Toggle Dark/Light/Auto modeToggle Dark/Light/Auto modeToggle Dark/Light/Auto modeBack to homepage

QUERY_MONITOR_ACTIVITY

Sundeck REPORTING.QUERY_MONITOR_ACTIVITY view stores information about Query Monitors activity.

Column NameData TypeDescription
ACTION_TIMETIMESTAMP_NTZTime when query monitor was run and action taken if any
PROBE_NAMETEXTQuery Monitor name
QUERY_IDTEXTSnowflake query id
ACTIONS_TAKENVARIANTDescribes actions that were taken (query cancel and/or notifications)
OUTCOMETEXTMethod with others will be notified: email or slack

Example

We’ve configured Query Monitor that will cancel any CTAS query that has produced more than million rows.

Home Page

By querying REPORTING.QUERY_MONITOR_ACTIVITY view we can now find all the users who’s queries were cancelled by the query monitor we defined above:

nori#COMPUTE_WH@SUNDECK.REPORTING>SELECT 
                                       user_name, 
                                       start_time, 
                                       SUBSTRING(query_text, 1, 60)  
                                     FROM 
                                       snowflake.account_usage.query_history
                                     WHERE query_id in
                                       ( SELECT 
                                           query_id 
                                         FROM 
                                           native-app.reporting.query_monitor_activity 
                                         WHERE 
                                           actions_taken['CANCEL'] = true and 
                                           outcome ilike '%terminated%'
                                       )
                                     ;

+-----------+-------------------------------+--------------------------------------------------------------+
| USER_NAME | START_TIME                    | SUBSTRING(QUERY_TEXT, 1, 60)                                 |
|-----------+-------------------------------+--------------------------------------------------------------|
| TEST      | 2023-10-31 16:21:01.587 -0700 | create table test.public.new_orders as select * from orders; |
| NORI     | 2023-10-31 16:50:34.413 -0700 | create table test.public.lineitem as select * from lineitem; |
+-----------+-------------------------------+--------------------------------------------------------------+
2 Row(s) produced. Time Elapsed: 8.584s