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

1. Active Directory details
2. PowerShell
3. Obdc Driver to reach to Tableau Postgres data base
4. Make sure Tableau database should be enabled
5. 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​