1 2 Previous Next 24 Replies Latest reply on May 4, 2018 1:11 PM by Vijay Medipelli

    Missing Postgres data

    nolan.gaskill

      My company has some administrative views which show Tableau Server URL hits by user ID using Tableau's Postrgres data.  It seems that these views previously worked as expected but starting at sometime in the past (unclear when) it appears to now only be capturing a fraction of the actual hits.  We've validated this by looking at the row level data and touching various dashboards to see if they are recorded.

       

      I'm wondering if anyone has any insights into recent changes or bugs that might be causing this problem or experienced similar problems?

        • 1. Re: Missing Postgres data
          Donna Coles

          Hi Nolan - if the query against the postgres db is using the historical_events table then this only captures by default 6 months worth of data I think.  There's a setting you can set via tabadmin to change the no of days being stored.  See #Tableau Server v8 History Tables | Tableau Love for info.  It may that you've now passed this threshold, so your older activity has now been lost.

          2 of 2 people found this helpful
          • 2. Re: Missing Postgres data
            nolan.gaskill

            Thanks, Donna.  It is unfortunately not old data but recent data that appears to be missing.  In looking into this a little further, it looks like maybe records are being created in the database when a dashboard is accessed but the worksheet name and url names are coming back as nulls.

            • 4. Re: Missing Postgres data
              Matt Coles

              To me this sounds like a querying issue. Could you share the workbook or datasource, or the customsql you're using to obtain the unexpected results?

              • 5. Re: Missing Postgres data
                Russell Christopher

                Nolan, you're probably leveraging http_requests, and the sheer amount of data dumped into this table has dropped dramatically over the last year or so.

                 

                In days past just about every interaction with the server got saved here - In other words, when you hit the login page, a record (or two) would be added...when you actually logged in, more records,,,when  you browsed vizzes and finally launvched one...yet more records...and then you'd see records associated with vizql doing it's rendering work.

                 

                MOST of that stuff no longer gets recorded - Anything associated with authentication / authorization and "doing stuff" in the portal is handled by VizPortal, and VizPortal no longer writes to http_requests. So all in all, http_requests is probably less useful than it used to be as a "one stop shopping" location for everything going on in the server....your report of "less data" may be a flavor of what I'm describing...

                2 of 2 people found this helpful
                • 6. Re: Missing Postgres data
                  nolan.gaskill

                  Hi Matt.  Sorry for the lateness of my reply, I had been travelling and this fell off my immediate radar.  The connection to the postgres data was published by one of our admins so I don't have access to the underlying custom SQL and I'm unable to share the workbook due to it containing PII.  I'll see if I can't get a copy of the SQL for you.

                  • 7. Re: Missing Postgres data
                    nolan.gaskill

                    Hi Russell.  What we are trying to do is determine the frequency with which specific users are hitting a given dashboard.  What seems odd is some of the hits we know are happening are being recorded but others are not and it does not seem as if there is a specific pattern to what is being kept.

                    • 8. Re: Missing Postgres data
                      Russell Christopher

                      Nolan, can you post the query you're using to attempt to figure this info out?

                      • 9. Re: Missing Postgres data
                        nolan.gaskill

                        I have received the below underlying SQL that is being used to connect to this data:

                         

                        --Historical View Events (PSTGRS_HIST_EVENTS_ALL)

                        --Created by Nathan Vanderwyst

                        --Created on 2013-10-28

                        SELECT

                           --historical_events

                        he.id as event_id

                        ,he.worker as event_worker

                        ,he.duration_in_ms as event_duration_millisec

                        ,he.is_failure as event_failure_indicator

                        ,he.details as event_details

                        ,he.created_at AT TIME ZONE 'UTC-6' as event_created_ts

                         

                        --hist_event_types

                        ,ht.name as event_name

                        ,ht.action_type as event_action_type

                         

                        --hist_users

                        ,hu.name as user_name

                        ,hu.domain_name as user_domain_name

                        ,hu.friendly_name as user_friendly_name

                         

                        --hist_sites (user)

                        ,hs.name as user_site_name

                        ,hs.url_namespace as user_url_namespace

                         

                        --hist_sites (target site)

                        ,hts.name as target_site_name

                        ,hts.url_namespace as target_url_namespace

                         

                        --hist_projects

                        ,hp.name as project_name

                         

                        --hist_workbooks

                        ,hw.name as workbook_name

                        ,hw.size/1000 as workbook_size

                        ,hw.revision as workbook_revision

                         

                        --hist_views

                        ,hv.name AS view_name

                        ,hv.repository_url AS view_repository_url

                         

                        --hist_datasource

                        ,hd.name as datasource_name

                        ,hd.size/1000 as datasource_size

                        ,hd.revision as datasource_revision

                         

                        --hist_groups

                        ,hg.name as group_name

                         

                        --hist_schedules

                        ,hc.name as schedule_name

                        ,hc.priority as schedule_priority

                         

                          --hist_tasks

                        ,hk.type as task_type

                        ,hk.priority as task_priority

                         

                        FROM

                                     public.historical_events he

                        INNER JOIN public.historical_event_types ht ON (he.historical_event_type_id=ht.type_id)

                        LEFT JOIN    (

                        SELECT

                        q01.id

                        ,upper(q01.name) as name

                        ,q01.domain_name as domain_name

                        ,q02.su_friendly_name as friendly_name

                        FROM

                        public.hist_users q01

                        LEFT JOIN

                                    (

                        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)

                        ) q02 ON (upper(q01.name)=upper(q02.su_name))

                        ) hu ON (he.hist_actor_user_id = hu.id)

                         

                        LEFT JOIN public.hist_sites hs ON (he.hist_actor_site_id = hs.id)

                        LEFT JOIN public.hist_sites hts ON (he.hist_target_site_id = hts.id)

                        LEFT JOIN public.hist_projects hp ON (he.hist_project_id = hp.id)

                        LEFT JOIN public.hist_workbooks hw ON (he.hist_workbook_id = hw.id)

                        LEFT JOIN public.hist_views hv ON (he.hist_view_id = hv.id)

                        LEFT JOIN public.hist_datasources hd ON (he.hist_datasource_id = hd.id)

                        LEFT JOIN public.hist_groups hg ON (he.hist_group_id = hg.id)

                        LEFT JOIN public.hist_schedules hc ON (he.hist_schedule_id = hc.id)

                          LEFT JOIN public.hist_tasks hk ON (he.hist_task_id = hk.id)

                        • 10. Re: Missing Postgres data
                          nolan.gaskill

                          Hi Matt,

                          Please see the copy of the SQL posted below.

                          • 11. Re: Missing Postgres data
                            Kristen Connolly

                            Hi Nolan,

                            We're having a similar problem, noticed only because a couple of very active users aren't showing up at all. I've tried various different queries (see below for an example), but haven't yet found the problem. The "missing" user is recognized as a current owner, but her activity history is entirely missing. It was true in TS8.1 and it's still true in 9.3. Hopefully one of us will figure out what we're doing wrong. Have you had any luck in the past month?

                            Thanks,

                            ::Kristen

                             

                            I used the query here: Determine How Often Workbooks are Used by Using Custom Admin Views | Tableau Software

                            • 12. Re: Missing Postgres data
                              nolan.gaskill

                              Unfortunately we have not learned anything more on this subject.  We uncovered the issue in a similar way. There is a consultant that uses the same suite of dashboards each week for his clients.  I'd expected his hits for each dashboard to be something around 40 per month.  When I look at the data though, I'm seeing only a few hits per month.

                              • 13. Re: Missing Postgres data
                                Kristen Connolly

                                I solved my own problem, though it probably won't help you, Nolan.

                                The query I got from Tableau's Knowledge base was slightly incorrect in one of the joins. Knowing which user was missing and that each user has a couple of different IDs, I looked at all the tables being joined, looked to see which ID number(s) showed up in each table, and determined that one of the joins was incorrect.

                                 

                                Where it said:

                                INNER JOIN "public"."hist_users" "hist_users" ON ("historical_events"."hist_actor_user_id" = "hist_users"."user_id")

                                It should actually have said:

                                INNER JOIN "public"."hist_users" "hist_users" ON ("historical_events"."hist_actor_user_id" = "hist_users"."id")

                                 

                                To clarify what I found for our "missing" user and the various IDs:

                                hist_users.system_user_id: 14

                                hist_users.user_id: 14

                                hist_users.id: 3

                                historical_events.hist_actor_user_id: 3 (no records with a 14)

                                _users.id: 14

                                _users.system_user_id: 14

                                users.id: 14

                                users.system_user_id: 14

                                users_view.id: 14

                                users_view.system_user_id: 14

                                 

                                Once I made that change, the user showed up fully. I'm bothered by the fact that "user id" is used differently in different places. I've also reported the knowledge base error to Tableau.

                                 

                                The only way this might translate to help you is that you might trace tediously back through to ensure that the right IDs are being used in each of your joins.

                                • 14. Re: Missing Postgres data
                                  Dan Scott

                                  Kristen,

                                  This is an unfortunate mistake in the data dictionary description, and it appears to be a problem for more than just hist_users.  In general, the id's in historical_events: hist_target_user_id, hist_actor_site_id, hist_target_site_id, hist_project_id, hist_workbook_id, hist_view_id, hist_datasource_id, hist_comment_id, hist_tag_id, hist_group_id, hist_licensing_role_id, hist_schedule_id, hist_task_id, hist_data_connection_id, hist_config_id, hist_capability_id are all meant to refer to the id column of the respective hist* tables they reference (historical_event_type_id is the one exception to that rule, I believe, as it references the type_id field of historical_event_types).

                                   

                                  Inside the other hist* tables it works differently

                                  In hist_workbooks for example, the field named workbook_id refers to the id of the workbooks table (assuming that id still exists, otherwise it will be NULL).

                                   

                                  In general, references inside historical_events refer to id values in the hist* tables, while references inside the hist* tables refer to id values of the main (i.e. non-hist*) table.

                                   

                                  I'll see that a bug gets filed on this right away.

                                  2 of 2 people found this helpful
                                  1 2 Previous Next