-
1. Re: VizAlert Not Being Triggered for Extract Failures
Matt Coles
Aug 10, 2016 12:08 PM
Make sure that in the logs, you're seeing something like this:
"2016-07-31 21:48:20 - [DEBUG] - get_views - PostgreSQL repository returned 35 rows"
If it says "0 rows" then it means your schedules aren't being recognized by VizAlerts. You'd want to check your schedules to ensure they're disabled, that they're named properly and that the db.query filter at the bottom of the vizalerts.config file is set up to properly match their names.
If you do see the row count coming back with the right number of alerts you're running, then ensure your start/stop times aren't ".
-
2. Re: VizAlert Not Being Triggered for Extract Failures
I'm getting "0 rows" in my logs. I named the Alerts according to the install docs, so for example: Alerts - [15 Minutes]
The subscription is disabled and I do have 1 subscription that is enabled as per the docs. It looks like the query in config/vizalerts.yaml is not returning any rows for me, even though it only appears to be checking for a name starting with _lerts and that it is disabled:
SELECT
s.id AS subscription_id ,
v.id AS view_id ,
cv.id AS customized_view_id ,
COALESCE(v.name || '/' || cv.url_id ,
v.name) AS view_name ,
COALESCE(w.repository_url || '/' || v.sheet_id || '/' || su_cust.name || '@' || d.name || '/' || cv.url_id ,
w.repository_url || '/' || v.sheet_id) -- determine URL for a customized view
AS view_url_suffix ,
w.id AS workbook_id ,
w.repository_url AS workbook_repository_url ,
p.id AS project_id ,
p.name AS project_name ,
st.id AS site_id ,
CASE
WHEN st.url_namespace IS NULL THEN 'Default'
WHEN st.url_namespace = '' THEN 'Default'
ELSE st.url_namespace
END AS site_name ,
v.owner_id AS view_owner_id ,
s.user_id AS subscriber_user_id ,
su_sub.name AS subscriber_sysname ,
dom_sub.name AS subscriber_domain ,
su_sub.email AS subscriber_email ,
l_sub.name AS subscriber_license ,
su.email AS owner_email ,
su.name AS owner_sysname ,
su.friendly_name AS owner_friendly_name ,
s.schedule_id AS schedule_id ,
true AS force_refresh ,
s.is_test ,
COALESCE(date_trunc('second', s.test_alert_timestamp), sch.run_next_at)
AS run_next_at ,
COALESCE(
/*
Give our best guess as to when a newly scheduled alert would have last run
We're basically making this up, but this is only used for timeout values on the first run
*/
CASE
WHEN sch.schedule_type = 0 THEN sch.run_next_at - sch.minute_interval * INTERVAL '1 minute'
WHEN sch.schedule_type = 1 THEN sch.run_next_at - INTERVAL '1 day'
WHEN sch.schedule_type = 2 THEN sch.run_next_at - INTERVAL '7 days'
WHEN sch.schedule_type = 3 THEN sch.run_next_at - INTERVAL '30 days'
ELSE sch.run_next_at - INTERVAL '1 day' --default to a day ago
END ,
date_trunc('second', (s.test_alert_timestamp - interval '30 minutes'))
) AS ran_last_at , -- used for initial timeouts only
60 AS timeout_s , -- placeholder, ignore this
1 AS data_retrieval_tries , -- placeholder, ignore this
'simple' AS alert_type -- placeholder, ignore this
FROM (
SELECT
s.id ,
s.user_id ,
s.subject ,
false is_test ,
sv.repository_url ,
s.schedule_id ,
NULL test_alert_timestamp
FROM subscriptions s
LEFT JOIN subscriptions_views sv
ON s.id = sv.subscription_id
UNION
SELECT
MIN((-1 * c.id))
id ,
c.user_id ,
v.name subject ,
true is_test ,
v.repository_url ,
-1 schedule_id ,
MAX(c.updated_at)
test_alert_timestamp
FROM comments c
INNER JOIN views v
ON c.commentable_id = v.id
AND c.commentable_type = 'View'
AND c.user_id = v.owner_id
AND LOWER(c.comment) = 'test_alert'
WHERE c.updated_at > current_timestamp - interval '5 minutes'
GROUP BY c.user_id ,
v.name ,
v.repository_url
) s
INNER JOIN users u_sub
ON s.user_id = u_sub.id
INNER JOIN system_users su_sub
ON u_sub.system_user_id = su_sub.id
INNER JOIN licensing_roles l_sub
ON u_sub.licensing_role_id = l_sub.id
INNER JOIN domains dom_sub
ON su_sub.domain_id = dom_sub.id
LEFT JOIN subscriptions_customized_views scv
ON s.id = scv.subscription_id
LEFT JOIN customized_views cv
ON scv.customized_view_id = cv.id
LEFT JOIN users u_cust
ON cv.creator_id = u_cust.id
LEFT JOIN system_users su_cust
ON u_cust.system_user_id = su_cust.id
LEFT JOIN schedules sch
ON s.schedule_id = sch.id
INNER JOIN views v
ON s.repository_url = v.repository_url
AND u_sub.site_id = v.site_id
INNER JOIN workbooks w
ON v.workbook_id = w.id
INNER JOIN sites st
ON v.site_id = st.id
INNER JOIN projects p
ON w.project_id = p.id
INNER JOIN users u
ON v.owner_id = u.id
INNER JOIN system_users su
ON u.system_user_id = su.id
INNER JOIN domains d
ON su.domain_id = d.id
WHERE 1 = 1
AND (
(s.is_test = true)
OR
-- ######################################################
-- ######## Editable Filters #########
-- ######################################################
(
LOWER(sch.name) LIKE '_lerts%' -- only Alerts schedules ("_" single-character wildcard allows you to substitute Cyrillic capital A, U+0410 for sorting purposes)
--AND st.name IN ('Default','TestSite') -- allowed Sites
--AND p.name IN ('Sales') -- allowed Projects
--AND su_sub.name IN ('mcoles', 'nkamkolkar') -- allowed Users
--AND st.name = 'administration'
--
-- ######################################################
-- ######## DO NOT EDIT FILTERS BELOW THIS LINE #########
-- ######################################################
AND sch.active = 'f' -- only disabled schedules
)
)
AND coalesce(su_sub.email, '') <> '' -- only accounts with valid emails (no service accts)
ORDER BY
sch.run_next_at ,
sch.priority DESC
-
3. Re: VizAlert Not Being Triggered for Extract Failures
Matt Coles
Aug 10, 2016 3:07 PM
Hmm, odd. The name of the schedule and the filter in the query text look right to me. Again though, I'd double check the start/stop times on the schedule, since if they're set incorrectly it can cause the schedule think that it is never time for it to run.
You can troubleshoot further by running the query manually as Custom SQL in Tableau or in some other PostgreSQL query client application such as PGadmin III to tweak it and see what results you get. Basically, remove the name and disable filters from the WHERE clause, and see if your subscription shows. Then you can add in the filters again until you narrow down what's preventing your subscription from being returned.
-
4. Re: VizAlert Not Being Triggered for Extract Failures
I double checked the start/stop times. The schedule is running every 15 minutes between 12:00am and 11:45pm.
I also tried running the query with filters removed and still get 0 rows returned. Here's what I ran:
SELECT
s.id AS subscription_id ,
v.id AS view_id ,
cv.id AS customized_view_id ,
COALESCE(v.name || '/' || cv.url_id ,
v.name) AS view_name ,
COALESCE(w.repository_url || '/' || v.sheet_id || '/' || su_cust.name || '@' || d.name || '/' || cv.url_id ,
w.repository_url || '/' || v.sheet_id) -- determine URL for a customized view
AS view_url_suffix ,
w.id AS workbook_id ,
w.repository_url AS workbook_repository_url ,
p.id AS project_id ,
p.name AS project_name ,
st.id AS site_id ,
CASE
WHEN st.url_namespace IS NULL THEN 'Default'
WHEN st.url_namespace = '' THEN 'Default'
ELSE st.url_namespace
END AS site_name ,
v.owner_id AS view_owner_id ,
s.user_id AS subscriber_user_id ,
su_sub.name AS subscriber_sysname ,
dom_sub.name AS subscriber_domain ,
su_sub.email AS subscriber_email ,
l_sub.name AS subscriber_license ,
su.email AS owner_email ,
su.name AS owner_sysname ,
su.friendly_name AS owner_friendly_name ,
s.schedule_id AS schedule_id ,
true AS force_refresh ,
s.is_test ,
COALESCE(date_trunc('second', s.test_alert_timestamp), sch.run_next_at)
AS run_next_at ,
COALESCE(
/*
Give our best guess as to when a newly scheduled alert would have last run
We're basically making this up, but this is only used for timeout values on the first run
*/
CASE
WHEN sch.schedule_type = 0 THEN sch.run_next_at - sch.minute_interval * INTERVAL '1 minute'
WHEN sch.schedule_type = 1 THEN sch.run_next_at - INTERVAL '1 day'
WHEN sch.schedule_type = 2 THEN sch.run_next_at - INTERVAL '7 days'
WHEN sch.schedule_type = 3 THEN sch.run_next_at - INTERVAL '30 days'
ELSE sch.run_next_at - INTERVAL '1 day' --default to a day ago
END ,
date_trunc('second', (s.test_alert_timestamp - interval '30 minutes'))
) AS ran_last_at , -- used for initial timeouts only
60 AS timeout_s , -- placeholder, ignore this
1 AS data_retrieval_tries , -- placeholder, ignore this
'simple' AS alert_type -- placeholder, ignore this
FROM (
SELECT
s.id ,
s.user_id ,
s.subject ,
false is_test ,
sv.repository_url ,
s.schedule_id ,
NULL test_alert_timestamp
FROM subscriptions s
LEFT JOIN subscriptions_views sv
ON s.id = sv.subscription_id
UNION
SELECT
MIN((-1 * c.id))
id ,
c.user_id ,
v.name subject ,
true is_test ,
v.repository_url ,
-1 schedule_id ,
MAX(c.updated_at)
test_alert_timestamp
FROM comments c
INNER JOIN views v
ON c.commentable_id = v.id
AND c.commentable_type = 'View'
AND c.user_id = v.owner_id
AND LOWER(c.comment) = 'test_alert'
WHERE c.updated_at > current_timestamp - interval '5 minutes'
GROUP BY c.user_id ,
v.name ,
v.repository_url
) s
INNER JOIN users u_sub
ON s.user_id = u_sub.id
INNER JOIN system_users su_sub
ON u_sub.system_user_id = su_sub.id
INNER JOIN licensing_roles l_sub
ON u_sub.licensing_role_id = l_sub.id
INNER JOIN domains dom_sub
ON su_sub.domain_id = dom_sub.id
LEFT JOIN subscriptions_customized_views scv
ON s.id = scv.subscription_id
LEFT JOIN customized_views cv
ON scv.customized_view_id = cv.id
LEFT JOIN users u_cust
ON cv.creator_id = u_cust.id
LEFT JOIN system_users su_cust
ON u_cust.system_user_id = su_cust.id
LEFT JOIN schedules sch
ON s.schedule_id = sch.id
INNER JOIN views v
ON s.repository_url = v.repository_url
AND u_sub.site_id = v.site_id
INNER JOIN workbooks w
ON v.workbook_id = w.id
INNER JOIN sites st
ON v.site_id = st.id
INNER JOIN projects p
ON w.project_id = p.id
INNER JOIN users u
ON v.owner_id = u.id
INNER JOIN system_users su
ON u.system_user_id = su.id
INNER JOIN domains d
ON su.domain_id = d.id
WHERE 1 = 1
AND (
(s.is_test = true)
OR
-- ######################################################
-- ######## Editable Filters #########
-- ######################################################
(
-- LOWER(sch.name) LIKE '_lerts%' -- only Alerts schedules ("_" single-character wildcard allows you to substitute Cyrillic capital A, U+0410 for sorting purposes)
--
-- --AND st.name IN ('Default','TestSite') -- allowed Sites
-- --AND p.name IN ('Sales') -- allowed Projects
-- --AND su_sub.name IN ('mcoles', 'nkamkolkar') -- allowed Users
-- --AND st.name = 'administration'
-- --
-- -- ######################################################
-- -- ######## DO NOT EDIT FILTERS BELOW THIS LINE #########
-- -- ######################################################
sch.active = 'f' -- only disabled schedules
)
)
AND coalesce(su_sub.email, '') <> '' -- only accounts with valid emails (no service accts)
ORDER BY
sch.run_next_at ,
sch.priority DESC
-
5. Re: VizAlert Not Being Triggered for Extract Failures
Jonathan Drummey
Aug 11, 2016 2:45 AM
Hi Zach,
The one thing I haven't seen so far in your description is an explicit statement that you've subscribed the trigger view to the alert schedule. You can run test_alert without having a subscription set, so if you haven't subscribed the view that could explain the behavior you are seeing.
Jonathan
-
6. Re: VizAlert Not Being Triggered for Extract Failures
Hi Jonathon, I did neglect to mention that, but yes, I have subscribed to the trigger view:
-
7. Re: VizAlert Not Being Triggered for Extract Failures
Matt Coles
Aug 11, 2016 7:16 AM
AH! I have forgotten the one that bites my users the most often. Let me go ahead and put all my money on...
you subscribed to the entire Workbook, rather than the View!
VizAlerts requires you to subscribe to the View, not the Workbook:
Did I win?
-
8. Re: VizAlert Not Being Triggered for Extract Failures
Ding ding ding! That was it. I had subscribed to all views hoping that you would trigger it and then narrow down from there. Subscribing to only the alert view did the trick!
Thanks for the help Matt!
