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

Take a look at the joins I've compiled.  It might help you with your query.

Hi Jayesh,

We are looking for same info. Any luck with your results?

Regards,

AB

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.

That would be a good start.. please do post

#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}

Atul Bhagwat I took Andrew Macey 's code and tweaked it, made it more dynamic:

Tableau-Server-Postgres-Queries/get_Tableau_Publishers.ps1 at master · isajediknight/Tableau-Server-Postgres-Queries · G…

It prompts for the needed details now and also pages automatically.  If this is helpful please mark it as such.

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

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).

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.

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/

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.