11 Replies Latest reply on Oct 11, 2016 6:28 PM by Justin D'Cruze

    Backgrounder ( wrong Extract Failures' Owners)

    abdelmajid elmounjid

      Hi,

       

      We are currently generating a report out of our server backgrounder where get :

      we use that report to reach out to users whose Extracts Fail to refresh that way they can go and  rectify the errors.

      The issue that we just start facing is that we are getting some of the Owner's names attached as they are responsible for some of the Extracts Failures, but in the reality they don't have anything to do with that at all and they don't even have access to that. their names are in the list where they shouldn't be there at all.

       

      Please if you have any ideas what may cause that,

       

      Thank you

        • 1. Re: Backgrounder ( wrong Extract Failures' Owners)
          Toby Erkson

          Please attach the workbook so we can see how you are getting the info.  I would guess it's an incorrect table JOIN.

          • 2. Re: Backgrounder ( wrong Extract Failures' Owners)
            abdelmajid elmounjid

            I am using this query:

            SELECT

                bkjob.id                                            AS "Job Id" ,

                bkjob.job_name                                      AS "Job Name" ,

                bkjob.progress                                      AS "Progress" ,

                bkjob.finish_code                                   AS "Finish Code" ,

                bkjob.priority                                      AS "Priority" ,

                bkjob.notes                                         AS "Notes" ,

                bkjob.created_at                                    AS "Created At" ,

                bkjob.started_at                                    AS "Started At" ,

                bkjob.completed_at                                  AS "Completed At",

                bkjob.backgrounder_id                               AS "Backgrounder Id",

                bkjob.created_on_worker                             AS "Created On Worker" ,

                bkjob.processed_on_worker                           AS "Processed On Worker" ,

                sch.name                                            AS "Schedule Name" ,

                sch.schedule_type                                   AS "Schedule Type" ,

                sch.day_of_week_mask                                AS "Schedule Day of Week Mask" ,

                sch.day_of_month_mask                               AS "Schedule Day of Month Mask" ,

                sch.start_at_minute                                 AS "Schedule Start At Minute" ,

                sch.minute_interval                                 AS "Schedule Minute Interval" ,

                sch.end_at_minute                                   AS "Schedule End At Minute" ,

                sch.end_schedule_at                                 AS "Schedule End Schedule At" ,

                sch.scheduled_action                                AS "Schedule Action" ,

                sch.priority                                        AS "Schedule Priority" ,

                sch.active                                          AS "Schedule Active" ,

                current_timestamp                                   AS "Current Datetime" ,

                bkjob.site_id                                       AS "Site Id" ,

                s.name                                              AS "Site Name" ,

                bkjob.subtitle                                      AS "Item Type" ,

                bkjob.item_id                                       AS "Item Id" ,

                bkjob.item_name                                     AS "Item Name" ,

                bkjob.repository_url                                AS "Item Repository Url" ,

                bkjob.project_id                                    AS "Item Project Id" ,

                bkjob.owner_id                                      AS "Item Owner Id" ,

                su_own.name                                         AS "Item Owner Name" ,

                su_own.friendly_name                 AS "Friendly Name",

                p.name                                              AS "Project Name"

               

            FROM

                (

                SELECT

                    bj.id ,

                    bj.job_name ,

                    bj.progress ,

                    bj.finish_code ,

                    bj.priority ,

                    bj.notes ,

                    bj.created_at ,

                    bj.started_at ,

                    bj.completed_at ,

                    bj.site_id ,

                    bj.subtitle ,

                    bj.backgrounder_id ,

                    bj.created_on_worker ,

                    bj.processed_on_worker ,

                    bj.correlation_id ,

                    COALESCE(d.repository_url, w.repository_url)        AS "repository_url" ,

                    COALESCE(d.project_id, w.project_id)                AS "project_id" ,

                    COALESCE(d.owner_id, w.owner_id)                    AS "owner_id" ,

                    COALESCE(dc.id, w.id)                               AS "item_id" ,

                    COALESCE(d.name, w.name)                            AS "item_name"

                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'

                 ) AS bkjob

                LEFT JOIN tasks t

                    ON bkjob.correlation_id = t.id

                LEFT JOIN schedules sch

                    ON t.schedule_id = sch.id

                LEFT JOIN sites s

                    ON bkjob.site_id = s.id

                LEFT JOIN users u_own

                    ON bkjob.owner_id = u_own.id

                LEFT JOIN system_users su_own

                    ON u_own.system_user_id = su_own.id

                LEFT JOIN projects p

                    ON bkjob.project_id = p.id

             

             

             

             

             

            I have highlighted the Owner name that is causing the issue, if I upload the workbook to you it won't open because it's connected to the data source using password

            Thank you

            please let me know if that helps

            • 3. Re: Backgrounder ( wrong Extract Failures' Owners)
              Matt Coles

              "Name" is an imprecise way to map the background_job record to the workbook/datasource. Check for another workbook/datasource with a duplicate name. I've had to deal with this myself.

              • 4. Re: Backgrounder ( wrong Extract Failures' Owners)
                Toby Erkson

                Agreed w/Matt (I remember that situation).

                 

                Yes, we know the workbook would need the connection to be edited for it to work in our environment.

                • 5. Re: Backgrounder ( wrong Extract Failures' Owners)
                  abdelmajid elmounjid

                  would you mind explaining to me little bit more please,

                  Thank you

                  • 6. Re: Backgrounder ( wrong Extract Failures' Owners)
                    Matt Coles

                    This criteria:

                        FROM background_jobs bj

                            LEFT JOIN datasources d

                                ON bj.title = d.name

                     

                    ...is not precise enough to identify the exact datasource (or workbook, from the other join below it), as there can be two with the same name. So if a failure occurs on one of the two, but there are two datasources named that, the join will bring two records into the results, one for each item of content. The owner may be different on the other, which is what can then cause this.

                     

                    The stock Admin view avoids the problem in an unfortunate way, in that it simply doesn't tell you who owns what. So once you try and use the same logic to find out who owns it, you run into this problem.

                    • 7. Re: Backgrounder ( wrong Extract Failures' Owners)
                      Justin D'Cruze

                      Have you tried using the hist tables (data from hist_tasks) instead? There's a bit of joining to to do with the hist tables but I find the background_jobs table in its current state is no good for getting this sort of information (whereas the hist tables joined via correct IDs should provide consistent results)

                      • 8. Re: Backgrounder ( wrong Extract Failures' Owners)
                        abdelmajid elmounjid

                        can you please provide me some guidance, I am not that strong with it,

                         

                        Thank you

                        • 9. Re: Backgrounder ( wrong Extract Failures' Owners)
                          Justin D'Cruze

                          This is not the full hist table join but a simplified example, could do something like this to get the owner info of workbook refreshes (need to add hist_datasources to the join for published data sources)

                           

                          You would need to filter on event type to show refreshes only, and probably write a calc to check the "details" field to see if the extract failed or succeeded.

                           

                          I'd be interested to know if anyone has any other (simpler) ideas as well.

                          • 10. Re: Backgrounder ( wrong Extract Failures' Owners)
                            Matt Coles

                            The historical_events tables, unfortunately, don't record whether the operation was a failure or not, nor how long it took--both bits of info I always seem to need when it comes to monitoring extracts.

                            • 11. Re: Backgrounder ( wrong Extract Failures' Owners)
                              Justin D'Cruze

                              Yeah it really isn't ideal.

                              My current method of working out whether the refresh was successful is pretty crude -  basically filter historical_events.details to check whether it contains 'Finished refresh of extracts'