Tableau Active Directory Assistant

Version 24

    Updates

    2Jan2014 - Version 1 - Attached documentation update for initial setup clarity; workbook similarly updated

    7Jan2014 - Version 2 - Workbook and interface updated to compare AD groups to Tableau Groups and remove Users from the Tableau Group if the person does not exist in the AD group; documentation updated.

    15Jan2014 - DAAP is necessary for the Tableau Desktop workbook to function so documentation updated.  NOTE: Version 3.2 doesn't require DAAP if using the Light version.

    16Jan2014 - Version 2.1 - User form refreshes Where? list boxes upon activation & double-clicking user form; hide user form when major processes are executing (e.g. Remove Missing Users From Groups) so it's more clear something is going on (some people don't look at the status bar at the bottom of Excel).

    16July2014 - Version 3.0 - Bigger changes and now what I personally use for adding/editing users on my server. New Groups can be custom added; custom user entry for adding users and changing users (my favorite use); users can have their rights & licensing changed using custom user entry; batch save location can be changed.

    17July2014 - Updated documentation to include the "Change" tab. Kind of useful to know about. Added control tip text to the Change button; expanded upon a couple comments in the code.

    25July2014 - Version 3.1 - Added a Groups list box on the Delete tab.  Added two more functions on the Delete tab.  Groups list box is now multi-select for all tabs.

    28July2014 - Version 3.2 - Option added to switch to "Light version" (toggles between "Full version" and "Light version"). Added Group_Only.twb file to support this. Allows admins that don't have the full control of their server (where DAAP isn't an option) to perform functions that don't require the Before and After sheets by disabling certain buttons and functions.

    29July2014 - Version 3.3 - Improved logic for building the Groups listing.

    15Sept2014 - Version 3.4 - Fixed logic for inserting a new Group.

    24July2015 - Version 3.8 - Huh, haven't updated this in a while.  Uhm...this particular update appends the date/time stamp to the beginning and end of the log file.

    10May2016 - This version no longer supported.  Please use Tableau Active Directory Assistant - version 4.

     

    Foreword

    As I am evolving in my role as a Tableau Server administrator I'm finding some things difficult -- or at least cumbersome -- to perform.  A big one right now is a true synchronization of our corporate Active Directory to our Tableau Server Groups.  Ideally this effort should be done by the server itself.  To help me with my manual synchronization process I developed this application.

     

    The problem we [as administrators] face is that we can sync an AD group but any new people come over Unlicensed and in the AD group...but suppose on the Tableau Server the Group is a different name?  There's no look-up table to automatically do this so we're stuck with the names assigned to the AD group   Where I work the names ain't pretty.  Finally, if a person is deleted from an AD group they are NOT removed from the Group on the Tableau Server!  Whatchatalkin' about, Willis?!

     

    What This Does

    The application helps the Tableau Server administrator delete users, remove users from a Tableau Group if they don't exist in the AD group, add users (including publishing rights and licensing), and add users to a Group after performing an AD sync.

     

    Batteries Not Included

    The attached .zip file contains three required files:

    1. Groups_Users.twb  --  A Tableau Desktop report.  This is required as it will take a before and after snapshot of the Tableau Server.
    2. tActiveDirectoryAssistant.docx  --  Documentation in MS Word.  Yes, read this as it tells you what to do.
    3. tActiveDirectoryAssistant.xlsm  --  The Excel application.  This uses macros so it MUST be enabled!
    4. Pull_GroupsUsers.bat -- Optional. Batch file for those wishing to have a starting template to have a scheduler generate the Group_User .CSV file.

    Unzip these files into a directory.  This directory is where all the action will take place.

     

    You will need Tableau Desktop, tabcmd installed on the machine you plan to run this application, and Microsoft Excel.  I used Excel to base my application on because it's so ubiquitous -- you'll find it on almost every desktop in the business world.  By example, I've worked for several companies where they don't have Access but they all had Excel (and Word, of course).  Even still, Excel should be a required tool for all report developers and administrators as it's very handy to have when checking data.

     

    Lastly, you will need to have full access to your Tableau Server db in order to use the Groups_Users.twb workbook.  This is very easily accomplished by following the steps in the DAAP documentation.

     

    Operation

    Please read the supplied documentation but this is essentially how the whole process works:

    1. You open Tableau Desktop and open the Group_Users.twb report.  That data is copied into the "Before" worksheet in the Excel workbook.
    2. You then log on to your Tableau Server and perform an AD sync.
    3. You switch over to your Tableau Desktop and refresh the report.  Copy the data and paste it into the "After" worksheet in the Excel workbook.
    4. Ctrl + Shift + s will open the user form where you select your server, site (optional), proxy (optional), and type in your login ID and password portal credentials for Tableau Server.
    5. Finally, click the button for the action you want to perform, either delete users or add them, including adding them to a specified Group or removing them from a Group if they aren't present in the similarly named AD group.
    6. The application will find the users that are different between the worksheets and build an appropriate CSV file.
    7. It will then build a batch script (a.k.a. batch file, the terms are interchangeable) and execute it.  The batch script will then carry out the desired actions i.e., it will be executed.
    8. Done!

    The theory behind this is very simple.  It's simply automating what we normally have to do, however, its power comes into play when you have tens, hundreds, thousands, or more users in an AD group that you must maintain.  I wish I had this when I had to import 1200+ users...doing it manually gave me a shaky pointer finger and a bad mood

     

    The nice thing with this application is that it is unprotected so you can view and edit the VBA code if you wish (and you're on your own if you do).  Also, the files that are generated are not deleted so you can view them as well, even modify them and manually run them if you desire.

     

    Misc.

    There are no options for assigning the various admin rights.  These folk are few between so it's a job that is, and I feel should be, done manually by a server administrator.

     

    The Tableau workbook credentials will need to be changed to the Tableau Server being logged on to.  Because the workbook is connecting directly to the Tableau Server database you will need to provide those credentials as well (Create Custom Administrative Views).

     

    Use it as it was designed to be used as.  Trying to run it with the columns in the wrong place or using the wrong data will likely give you an ugly, raw VBAerror message.  If you try to break it you definitely will as I didn't add robust error handling on purpose.  Just the basics to keep it working.  If you must have a better application from me then I charge $100/hr in one hour increments and I accept PayPal.

     

    Your Humble Tableau Server Padawan,

        Toby Erkson

    tADA_v3-8.zip (646.7 KB) Download