KNOWLEDGE BASE

How-to-Create-a-Custom-Admin-View-that-Shows-All-Users-in-a-Given-Site


Published: 22 Jun 2015
Last Modified Date: 07 Mar 2017

Question

This article demonstrates how to build a view that shows all users given access to a site. 

Environment

  • Tableau Desktop + Server 9.0
  • Postgres
  • Custom Admin Views

Answer

 The data dictionary shows that users and sites can be joined on site ID. Here is an example of how you might join these tables: 

SELECT "_users"."id" AS "id", 
"_users"."name" AS "name", 
"_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"."domain_name" AS "domain_name", 
"_users"."domain_short_name" AS "domain_short_name", 
"_users"."site_id" AS "site_id", 
"group_users"."id" AS "id (group_users)", 
"group_users"."group_id" AS "group_id", 
"group_users"."user_id" AS "user_id", 
"groups"."id" AS "id (groups)", 
"groups"."name" AS "name (groups)", 
"groups"."created_at" AS "created_at", 
"groups"."updated_at" AS "updated_at", 
"groups"."system" AS "system", 
"groups"."owner_id" AS "owner_id", 
"groups"."domain_id" AS "domain_id (groups)", 
"groups"."site_id" AS "site_id (groups)", 
"groups"."luid" AS "luid", 
"sites"."name" AS "Site Name" 
FROM "public"."_users" "_users" 
LEFT JOIN "public"."group_users" "group_users" ON ("_users"."id" = "group_users"."user_id") 
LEFT JOIN "public"."groups" "groups" ON ("group_users"."group_id" = "groups"."id") 
LEFT JOIN "public"."sites" "sites" ON ("_users"."site_id" = "sites"."id") 
WHERE "sites"."name" = 'Default' 

After submitting this custom SQL, you can pull out “Site Name” onto the columns shelf and “Friendly Name” onto the “text” label on the marks card. This will give you a list of users on each site. 
Did this article resolve the issue?