3 Replies Latest reply on Nov 2, 2016 12:01 PM by Matt Coles

    VizAlerts Query Modification

    Shivaraj Sajjan

      Hi Team,

       

      I have installed VizAlerts Setup at our organisation.

       

      As suggested, I have used below code to fetch data from Postgress.

       

      FROM background_jobs bj

              LEFT JOIN datasources d

                  ON bj.title = d.name

                      AND bj.subtitle = 'Datasource'

              LEFT JOIN data_connections dc

                  ON d.id = dc.owner_id

                      AND dc.owner_type = 'Datasource'

              LEFT JOIN workbooks w

                  ON bj.title = w.name

                      AND bj.subtitle = 'Workbook'

          WHERE age(bj.created_at) <= INTERVAL '<Parameters.Days Data To Retrieve> days'

       

      As per this logic, its joining with workbook/datasource name here.

      My question is: If there are two workbooks having same name published by different users. Out of which one is running successful where as failing another. But with this above logic, its sending alert notification to both publishers.

       

      Is there any way to join with workbook/datasource ID to avoid conflict, with then failed extract owner alone receives alert notification.

       

      Much appreciated your comments... Thank you

       

      Regards,

      Shivaraj

        • 1. Re: VizAlerts Query Modification
          Matt Coles

          Yes, that's a real pain in the neck. There's a column in background_jobs called "correlation_id" that joins to the Tasks table. From there, you can join to workbooks or datasources on the obj_id and obj_type columns. That provides a better result in most cases, but I have noticed that if you refresh your extracts on an ad-hoc basis with tabcmd, it won't show up in the results using that method, presumably because it has no permanent scheduled "task" record, but generates one on the fly.

          • 2. Re: VizAlerts Query Modification
            Shivaraj Sajjan

            Thanks Matt. I will check this workaround and will get back to you for any issues. Thank you

             

            Regards,

            Shivaraj

            • 3. Re: VizAlerts Query Modification
              Matt Coles

              You really just can't win right now with the background_jobs or historical_events tables for monitoring what's up with your backgrounder processes. Historical events won't show what's currently happening, only what has already happened, but worst of all it lacks a critical metric for measuring resource consumption--the duration of the task. Background_jobs gives you everything that is happening right now, and has happened up to 30 days in the past, with the duration information. But it's method for joining to a Task record is flawed, since even switching a schedule on the workbook or datasource in question, or refreshing it manually, will cause the CorrelationId to no longer match up with a TaskId in the Tasks table. And while there's a hist_tasks table that contains historical Task ids, it doesn't contain any other information that would allow you to map it to a workbook or datasource! You'd be stuck again mapping it based on the Title field to the name of the item...an imprecise method that results in the wrong answer when there are two items named the same. ARRRGGGG.

               

              I'm taking a hybrid approach of using background_jobs when correlation_id does map to a valid Task, then falling back to the Title method if it's no longer available. Increases complexity and decreases performance, but hopefully there'll at least be better information. I'll post my version soon in another thread.