7 Replies Latest reply on May 17, 2018 11:04 AM by Justin Pulley

    Tableau Server admin AD User automation

    Justin Pulley

      I don't know what category this fits in very well, but I picked one.

       

      Here is my situation:

       

      1. Active Directory SSO is enabled and required by my org

      2. The REST API is disabled as required by my org's security team (don't ask)

      3. The AD Sync is not a sync, but an add only, so I have 100's of unlicensed users (30,327 total users)

      4. Manually removing the unlicensed users via the GUI is ridiculous because I can't select all and delete

       

       

      I am considering attempting to enable read / write account for the users table within tableau and running a powershell script to query and delete any unlicensed users.  I don't know if enabling read write on a particular table in the DB is possible and searching that hasn't given me any specific information on it.

        • 1. Re: Tableau Server admin AD User automation
          Justin Pulley

          Specifically there are 2662 unlicensed users, so clicking one at a time through that list would be ridiculous.

          • 2. Re: Tableau Server admin AD User automation
            Andrew Bickert

            Hi Justin,

             

            This might gain more attention in the Server Administration area.

             

            I do not believe there is a 'supported' way to have write access to the PostGres Repository on Tableau Server. You are able to have read access though, build a query against it, and then write a tabcmd command to remove the users.

             

            *There is a yml file with the superuser tblwgadmin account which is explained here but it is not supported: Accessing tblwgadmin user in Postgres database for Tableau 9

             

             

            You could export the list though from Tableau PostGres to a csv and use the 'tabcmd deleteusers "user.csv"  ' which is referenced here: tabcmd Commands

            One other note, you will not be able to remove users that currently are listed as owners so you would need to get a list of users that are attached to content on the server. You can also get this information from the PostGres repository though. If you run the deleteusers command and a user still has content it will leave them as 'unlicensed'

             

            If you haven't enabled access to the PostGres Repository, here is a link:Collect Data with the Tableau Server Repository

             

            Andrew

            • 3. Re: Tableau Server admin AD User automation
              Justin Pulley

              yea I suppose that I will have to export to csv and then loop through the csv of users to then do a tabcmd remove user looping through the csv.

              • 4. Re: Tableau Server admin AD User automation
                Andrew Bickert

                Hi Justin,

                 

                If you would like I can see if I can write a script/program to automate this more for you. Do you want all 'unlicensed' users to be removed? Since the Rest API has been disabled we would likely need a different workaround unless you can run it on the server side for your security.

                 

                Andrew

                • 5. Re: Tableau Server admin AD User automation
                  Justin Pulley

                  I actually did it;  These are both powershell and you could likely combine them into one, but I didn't.  Running on Windows Server 2016 Base.

                   

                  $pf = "\\domain\dept\dcgsi\Extracts\Tableau_Unlicensed_Users_Original.csv"

                  $sf = "\\domain\dept\dcgsi\Extracts\Tableau_Unlicensed_Users.csv"

                  $if = Import-Csv $sf

                   

                  function CleanOut-Old-Files-Start{

                  if (Test-Path $pf){

                      Remove-Item $pf

                      }

                  }

                   

                  function CleanOut-Old-Files-Final{

                  if (Test-Path $sf){

                      Remove-Item $sf

                      }

                  }

                   

                  $query = @"

                          SELECT DISTINCT Name

                          FROM _users

                          WHERE licensing_role_name = 'Unlicensed'

                  "@

                   

                  function Get-ODBC-Data{

                     $conn = New-Object System.Data.Odbc.OdbcConnection

                     $conn.ConnectionString = "DSN=PostgreSQL30;"

                     $conn.open()

                     $cmd = New-object System.Data.Odbc.OdbcCommand($query,$conn)

                     $ds = New-Object system.Data.DataSet

                     (New-Object system.Data.odbc.odbcDataAdapter($cmd)).fill($ds) | out-null

                     $conn.close()

                     $ds.Tables[0] | Export-Csv $pf

                  }

                   

                  function Clean-CSV{

                  Get-Content $pf | Select -Skip 2 | Set-Content $sf

                  }

                   

                  CleanOut-Old-Files-Final

                  Get-ODBC-Data

                  Clean-CSV

                  CleanOut-Old-Files-Start

                   

                  That gets the unlicensed users and dumps them to a file on a network location accessible by the server host; you'll notice that I have to remove the first two lines.  The first line is Type info line and the second is the column header.

                   

                  $users = "\\domain\dept\dcgsi\Extracts\Tableau_Unlicensed_Users.csv"

                  $server = "https://TableauServer"

                   

                  function Clear-Unlicensed-Users{

                      & "D:\Tableau Server\2018.1\bin\tabcmd.exe" deleteusers $users --server $server -u <Username> -p <Password> --no-certcheck

                  }

                   

                  Clear-Unlicensed-Users

                   

                  That clears the users in that file from the server.

                   

                  Of course when you do upgrade the server you'll have to do some maintenance on the scripts to update the tabcmd location.

                  • 6. Re: Tableau Server admin AD User automation
                    Andrew Bickert

                    Nice!

                     

                    If you want to scrip the update to the TabCMD to go to the latest version of Tableau Installed you can use something like this:

                     

                    Andrew

                     

                    #Listed as 10.1 but can be any number.
                    $tableauDirectory = 'C:\Program Files\Tableau\Tableau Server\10.1\bin'      

                    $pathParts = $tableauDirectory.Split([system.io.path]::DirectorySeparatorChar);

                            foreach($dirName in $pathParts)
                            {
                                #check if directory is numeric
                                if($dirName -match '^-?(\.\d+|\d+(\.\d*)?)$')
                                {
                                    #version number directory found, check for latest version
                                    if($PSVersionTable.PSVersion.Major -lt 3)
                                    {
                                        $versionDirs = Get-ChildItem $joinedPath | Where-Object {$_.PSIsContainer} | Sort-Object -Property {$_.Name -as [double]} -Descending;
                                    }
                                    else
                                    {
                                        $versionDirs = Get-ChildItem $joinedPath -Directory | Sort-Object -Property {$_.Name -as [double]} -Descending;
                                    }
                                    $latestVersionDir = $versionDirs[0];

                                    #replace the version number directory with the newest version
                                    $tableauDirectory = $tableauDirectory.Replace($dirName,$latestVersionDir);

                                    #stop the loop
                                    break;
                                }
                                else
                                {
                                    #create the path to this point
                                    $joinedPath = [System.IO.Path]::Combine(($joinedPath + [system.io.path]::DirectorySeparatorChar), $dirName);
                                }
                            }
                       
                        return $tableauDirectory;

                    • 7. Re: Tableau Server admin AD User automation
                      Justin Pulley

                      That script still has the problem of the directory hard coded.

                       

                      Long term I think I can use something like

                       

                      $path = "D:\Tableau Server\"

                      $tableau_version = Get-ChildItem -Path $path -Recurse -Match "^[0-9].^[0-9]"

                       

                      embedded in the previous code.  I mean as long as tableau keeps with their historical naming conventions anyway.