QUERY_MONITOR_ACTIVITY
Sundeck REPORTING.QUERY_MONITOR_ACTIVITY view stores information about Query Monitors activity.
| Column Name | Data Type | Description |
|---|---|---|
| ACTION_TIME | TIMESTAMP_NTZ | Time when query monitor was run and action taken if any |
| PROBE_NAME | TEXT | Query Monitor name |
| QUERY_ID | TEXT | Snowflake query id |
| ACTIONS_TAKEN | VARIANT | Describes actions that were taken (query cancel and/or notifications) |
| OUTCOME | TEXT | Method with others will be notified: email or slack |
We’ve configured Query Monitor that will cancel any CTAS query that has produced more than million rows.
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:
vicky#COMPUTE_WH@OPSCENTER.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
opscenter.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; |
| VICKY | 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