Tableau Server – SQL Queries

March 1, 2019

How many users are there (not including system accounts)?

Exclude built-in _system, Tableau Guest and Tableau Software users with id’s (1,3,5) respectively.

SELECT DISTINCT 
    users.id,
    su.name
FROM 
    public.users
INNER JOIN 
    public.system_users su ON su.id = users.system_user_id
WHERE 
    su.id NOT IN (1,3,5)
    AND 
    users.id < 500
ORDER BY 
    users.id
;

Create an in-memory table.

Use an ARRAY variable and built-in perm_users_projects_capabilities function to create a proj_perms in-memory table. A “Query returned successfully in xx secs xxx msec.” response means the table was successfully stored in memory.

DO $$
DECLARE user_ids INTEGER ARRAY;
BEGIN
user_ids := ARRAY((SELECT users.id FROM users WHERE users.id NOT IN (1,3,5) AND users.id < 500));

DROP TABLE IF EXISTS proj_perms;

CREATE TEMP TABLE proj_perms AS
	SELECT 
		'project'::text object_type,
		*
	FROM 
		perm_users_projects_capabilities
		(
			,user_ids
			,(SELECT ARRAY(SELECT id FROM projects WHERE site_id = 1))
			,ARRAY[1]
		);
END
$$;

Which Projects are missing direct user/group permission assignments?

------------------------------------------------------------
-- Projects missing direct user/group permission assignments
------------------------------------------------------------
SELECT DISTINCT
	ngp.authorizable_id
	,p.name
FROM 
	public.next_gen_permissions ngp
JOIN
	public.projects p ON p.id = ngp.authorizable_id
WHERE
	p.controlled_permissions_enabled = FALSE
ORDER BY
	p.name
;

Which projects don’t have workbooks?

SELECT DISTINCT
	wb.id
	,wb.name
	,p.id
	,p.name
FROM
	public.workbooks wb
FULL OUTER JOIN
	public.projects p ON p.id = wb.project_id
WHERE
	wb.id IS NULL
ORDER BY
	wb.id
;

Assets with conflicting ID’s

------------------------------------------------------------
-- Find assets colliding with Workbook ID's
------------------------------------------------------------
SELECT DISTINCT
	wb.id
	,ngp.authorizable_id
	,ngp.authorizable_type
FROM 
	public.workbooks wb 
LEFT OUTER JOIN
	public.next_gen_permissions ngp ON ngp.authorizable_id = wb.id
WHERE
	ngp.authorizable_type NOT LIKE 'Workbook'
ORDER BY
	ngp.authorizable_id
;

Leave a Reply

Your email address will not be published. Required fields are marked *