KNOWLEDGE BASE

Determine When Users Are Signing in to Tableau Server by Using Custom Admin Views


Published: 20 Mar 2017
Last Modified Date: 20 Mar 2017

Question

How to use the Workgroups Database to monitor user actions through custom admin views?

Tableau Server includes several administrative views designed to help administrators monitor activity on Tableau Server. You can use the build-in "readonly" user to create custom administrative views in Tableau Desktop by querying tables in the Tableau Server repository. This article walks through an example of how you might use custom administrative views to answer questions about group membership on your Tableau Server.

 

Environment

Tableau Server

Answer

Note: The "readonly" user has access to tables (included in this example) that are intended to support the Tableau application and may be changed or removed without warning. This can break views built using these tables.

To follow along with this example:

  1. Download the sample data source.
  2. Enable external access to the Tableau Server Postgres database (the repository). For details see Collect Data with the Tableau Server Repository in the Tableau Server Help.
  3. Open the sample data source in Tableau Desktop and connect to your Postgres database as the readonly user. For details, see Connecting to the Tableau Server Database in the Tableau Server Help.
  4. Create views using the tables selected in the data source.

This example uses four tables to answer questions about server access

 

  • historical_events, historical_event_types—These tables provide data about user interaction. historical_events contains user actions on Tableau Server and historical_event_types contains data about activity (accessing views, publishing workbooks, refreshing extracts).

  • hist_users—This table provides data related to user activity and allows you to differentiate between the target of an action and the user taking that action.

  • _users—The _users table contains user identification and names. Use this table instead of the users table to access “friendly” user names.


    Notes:

  • Created At (in historical_events) – The time defaults to UTC so you may need to adjust this using a calculated field.
  • Median Users (a calculated field) – This calculated field takes the median of total values of Friendly Name (in _users) in the view and is shown as an example. You can create any statistical operator.

  • The sample data source connects the tables in the following way:

  • The historical_events table is joined to the hist_users table using a left join on the Hist Actor User Id and Id (Hist Users) fields:

    How the tables are connected:

    The historical_event_types table is joined to the groups table using an inner join on the Historical Event Type Id and Type Id fields:


    The hist_users table is joined to the _users table using a left join on the User Id and Id (Users) fields:

  • The table joins generate the following custom SQL:

    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_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"  
    "_users"."id" AS "id (_users)"  
    "_users"."name" AS "name (_users)"  
    "_users"."login_at" AS "login_at"  
    "_users"."friendly_name" AS "friendly_name"  
    "_users"."licensing_role_id" AS "licensing_role_id"  
    "_users"."licensing_role_name" AS "licensing_role_name"  
    "_users"."domain_id" AS "domain_id"  
    "_users"."system_user_id" AS "system_user_id (_users)"  
    "_users"."domain_name" AS "domain_name (_users)"  
    "_users"."domain_short_name" AS "domain_short_name"  
    "_users"."site_id" AS "site_id"  
    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_users" "hist_users" ON ("historical_events"."hist_actor_user_id" = "hist_users"."id")  
    LEFT JOIN "public"."_users" "_users" ON ("hist_users"."user_id" = "_users"."id"))  

Did this article resolve the issue?