6 Replies Latest reply on Sep 7, 2017 1:40 PM by Bob Gale

    Getting Views by Device Type from the Workgroup Database

    Jasson Giannini

      Hi All,

       

      I am trying to get views by device type for an internal monitoring dashboard on server.

       

      I was able to get views by downloading the Tabbed Admin Views from server itself. What I am trying to do is add on either the views_stats table or the datasouce_metrics_aggregations table to the historical events table to track device type. Thus far it joins but all device types are null, when I know they should not be

       

      My question is what fields join views_stats or datasouce_metrics_aggregations to historica_ events? Or is there another way to do this?

        • 1. Re: Getting Views by Device Type from the Workgroup Database
          Toby Erkson

          Try this:

          Converting the above to Custom SQL give this (so you can see the JOINs to use ):

          SELECT "historical_events"."id" AS "id",
            "historical_events"."historical_event_type_id" AS "historical_event_type_id",
            CAST("historical_events"."worker" AS TEXT) AS "worker",
            "historical_events"."duration_in_ms" AS "duration_in_ms",
            "historical_events"."is_failure" AS "is_failure",
            CAST("historical_events"."details" AS TEXT) AS "details",
            "historical_events"."created_at" AS "created_at",
            "historical_events"."hist_actor_user_id" AS "hist_actor_user_id",
            "historical_events"."hist_target_user_id" AS "hist_target_user_id",
            "historical_events"."hist_actor_site_id" AS "hist_actor_site_id",
            "historical_events"."hist_target_site_id" AS "hist_target_site_id",
            "historical_events"."hist_project_id" AS "hist_project_id",
            "historical_events"."hist_workbook_id" AS "hist_workbook_id",
            "historical_events"."hist_view_id" AS "hist_view_id",
            "historical_events"."hist_datasource_id" AS "hist_datasource_id",
            "historical_events"."hist_comment_id" AS "hist_comment_id",
            "historical_events"."hist_tag_id" AS "hist_tag_id",
            "historical_events"."hist_group_id" AS "hist_group_id",
            "historical_events"."hist_licensing_role_id" AS "hist_licensing_role_id",
            "historical_events"."hist_schedule_id" AS "hist_schedule_id",
            "historical_events"."hist_task_id" AS "hist_task_id",
            "historical_events"."hist_data_connection_id" AS "hist_data_connection_id",
            "historical_events"."hist_config_id" AS "hist_config_id",
            "historical_events"."hist_capability_id" AS "hist_capability_id",
            "views"."id" AS "id (views)",
            "views"."name" AS "name",
            CAST("views"."repository_url" AS TEXT) AS "repository_url",
            CAST("views"."description" AS TEXT) AS "description",
            "views"."created_at" AS "created_at (views)",
            "views"."locked" AS "locked",
            "views"."published" AS "published",
            "views"."read_count" AS "read_count",
            "views"."edit_count" AS "edit_count",
            "views"."datasource_id" AS "datasource_id",
            "views"."workbook_id" AS "workbook_id",
            "views"."index" AS "index",
            "views"."updated_at" AS "updated_at",
            "views"."owner_id" AS "owner_id",
            CAST("views"."fields" AS TEXT) AS "fields",
            CAST("views"."title" AS TEXT) AS "title",
            CAST("views"."caption" AS TEXT) AS "caption",
            "views"."sheet_id" AS "sheet_id",
            "views"."state" AS "state",
            "views"."sheettype" AS "sheettype",
            "views"."site_id" AS "site_id",
            "views"."repository_data_id" AS "repository_data_id",
            "views"."first_published_at" AS "first_published_at",
            "views"."revision" AS "revision",
            "views"."for_cache_updated_at" AS "for_cache_updated_at",
            "views"."luid" AS "luid",
            "views"."thumbnail_id" AS "thumbnail_id",
            "views_stats"."id" AS "id (views_stats)",
            "views_stats"."user_id" AS "user_id",
            "views_stats"."view_id" AS "view_id",
            "views_stats"."nviews" AS "nviews",
            "views_stats"."time" AS "time",
            "views_stats"."site_id" AS "site_id (views_stats)",
            CAST("views_stats"."device_type" AS TEXT) AS "device_type"
          FROM "public"."historical_events" "historical_events"
            INNER JOIN "public"."views" "views" ON ("historical_events"."hist_view_id" = "views"."id")
            INNER JOIN "public"."views_stats" "views_stats" ON ("views"."id" = "views_stats"."view_id")
          
          • 2. Re: Getting Views by Device Type from the Workgroup Database
            Jasson Giannini

            Hi Toby,

             

            No such luck thus far. These are my joins

             

            joins.PNG

             

            my results:views by device.PNG

            and my code to show joins:

             

            SELECT "historical_events"."id" AS "id",

              "historical_events"."historical_event_type_id" AS "historical_event_type_id",

              "historical_events"."worker" AS "worker",

              "historical_events"."duration_in_ms" AS "duration_in_ms",

              "historical_events"."is_failure" AS "is_failure",

              "historical_events"."details" AS "details",

              "historical_events"."created_at" AS "created_at",

              "historical_events"."hist_actor_user_id" AS "hist_actor_user_id",

              "historical_events"."hist_target_user_id" AS "hist_target_user_id",

              "historical_events"."hist_actor_site_id" AS "hist_actor_site_id",

              "historical_events"."hist_target_site_id" AS "hist_target_site_id",

              "historical_events"."hist_project_id" AS "hist_project_id",

              "historical_events"."hist_workbook_id" AS "hist_workbook_id",

              "historical_events"."hist_view_id" AS "hist_view_id",

              "historical_events"."hist_datasource_id" AS "hist_datasource_id",

              "historical_events"."hist_comment_id" AS "hist_comment_id",

              "historical_events"."hist_tag_id" AS "hist_tag_id",

              "historical_events"."hist_group_id" AS "hist_group_id",

              "historical_events"."hist_licensing_role_id" AS "hist_licensing_role_id",

              "historical_events"."hist_schedule_id" AS "hist_schedule_id",

              "historical_events"."hist_task_id" AS "hist_task_id",

              "historical_events"."hist_data_connection_id" AS "hist_data_connection_id",

              "historical_events"."hist_config_id" AS "hist_config_id",

              "historical_events"."hist_capability_id" AS "hist_capability_id",

              "historical_event_types"."type_id" AS "type_id",

              "historical_event_types"."name" AS "name",

              "historical_event_types"."action_type" AS "action_type",

              "hist_workbooks"."id" AS "id (hist_workbooks)",

              "hist_workbooks"."workbook_id" AS "workbook_id",

              "hist_workbooks"."name" AS "name (hist_workbooks)",

              "hist_workbooks"."repository_url" AS "repository_url",

              "hist_workbooks"."size" AS "size",

              "hist_workbooks"."revision" AS "revision",

              "hist_users"."id" AS "id (hist_users)",

              "hist_users"."user_id" AS "user_id",

              "hist_users"."name" AS "name (hist_users)",

              "hist_users"."domain_name" AS "domain_name",

              "hist_users"."email" AS "email",

              "hist_users"."system_user_id" AS "system_user_id",

              "hist_users"."system_admin_level" AS "system_admin_level",

              "hist_users"."hist_licensing_role_id" AS "hist_licensing_role_id (hist_users)",

              "hist_users"."site_admin_level" AS "site_admin_level",

              "hist_users"."publisher_tristate" AS "publisher_tristate",

              "hist_views"."id" AS "id (hist_views)",

              "hist_views"."view_id" AS "view_id",

              "hist_views"."name" AS "name (hist_views)",

              "hist_views"."repository_url" AS "repository_url (hist_views)",

              "hist_views"."revision" AS "revision (hist_views)",

              "view_metrics_aggregations"."id" AS "id (view_metrics_aggregations)",

              "view_metrics_aggregations"."view_id" AS "view_id (view_metrics_aggregations)",

              "view_metrics_aggregations"."day_index" AS "day_index",

              "view_metrics_aggregations"."month_index" AS "month_index",

              "view_metrics_aggregations"."year_index" AS "year_index",

              "view_metrics_aggregations"."view_count" AS "view_count",

              "view_metrics_aggregations"."device_type" AS "device_type",

              "views"."id" AS "id (views)",

              "views"."name" AS "name (views)",

              "views"."repository_url" AS "repository_url (views)",

              "views"."description" AS "description",

              "views"."created_at" AS "created_at (views)",

              "views"."locked" AS "locked",

              "views"."published" AS "published",

              "views"."read_count" AS "read_count",

              "views"."edit_count" AS "edit_count",

              "views"."datasource_id" AS "datasource_id",

              "views"."workbook_id" AS "workbook_id (views)",

              "views"."index" AS "index",

              "views"."updated_at" AS "updated_at",

              "views"."owner_id" AS "owner_id",

              "views"."fields" AS "fields",

              "views"."title" AS "title",

              "views"."caption" AS "caption",

              "views"."sheet_id" AS "sheet_id",

              "views"."state" AS "state",

              "views"."sheettype" AS "sheettype",

              "views"."site_id" AS "site_id",

              "views"."repository_data_id" AS "repository_data_id",

              "views"."first_published_at" AS "first_published_at",

              "views"."revision" AS "revision (views)",

              "views"."for_cache_updated_at" AS "for_cache_updated_at",

              "views"."luid" AS "luid",

              "views"."thumbnail_id" AS "thumbnail_id",

              "views_stats"."id" AS "id (views_stats)",

              "views_stats"."user_id" AS "user_id (views_stats)",

              "views_stats"."view_id" AS "view_id (views_stats)",

              "views_stats"."nviews" AS "nviews",

              "views_stats"."time" AS "time",

              "views_stats"."site_id" AS "site_id (views_stats)",

              "views_stats"."device_type" AS "device_type (views_stats)"

            FROM "public"."historical_events" "historical_events"

              INNER JOIN "public"."historical_event_types" "historical_event_types" ON ("historical_events"."historical_event_type_id" = "historical_event_types"."type_id")

              LEFT JOIN "public"."hist_workbooks" "hist_workbooks" ON ("historical_events"."hist_workbook_id" = "hist_workbooks"."id")

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

              LEFT JOIN "public"."hist_views" "hist_views" ON ("historical_events"."hist_view_id" = "hist_views"."id")

              LEFT JOIN "public"."view_metrics_aggregations" "view_metrics_aggregations" ON ("historical_events"."hist_view_id" = "view_metrics_aggregations"."view_id")

              LEFT JOIN "public"."views" "views" ON ("historical_events"."hist_view_id" = "views"."id")

              LEFT JOIN "public"."views_stats" "views_stats" ON ("views"."id" = "views_stats"."view_id")

            • 3. Re: Getting Views by Device Type from the Workgroup Database
              Toby Erkson

              I created a workbook, took your Custom SQL and copy/pasted verbatim as my data source, and got this:

              So it appears to be working for me   Check your filtering.  How long has your Tableau Server been up and running?  Are you sure it's been hit by various devices?

              • 4. Re: Getting Views by Device Type from the Workgroup Database
                Toby Erkson

                Since you are using VIEW_METRICS_AGGREGATIONS I don't think you need to also use VIEWS and VIEWS_STATS.  Use one or the other-- I think I'd recommend using the former.

                • 5. Re: Getting Views by Device Type from the Workgroup Database
                  Jasson Giannini

                  There are no filters. What I did notice is historical_events.hist_view_id has no matching values with views.id. Any idea why this may have happened?

                  • 6. Re: Getting Views by Device Type from the Workgroup Database
                    Bob Gale

                    I have also been trying to use device_type in the view_stats table but am finding that it is inaccurate. Or maybe "misleading" is more appropriate. It appears not to be actual device but Tableau's guess based on browser window size as described here: Create Dashboard Device Layouts

                     

                    If the smallest web browser dimension is...This device layout is loaded...
                    Less than or equal to 500 pixelsPhone
                    Between 501 and 800 pixelsTablet
                    Greater than 800 pixelsDesktop

                     

                    I confirmed this by querying the table for my user_id while I browsed from my Windows 10 machine. When my browser is not maximized, it thinks I'm a tablet. When I maximize, it thinks I'm a desktop.

                     

                    I'm looking into parsing http_user_agent in http_requests to get a more accurate breakdown.

                    2 of 2 people found this helpful