Skip to content

Instantly share code, notes, and snippets.

@AdamStormhardtGH

AdamStormhardtGH/get list of tableau users and their max site role

Created January 30, 2020 03:46
Show Gist options
  • Save AdamStormhardtGH/d1d0f37532fd47653f6b41ac7469dddc to your computer and use it in GitHub Desktop.
Save AdamStormhardtGH/d1d0f37532fd47653f6b41ac7469dddc to your computer and use it in GitHub Desktop.
Download ZIP
Raw
get list of tableau users and their max site role
------------------------------------------------------
-- get a list of users and their max site role
------------------------------------------------------
Select
su.name
, -- site role id should be max here
sr.id,
sr.display_name,
sr.licensing_rank,
case
when su.admin_level = 10 then
'Server Admin'
else null
end as admin_level,
login_at
from system_users su
inner join (
with siteranked as (
select system_user_id, site_role_id, licensing_rank
from users
inner join site_roles sr on sr.id = users.site_role_id
)
select
distinct
max(siteranked.licensing_rank) as licensing_rank,
siteranked.system_user_id
--site_role_id
from siteranked
group by system_user_id
)
users
on users.system_user_id = su.id
inner join workgroup.public.site_roles sr on users.licensing_rank = sr.licensing_rank
inner join (
select
distinct name,
first_value(login_at)
over (
PARTITION by name
order by login_at desc
) login_at
from public.users_view
--order by login_at desc
) logindata on logindata.name = su.name
--where su.name like '%<NAME>%'
-- where display_name like '%xplorer%'
group by su.name, system_user_id, sr.display_name, sr.id, admin_level, login_at --,
order by name asc
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment