8 Replies Latest reply on Aug 11, 2016 8:46 AM by Zach Krzyzanowski

    VizAlert Not Being Triggered for Extract Failures

    Zach Krzyzanowski

      I'm using the Email Users Whose Extracts fail alert provided here: Example: Email users whose extracts fail to refresh

       

      My VizAlerts from this workbook send without issue when using the 'test_alert' trigger on either view in that workbook, so it looks like the Python script is working properly and schedule appropriately on the server.  However, alerts aren't being triggered on their own based on subscription.  I've tried hourly and 15 minute intervals for subscriptions but they don't appear to be sending. 

        • 1. Re: VizAlert Not Being Triggered for Extract Failures
          Matt Coles

          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 preventing the schedule from "running".

          • 2. Re: VizAlert Not Being Triggered for Extract Failures
            Zach Krzyzanowski

            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

              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
                Zach Krzyzanowski

                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

                  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
                    Zach Krzyzanowski

                    Hi Jonathon, I did neglect to mention that, but yes, I have subscribed to the trigger view:

                     

                    Screen Shot 2016-08-11 at 9.30.05 AM.png

                    • 7. Re: VizAlert Not Being Triggered for Extract Failures
                      Matt Coles

                      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:

                       

                      2016-08-11_7-14-35.jpg

                       

                      Did I win?

                      • 8. Re: VizAlert Not Being Triggered for Extract Failures
                        Zach Krzyzanowski

                        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!