1 2 Previous Next 24 Replies Latest reply on May 4, 2018 1:11 PM by Vijay Medipelli Go to original post
      • 15. Re: Missing Postgres data
        Kristen Connolly

        Thanks Dan. I wonder if this is related to Nolan's problems after all?

        • 16. Re: Missing Postgres data
          Dan Scott

          The only thing I notice about this query that looks suspicious is this part:

           

          SELECT

          upper(su.name) as su_name

          ,max(su.email) as su_email

          ,max(su.friendly_name) as su_friendly_name

          FROM

          public.users u

          INNER JOIN public.system_users su ON (u.system_user_id = su.id)

          GROUP BY

          upper(su.name)

           

          The reason I find this suspicious is that it is clearly designed to deal with instances when the name appears in system_users more than once.  I believe there exists a unique index on (name, domain_id) so the ability to repeat the same name is somewhat circumscribed (but possible). But choosing upper(su.name), max(su.email), max(su.friendly_name) could easily select the name of one user, the email of a different user, and the friendly_name of a third---Not what you want, I imagine.

           

          I don't know if you even have any duplicated names in system_users, and I don't really see how this issue would result in missing data (wrong data, yes, but missing?). However, at the moment it's the only thing I see.

           

          Come to think of it, this query doesn't seem to limit the events to any specific type, so perhaps this query is being used to feed into some other logic.  In that case the fact that all users sharing a particular name (or different capitalization of it) will be munged together might create a problem with whatever you are doing further down the analysis pipeline.

           

          Maybe you can check for duplicate names for users where a problem is being observed? Just in case this might be relevant.

          • 17. Re: Missing Postgres data
            Andrew Macey

            This sounds like the issue I noticed in Server 9.1.3 that didn't exist in my earlier 8.3.  What I found is that:

            1.) "_http_requests" is accurate (but only covers a few days' history)

            2.) "_views_stats" is accurate but only covers the cumulative total, not the time series detail.

            3.) "historical_events" is missing a bunch or records, resulting in intermittent missing usage records. In my case this resulted in very noticeable missing usage data (>50% I'd say).

             

            For anyone piling tons of money into dashboard creation, only to find that usage reports (based on "historical_events") show lower than expected usage, this missing data is a major issue!

             

            http://www.tableau.com/support/releases/9.1.8  resolved issues states "In some cases, user view data was not properly written to the Tableau Server repository database, which resulted in incorrectly displayed user view statistics in the Tableau Server administrative views." Also fixed in 9.2.6.

             

            What is your server version?

            • 18. Re: Missing Postgres data
              Luke Brady

              Nolan Gaskill Compare the query you wrote to the joins I've compiled.

              • 19. Re: Missing Postgres data
                Toby Erkson

                Luke Brady wrote:

                 

                Nolan Gaskill Compare the query you wrote to the joins I've compiled.

                Given the large number of queries available it would be helpful to point out the specific one(s).

                • 20. Re: Missing Postgres data
                  Luke Brady

                  Hey Nolan Gaskill I have gone through the list of tables and joins I compiled and put together this query for you.  I want to stress - please run this in a non-prod first and further filter/tweak it as necessary.  This query appears to go back multiple months and if you have a large Tableau Server Deployment with thousands of users it could take a while to run.

                   

                  Please mark this reply as helpful if you find it so.

                   

                   

                  SELECT DISTINCT T96.created_at AS "Created_At (Hist Events)", T96.details AS "Details (Hist Events)", T96.duration_in_ms AS "Duration_In_Ms (Hist Events)", T96.hist_actor_site_id AS "Hist_Actor_Site_ID (Hist Events)", T96.hist_actor_user_id AS "Hist_Actor_User_ID (Hist Events)", T96.hist_capability_id AS "Hist_Capability_ID (Hist Events)", T96.hist_comment_id AS "Hist_Comment_ID (Hist Events)", T96.hist_config_id AS "Hist_Config_ID (Hist Events)", T96.hist_data_connection_id AS "Hist_Data_Connection_ID (Hist Events)", T96.hist_datasource_id AS "Hist_Datasource_ID (Hist Events)", T96.hist_group_id AS "Hist_Group_ID (Hist Events)", T96.hist_licensing_role_id AS "Hist_Licensing_Role_ID (Hist Events)", T96.hist_project_id AS "Hist_Project_ID (Hist Events)", T96.hist_schedule_id AS "Hist_Schedule_ID (Hist Events)", T96.hist_tag_id AS "Hist_Tag_ID (Hist Events)", T96.hist_target_site_id AS "Hist_Target_Site_ID (Hist Events)", T96.hist_target_user_id AS "Hist_Target_User_ID (Hist Events)", T96.hist_task_id AS "Hist_Task_ID (Hist Events)", T96.hist_view_id AS "Hist_View_ID (Hist Events)", T96.hist_workbook_id AS "Hist_Workbook_ID (Hist Events)", T96.historical_event_type_id AS "Historical_Event_Type_ID (Hist Events)", T96.id AS "Id (Hist Events)", T96.is_failure AS "Is_Failure (Hist Events)", T96.worker AS "Worker (Hist Events)", T95.action_type AS "Action_Type (Hist Event Types)", T95.name AS "Name (Hist Event Types)", T95.type_id AS "Type_ID (Hist Event Types)", T86.id AS "Id (Hist Projects)", T86.name AS "Name (Hist Projects)", T86.project_id AS "Project_ID (Hist Projects)", T93.id AS "Id (Hist WB)", T93.name AS "Name (Hist WB)", T93.repository_url AS "Repository_Url (Hist WB)", T93.revision AS "Revision (Hist WB)", T93.size AS "Size (Hist WB)", T93.workbook_id AS "Workbook_ID (Hist WB)", T92.id AS "Id (Hist VW)", T92.name AS "Name (Hist VW)", T92.repository_url AS "Repository_Url (Hist VW)", T92.revision AS "Revision (Hist VW)", T92.view_id AS "View_ID (Hist VW)", T91_ACTOR.domain_name AS "Domain_Name (Hist Actor Users)", T91_ACTOR.email AS "Email (Hist Actor Users)", T91_ACTOR.hist_licensing_role_id AS "Hist_Licensing_Role_ID (Hist Actor Users)", T91_ACTOR.id AS "Id (Hist Actor Users)", T91_ACTOR.name AS "Name (Hist Actor Users)", T91_ACTOR.publisher_tristate AS "Publisher_Tristate (Hist Actor Users)", T91_ACTOR.site_admin_level AS "Site_Admin_Level (Hist Actor Users)", T91_ACTOR.system_admin_level AS "System_Admin_Level (Hist Actor Users)", T91_ACTOR.system_user_id AS "System_User_ID (Hist Actor Users)", T91_ACTOR.user_id AS "User_ID (Hist Actor Users)", T88_ACTOR.id AS "Id (Hist Actor Sites)", T88_ACTOR.name AS "Name (Hist Actor Sites)", T88_ACTOR.site_id AS "Site_ID (Hist Actor Sites)", T88_ACTOR.url_namespace AS "Url_Namespace (Hist Actor Sites)", T91_TARGET.domain_name AS "Domain_Name (Hist Target Users)", T91_TARGET.email AS "Email (Hist Target Users)", T91_TARGET.hist_licensing_role_id AS "Hist_Licensing_Role_ID (Hist Target Users)", T91_TARGET.id AS "Id (Hist Target Users)", T91_TARGET.name AS "Name (Hist Target Users)", T91_TARGET.publisher_tristate AS "Publisher_Tristate (Hist Target Users)", T91_TARGET.site_admin_level AS "Site_Admin_Level (Hist Target Users)", T91_TARGET.system_admin_level AS "System_Admin_Level (Hist Target Users)", T91_TARGET.system_user_id AS "System_User_ID (Hist Target Users)", T91_TARGET.user_id AS "User_ID (Hist Target Users)", T88_TARGET.id AS "Id (Hist Target Sites)", T88_TARGET.name AS "Name (Hist Target Sites)", T88_TARGET.site_id AS "Site_ID (Hist Target Sites)", T88_TARGET.url_namespace AS "Url_Namespace (Hist Target Sites)", T83.datasource_id AS "Datasource_ID (Hist DS)", T83.id AS "Id (Hist DS)", T83.name AS "Name (Hist DS)", T83.repository_url AS "Repository_Url (Hist DS)", T83.revision AS "Revision (Hist DS)", T83.size AS "Size (Hist DS)", T84.domain_name AS "Domain_Name (Hist Groups)", T84.group_id AS "Group_ID (Hist Groups)", T84.id AS "Id (Hist Groups)", T84.name AS "Name (Hist Groups)", T87.day_of_month_mask AS "Day_Of_Month_Mask (Hist Sched)", T87.day_of_week_mask AS "Day_Of_Week_Mask (Hist Sched)", T87.end_at_minute AS "End_At_Minute (Hist Sched)", T87.end_schedule_at AS "End_Schedule_At (Hist Sched)", T87.id AS "Id (Hist Sched)", T87.is_serial AS "Is_Serial (Hist Sched)", T87.minute_interval AS "Minute_Interval (Hist Sched)", T87.name AS "Name (Hist Sched)", T87.priority AS "Priority (Hist Sched)", T87.schedule_id AS "Schedule_ID (Hist Sched)", T87.schedule_type AS "Schedule_Type (Hist Sched)", T87.scheduled_action AS "Scheduled_Action (Hist Sched)", T87.start_at_minute AS "Start_At_Minute (Hist Sched)", T90.id AS "Id (Hist Tasks)", T90.priority AS "Priority (Hist Tasks)", T90.task_id AS "Task_ID (Hist Tasks)", T90.type AS "Type (Hist Tasks)", T271_ACTOR.activated_at AS "Activated_At (Sys Actor Users)", T271_ACTOR.activation_code AS "Activation_Code (Sys Actor Users)", T271_ACTOR.admin_level AS "Admin_Level (Sys Actor Users)", T271_ACTOR.asset_key_id AS "Asset_Key_ID (Sys Actor Users)", T271_ACTOR.auth_user_id AS "Auth_User_ID (Sys Actor Users)", T271_ACTOR.created_at AS "Created_At (Sys Actor Users)", T271_ACTOR.custom_display_name AS "Custom_Display_Name (Sys Actor Users)", T271_ACTOR.deleted_at AS "Deleted_At (Sys Actor Users)", T271_ACTOR.domain_id AS "Domain_ID (Sys Actor Users)", T271_ACTOR.email AS "Email (Sys Actor Users)", T271_ACTOR.friendly_name AS "Friendly_Name (Sys Actor Users)", T271_ACTOR.hashed_password AS "Hashed_Password (Sys Actor Users)", T271_ACTOR.id AS "Id (Sys Actor Users)", T271_ACTOR.keychain AS "Keychain (Sys Actor Users)", T271_ACTOR.lock_version AS "Lock_Version (Sys Actor Users)", T271_ACTOR.name AS "Name (Sys Actor Users)", T271_ACTOR.salt AS "Salt (Sys Actor Users)", T271_ACTOR.state AS "State (Sys Actor Users)", T271_ACTOR.sys AS "Sys (Sys Actor Users)", T271_ACTOR.updated_at AS "Updated_At (Sys Actor Users)", T271_TARGET.activated_at AS "Activated_At (Sys Target Users)", T271_TARGET.activation_code AS "Activation_Code (Sys Target Users)", T271_TARGET.admin_level AS "Admin_Level (Sys Target Users)", T271_TARGET.asset_key_id AS "Asset_Key_ID (Sys Target Users)", T271_TARGET.auth_user_id AS "Auth_User_ID (Sys Target Users)", T271_TARGET.created_at AS "Created_At (Sys Target Users)", T271_TARGET.custom_display_name AS "Custom_Display_Name (Sys Target Users)", T271_TARGET.deleted_at AS "Deleted_At (Sys Target Users)", T271_TARGET.domain_id AS "Domain_ID (Sys Target Users)", T271_TARGET.email AS "Email (Sys Target Users)", T271_TARGET.friendly_name AS "Friendly_Name (Sys Target Users)", T271_TARGET.hashed_password AS "Hashed_Password (Sys Target Users)", T271_TARGET.id AS "Id (Sys Target Users)", T271_TARGET.keychain AS "Keychain (Sys Target Users)", T271_TARGET.lock_version AS "Lock_Version (Sys Target Users)", T271_TARGET.name AS "Name (Sys Target Users)", T271_TARGET.salt AS "Salt (Sys Target Users)", T271_TARGET.state AS "State (Sys Target Users)", T271_TARGET.sys AS "Sys (Sys Target Users)", T271_TARGET.updated_at AS "Updated_At (Sys Target Users)" FROM historical_events T96 LEFT JOIN historical_event_types T95 ON T96.historical_event_type_id = T95.type_id LEFT JOIN hist_projects T86 ON T96.hist_project_id = T86.id LEFT JOIN hist_workbooks T93 ON T96.hist_workbook_id = T93.id LEFT JOIN hist_views T92 ON T96.hist_view_id = T92.id LEFT JOIN hist_users T91_ACTOR ON T96.hist_actor_user_id = T91_ACTOR.id LEFT JOIN hist_sites T88_ACTOR ON T96.hist_actor_site_id = T88_ACTOR.id LEFT JOIN hist_users T91_TARGET ON T96.hist_target_user_id = T91_TARGET.id LEFT JOIN hist_sites T88_TARGET ON T96.hist_target_site_id = T88_TARGET.id LEFT JOIN hist_datasources T83 ON T96.hist_datasource_id = T83.id LEFT JOIN hist_groups T84 ON T96.hist_group_id = T84.id LEFT JOIN hist_schedules T87 ON T96.hist_schedule_id = T87.id LEFT JOIN hist_tasks T90 ON T96.hist_task_id = T90.id LEFT JOIN system_users T271_ACTOR ON T91_ACTOR.system_user_id = T271_ACTOR.id LEFT JOIN system_users T271_TARGET ON T91_ACTOR.system_user_id = T271_TARGET.id

                  CC: Toby Erkson

                  • 21. Re: Missing Postgres data
                    Kristen Connolly

                    Luke, I pared down the massive query you pasted so that it only contained the fields I need (see below), and unfortunately, found that it still doesn't show our missing data. I wonder if the bug Dan mentioned was ever fixed? I saw a comment elsewhere on the web suggesting that only 6months of data get stored, but it's unclear whether that's true (nor have I determined where that setting may live). The "who has seen this view" page behind each TS view shows info that isn't showing up in the postgres data; I don't know where it's looking.

                    I have a case in with support now, so if I find out anything more, I'll post here.

                    SELECT DISTINCT

                    T96.created_at AS "Created_At (Hist Events)",

                    T91_ACTOR.name AS "Name (Hist Actor Users)",

                    T91_ACTOR.id     AS "Id (Hist Actor Users)",

                    T92.name AS "Name (Hist VW)",

                    T93.name AS "Name (Hist WB)",

                    _workbooks.name AS "_workbooks_name",

                    _workbooks.owner_name AS "owner_name",

                    _workbooks.project_name AS "project_name",

                    projects.name AS "projects project name"

                     

                    FROM historical_events T96

                    LEFT JOIN hist_views T92 ON T96.hist_view_id = T92.id

                    LEFT JOIN hist_workbooks T93 ON T96.hist_workbook_id = T93.id

                    LEFT JOIN hist_users T91_ACTOR ON T96.hist_actor_user_id = T91_ACTOR.id

                    LEFT JOIN hist_projects projects ON T96.hist_project_id= projects.id

                    LEFT JOIN _workbooks _workbooks ON ((T93.name = _workbooks.name) AND (T93.repository_url = _workbooks.workbook_url))

                    • 22. Re: Missing Postgres data
                      Luke Brady

                      Hey Kristen Connolly were you wanting just the "Who has seen this view" data?  Here it is:

                      SELECT DISTINCT T271.friendly_name AS "Friendly_Name (Sys Users)", T271.name AS "Name (Sys Users)", SUM(T298.nviews) AS "Nviews (VW Stats)", MAX(T298.time) AS "Time (VW Stats)", T301.name AS "Name (WB)", T297.name AS "Name (Views)" FROM views_stats T298 INNER JOIN users T290 ON T298.user_id = T290.id INNER JOIN views T297 ON T298.view_id = T297.id INNER JOIN workbooks T301 ON T297.workbook_id = T301.id INNER JOIN system_users T271 ON T290.system_user_id = T271.id GROUP BY T271.friendly_name, T271.name, T301.name, T297.name
                      • 23. Re: Missing Postgres data
                        Kristen Connolly

                        Thanks Luke Brady, but no, I was looking for the details underlying the view.

                         

                        I think we've solved the latest problem, though -- Tableau support confirmed this morning that the default settings for the postgres DB only saves 183 days of usage details. This is configurable, so we're going to change it, but this explains why people are on the "who has seen this view" list as viewing in early 2017 but aren't showing up when we expand our custom admin views to the beginning of the year.

                        • 24. Re: Missing Postgres data
                          Vijay Medipelli

                          Hello Kristen

                          Can you share me what setting you have done to store complete History in Postgresql and is there any particular reason Tableau has given you that why they store only 183 days of History due to Space issue?

                           

                          Appreciate your help..

                           

                          Thanks..

                          Vijay

                          1 2 Previous Next