-
1. Re: Like to query users who has a Site role Publisher or Interactor or Viewer from the Postgres db
Donna ColesMay 19, 2017 12:49 AM (in response to Jayesh Patel)
Hi Jayesh
I think the users table is the place you want to look. The licensing_role_id field joins to licensing_roles which contains these values
"Unlicensed"
"Guest"
"Viewer"
"Interactor"
"Support"
The users table also has a
- site_id indicating what site the users belong to
- publisher_trisate field which is 0,1,2 and is descibed in the system as "publisher_tristate integer NOT NULL DEFAULT 0, -- From among 0 (not), 1 (implicit) and 2 (explicit)"
- admin_level which I think will indicate if they're site admin (we only have 1 site so this isn't relevant for us)
You'll also need the system_users table - this contains the names of users and the indicator of whether they're server admin or not.
Try this query
select su.name, su.admin_level, u.site_id, u.admin_level, u.publisher_tristate, l.name
from system_users su
inner join users u
on u.system_user_id = su.id
inner join licensing_roles l
on u.licensing_role_id = l.id
We only have 1 site and everyone is granted publisher role on the site, so they have the right to publish at the site level if they are then granted publisher on a specific project. When I run the query above, all my users have publisher_tristate = 2 and licensing_role = interactor. Server admins then have the admin_level from system_users set to 10.
Hope that helps
Donna
-
2. Re: Like to query users who has a Site role Publisher or Interactor or Viewer from the Postgres db
Jayesh Patel May 19, 2017 9:49 AM (in response to Donna Coles)Hi Donna,
That is not helping as I’m looking for Publisher role. We import groups/members from the AD and assigned Site role Publisher or Interactor (Viewer) to groups. I was able to figure out but the result is not the same as we see in Tableau browser when click Users and filter by Site role Publisher. Here is the query and web browser snippet.
I can get this information from the System_users, Group_users and Group with minimum site role filter but result is not the same as from the Tableau browser calculation (I’m getting about 200 publishers as oppose to web result is 475.
SELECT distinct "system_users"."id" AS "id",
"system_users"."name" AS "name",
"system_users"."email" AS "email",
"system_users"."friendly_name" AS "friendly_name",
"system_users"."state" AS "state",
"group_users"."id" AS "id (group_users)",
"groups"."name" AS "name (groups)",
"groups"."site_id" AS "site_id",
"groups"."minimum_site_role" AS "minimum_site_role"
FROM "public"."system_users" "system_users"
INNER JOIN "public"."group_users" "group_users" ON ("system_users"."id" = "group_users"."user_id")
LEFT JOIN "public"."groups" "groups" ON ("group_users"."group_id" = "groups"."id")
Thank you,
Jayesh Patel
-
image001.png 52.6 KB
-
-
3. Re: Like to query users who has a Site role Publisher or Interactor or Viewer from the Postgres db
Luke Brady Jun 2, 2017 3:26 PM (in response to Jayesh Patel)Take a look at the joins I've compiled. It might help you with your query.
-
4. Re: Like to query users who has a Site role Publisher or Interactor or Viewer from the Postgres db
Atul Bhagwat Jul 26, 2017 6:34 AM (in response to Jayesh Patel)Hi Jayesh,
We are looking for same info. Any luck with your results?
Regards,
AB
-
5. Re: Like to query users who has a Site role Publisher or Interactor or Viewer from the Postgres db
Andrew Macey Jul 26, 2017 3:39 PM (in response to Atul Bhagwat)I haven't tried Postgres query for this particular info, but REST API works fine to list users in a site + their role. Tested in 10.0. It's a bit messy if you have large number of users because you need to paginate. See examples posted in developer corner or I can post Powershell example.
-
6. Re: Like to query users who has a Site role Publisher or Interactor or Viewer from the Postgres db
Atul Bhagwat Jul 26, 2017 3:47 PM (in response to Andrew Macey)That would be a good start.. please do post
-
7. Re: Like to query users who has a Site role Publisher or Interactor or Viewer from the Postgres db
Andrew Macey Jul 26, 2017 4:56 PM (in response to Atul Bhagwat)2 of 2 people found this helpful#PowerShell code
$tabserver = 'https://my-tab-server.myco.com'
$usr = Read-Host -Prompt 'Tableau Server ID (must be a System-Admin user)'
$pass = Read-Host -Prompt 'Password'
$siteshortname = '' # blank for default site
$loginurl = '/api/2.3/auth/signin'
[xml]$xml_loginpayload = '<tsRequest><credentials name="'+$usr+'" password="'+$pass+'" ><site contentUrl="'+$siteshortname+'" /></credentials></tsRequest>'
# login once to list sites
[xml]$x = invoke-WebRequest -Uri $tabserver/$loginurl -Method POST -ContentType "text/xml" -Body $xml_loginpayload
$token = $x.tsResponse.credentials.token
$siteid = $x.tsResponse.credentials.site.id
"# Getting sites..."
$sitelisturl = '/api/2.3/sites?pageSize=1000&pageNumber=1'
[xml]$sitelistresp = invoke-WebRequest -Uri $tabserver/$sitelisturl -Method GET -ContentType "text/xml" -Headers @{"X-Tableau-Auth"=$token}
ForEach ($row in $sitelistresp.tsResponse.sites.ChildNodes) {
$siteshortname = $row.contentUrl
[xml]$xml_loginpayload = '<tsRequest><credentials name="'+$usr+'" password="'+$pass+'" ><site contentUrl="'+$siteshortname+'" /></credentials></tsRequest>'
# login to site to list users
[xml]$x = invoke-WebRequest -Uri $tabserver/$loginurl -Method POST -ContentType "text/xml" -Body $xml_loginpayload
$token = $x.tsResponse.credentials.token
$siteid = $x.tsResponse.credentials.site.id
#$ListUsersUrl = "/api/2.3/sites/$siteid/users?filter=siteRole:eq:SiteAdministrator&pageSize=1000&pageNumber=1&sort=name:asc"
$ListUsersUrl = "/api/2.3/sites/$siteid/users?pageSize=1000&pageNumber=1&sort=name:asc"
[xml]$SiteUsers = invoke-WebRequest -Uri $tabserver/$ListUsersUrl -Method GET -ContentType "text/xml" -Headers @{"X-Tableau-Auth"=$token}
$TotCnt=$SiteUsers.tsResponse.pagination.totalAvailable
[array]$SiteUserLst = $SiteUsers.tsResponse.users.user
if ($TotCnt -gt $SiteUserLst.length) {
Write-Host "### WARNING: You need to pagenate! [$TotCnt > $($SiteUserLst.length)] in site [$siteshortname]" -foregroundColor Red
}
"--- Site [$siteshortname] users [$($SiteUserLst.length)]:"
#$SiteUserLst | select siteRole, name | ft
$SiteUserLst | select name, siteRole | ConvertTo-CSV
}
# Signout
$signouturl = '/api/2.3/auth/signout'
[xml]$x = invoke-WebRequest -Uri $tabserver/$signouturl -Method POST -ContentType "text/xml" -Headers @{"X-Tableau-Auth"=$token}
-
8. Re: Like to query users who has a Site role Publisher or Interactor or Viewer from the Postgres db
Luke Brady Jul 27, 2017 10:58 AM (in response to Atul Bhagwat)1 of 1 people found this helpfulAtul Bhagwat I took Andrew Macey 's code and tweaked it, made it more dynamic:
It prompts for the needed details now and also pages automatically. If this is helpful please mark it as such.
-
9. Re: Like to query users who has a Site role Publisher or Interactor or Viewer from the Postgres db
Atul Bhagwat Jul 27, 2017 12:51 PM (in response to Luke Brady)Hi Experts,
Amazing script.. It helps..
However, i see some disparity between the actual numbers and script results.
We have 3 sites. So i did try all.. Enter for default, as well as other sites.
It gives the same result for all..
Regards,
Atul B
-
10. Re: Like to query users who has a Site role Publisher or Interactor or Viewer from the Postgres db
Andrew Macey Jul 27, 2017 1:55 PM (in response to Atul Bhagwat)Depending on your version maybe you hit "duplicate" bug. Tableau Server 10.0.8 | Tableau Software "609115 - The Get Users in Group REST API call would sometimes return duplicate results." In my case bug was intermittent, so I just re-ran script if I detected wrong number of users returned.
To try to eliminate duplicates, you can add each resultset to an All-Users array as you loop through the pagination, then eliminate duplicates like:
#initialize type
[array]$AllUsersLst = $null
...
#inside loop
# only add the page if >0 users returned
if ($SiteUserLst.Length -ge 0) {
$AllUsersLst += $SiteUserLst
}
#eliminate duplicates after loop
$AllUsersLst = $AllUsersLst | sort name -unique
Now compare $AllUsersLst.length to the original $SiteUsers.tsResponse.pagination.totalAvailable (they should match).
-
11. Re: Like to query users who has a Site role Publisher or Interactor or Viewer from the Postgres db
Luke Brady Jul 27, 2017 2:15 PM (in response to Andrew Macey)Hey Andrew Macey do you have any idea why it's always hitting the same site though?
$ListUsersUrl = "/api/2.3/sites/$siteid/users?pageSize=1000&pageNumber=1&sort=name:asc"
I have validated for my two sites the $siteid value is different based on the site yet it still will only reference the default site.
-
12. Re: Like to query users who has a Site role Publisher or Interactor or Viewer from the Postgres db
Luke Brady Jul 27, 2017 2:58 PM (in response to Luke Brady)I found that someone else had this same issue, where you can't pull users for another Site:
Search for "Daniel Seisun" on this page. It is two years old though.
https://www.theinformationlab.co.uk/2014/09/10/getting-tableau-server-rest-api/
-
13. Re: Like to query users who has a Site role Publisher or Interactor or Viewer from the Postgres db
Andrew Macey Jul 27, 2017 4:12 PM (in response to Luke Brady)I'd try including Logout in the loop (immediately before the site level Login). Mine definitely works for all sites in 10.0 using 2.3 api. For higher version servers, I'd use later api version.
-
14. Re: Like to query users who has a Site role Publisher or Interactor or Viewer from the Postgres db
Jayesh Patel Jul 28, 2017 6:49 AM (in response to Andrew Macey)Works and match the numbers from the web counts.