
How to update / create users coming from Active Directory in Tableau?đ
I have been asked this question many times in last few months in many forums, how to automatically inactive the users in Tableau server if they are getting inactive in Active Directory or how to create Users in Tableau Server in case any users are newly on boarded in AD users groups available in Tableau, hence writing this solution here
I will not write full code here, it will give you fair enough idea how to achieve the other things
Requirements
- Active Directory details
- PowerShell
- Obdc Driver to reach to Tableau Postgres data base
- Make sure Tableau database should be enabled
- Administrative privileges on the Tableau server
Script:
Clear
âGet users from ad server
Get-ADUser -Filter * -Properties Name, SamAccountName, EmailAddress, UserPrincipalName, Enabled, Company | Select-Object Name, SamAccountName,UserPrincipalName, EmailAddress, Enabled, Company | where {$_.company -eq "ABHICL"} | Export-Csv "E:\Sandip_SQL\ADUsers_group.csv"
âexport all the users from Tableau Server
âimporting my tableau server postgres details kept in csv file
$PostgreSQL_Connection_String = import-csv -path "E:\Sandip_SQL\PostgreSQL_Connection_String.csv"
foreach ($Row in $PostgreSQL_Connection_String)
{
$connectionString = "Driver=$($Row.Driver);Server=$($Row.Server);Port=$($Row.Port);Database=$($Row.Database);Uid=$($Row.User);Pwd=$($Row.Password);"
}
$connection = New-Object System.Data.Odbc.OdbcConnection
$connection.ConnectionString = $connectionString
$connection.Open()
$query = "SELECT name AS UserID,friendly_name AS UserName,licensing_role_name AS Role FROM public._users where name not in ('_system','guest')â
$command = $connection.CreateCommand()
$command.CommandText = $query
$command.CommandTimeout = 0
$result = $command.ExecuteReader()
$table = new-object âSystem.Data.DataTableâ
$table.Load($result)
$table |Export-Csv -path "E:\Sandip_SQL\tableauusers.csv" âROWS_INSERTED
$connection.Close()
âimporting both files ad users and Tableau users
$adusers = import-csv -path "E:\Sandip_SQL\ADUsers_group.csv"
$tabusers = import-csv -path "E:\Sandip_SQL\tableauusers.csv"
#I will first check the records only if exist in both csv and then will mark them unlicensed in Tableau server if their status is not active in AD and License type is still allocated to user
âmatching counter and monitoring of time taken by the script
$matchcounter
$start = [system.datetime]::Now
# create new CSV file
foreach ($order1 in $adusers){
$matched = $false
foreach ($order2 in $tabusers){
$obj = "" | select "SamAccountName","Ad_Status","Tableau_Role"
if(($order1.SamAccountName ) -eq $order2.userid ){
$matchCounter++
$matched = $true
$obj.SamAccountName = $order1.SamAccountName
$obj.Ad_Status = $order1.Enabled
$obj.Tableau_Role = $order2.role
Write-Host "Match Found Orders " "$matchCounter"
$obj | Export-Csv -Path E:\Sandip_SQL\AD_Tableau_Users_Match.csv -Append -NoTypeInformation
}
}
}
$end = [system.datetime]::Now
$resultTime = $end - $start
Write-Host "Execution took : $($resultTime.TotalSeconds) seconds."
$updateuser = import-csv -path "E:\Sandip_SQL\AD_Tableau_Users_Match.csv"
# Setup to connect
$server = "your tableau server url"
$s = Invoke-RestMethod -Uri $server/api/3.7/serverinfo -Method get âworks on server version 10.1 and later
$api = $s.tsResponse.serverInfo.restApiVersion #2020.1 server
echo $api
âmake sure you should use either site admin or administrator account only to perform below activity
$username = âyour tableau userâ
$password = âyour tableau passwordâ
$sitelogin = "" âkeep site as blank in case of default site
# generate body for sign in
$signin_body = (â<tsRequest>
<credentials name=ââ + $username + ââ password=ââ+ $password + ââ >
<site contentUrl="'+$sitelogin +'" />
</credentials>
</tsRequest>â)
$response = Invoke-RestMethod -Uri $server/api/$api/auth/signin -Body $signin_body -Method post
# save the auth token, site id and my user id
$authToken = $response.tsResponse.credentials.token
$siteID = $response.tsResponse.credentials.site.id
$myUserID = $response.tsResponse.credentials.user.id
$siteURL = $response.tsResponse.credentials.site.contentUrl
echo $siteID
# set up header fields with auth token
$headers = New-Object âSystem.Collections.Generic.Dictionary[[String],[String]]â
# add X-Tableau-Auth header with our auth token
$headers.Add(âX-Tableau-Authâ, $authToken)
âtests whether logged in user is an Administrator (site or server)
$loginUserid = Invoke-RestMethod -Uri $server/api/$api/sites/$siteID/users/$myUserID -Headers $headers -Method Get
$admin = $loginUserid.tsResponse.user.siteRole -like "*Administrator"
if($admin)
{
foreach ($line in $updateuser )
{
if($line.Ad_Status -eq "FALSE" -and $line.Tableau_Role -ne "Unlicensed")
{
$userid = $line.SamAccountName
$SiteRole = "Unlicensed"
âuser update body
$userupdate = ( '<tsRequest> <user siteRole="'+$SiteRole+'" /> </tsRequest>' )
$response = Invoke-RestMethod -Uri $server/api/$api/sites/$siteID/users/$userid -Headers $headers -Method Put -Body $userupdate
write-host $response.tsResponse.user
}
}
}
@Ciara Brennanâ @David Browneâ @Veronica Simoesâ