1 2 3 Previous Next 31 Replies Latest reply on Jul 29, 2016 1:11 PM by Toby Erkson

    What's in your workbooks? The TWB Auditor v2.0

    Toby Erkson

      I realize Andy Cotgreave is a busy dude so I'm posting first and asking for forgiveness later   So Andy, if you're out there, please let me know if you would like me to change anything about this post.

       

      I finally got around to looking at Andy's masterpiece, What's in your workbooks? The TWB Auditor a couple months ago.  The issue I ran into was getting all of our workbooks from our Tableau Server.  I figured that if I was having this issue then there would be others as well so I decided to add a new function to his already excellent tool to help others out.

       

      What I basically did was add the ability for a user or Tableau Server Administrator to download all of the workbooks from a specified Site from their Tableau Server.  The folder can then be audited by Andy's TWB Auditor application.

       

      Please read the attached Word document for more information (it's only three pages, with pictures, so it'll be quick).  Because I added my code and made modifications to Andy's code you will also need to download the attached version of the TWB Auditor.

                                                  

      What I learned

      Not every workbook can be downloaded as a .twb.  Turns out many of my workbooks I was trying to download were really .twbx!  Because of this, all workbooks downloaded are saved as .twbx.  If a workbook fails to open properly then change the file extension to .twb and all will be good.  I modified the code in the TWB Auditor to perform this when necessary.

      Updates

      24Aug2015 - Updated PowerShell scripting file to include --no-cert per Derek's request and a longer timeout period (an issue I was having).

      13July2016 - Version 2.1:  Download workbooks by Site or by Project.

        • 1. Re: What's in your workbooks? The TWB Auditor v2.0
          Marie Limousin

          Thank you so much for sharing this tool. It is really useful!

           

          Is there a way it can be modified (or if I missed, where I can see it) to include the table that the field comes from? RIght now, the source column name is mentioned in the worksheet field download, however, if I have the same column name repeated in several tables within the same connection, I have no way to distinguish them.

           

          THank you!
          ~~Marie

          • 2. Re: What's in your workbooks? The TWB Auditor v2.0
            Beatriz Manzano

            Hello Toby, Beatriz again.

             

            I cannot get any details on dimensions and fields used from a workbook using a cube as a datasource.

            I see the connection but nothing else.

            Any ideas please?

            • 3. Re: What's in your workbooks? The TWB Auditor v2.0
              Derek Arsenault

              For it to work in our environment with a server accepting only HTTPS connection I had to modify your source VBA to include --no-certcheck in the command to grab the workbooks.xml file:

               

              'Query the Server and store a list of all Tableau Server workbooks

              Print #1, "tabcmd get workbooks.xml -f """ & sFilePath & "workbooks.xml"""

               

              changed to:

              'Query the Server and store a list of all Tableau Server workbooks

              Print #1, "tabcmd get workbooks.xml -f """ & sFilePath & "workbooks.xml""" & "--no-certcheck"

              • 4. Re: What's in your workbooks? The TWB Auditor v2.0
                Derek Arsenault

                As I wait for the script to complete now...we have a lot of workbooks...

                 

                Would be cool if we could filter the job on Project Area so that is doesn't scan the entire site.

                 

                Really like the work you have done on this. It has saved me considerable time! For my use case - if you are interested - is to keep a "sandbox" area clean. Before I "clean" (delete) all of the workbooks in the sandbox I wanted to back them up...unfortunately for me right now the script is downloading all of the workbooks on the site, not just the sandbox project area. I'm ok with it but would be nice improvement to be able to filter on project area.

                 

                So if you are working on a v2.1 I'd add the option for HTTPS connections I talked about above + the option to scan only a single project area (or a selection of project areas?).

                • 5. Re: What's in your workbooks? The TWB Auditor v2.0
                  Toby Erkson

                  Derek Arsenault wrote:

                   

                  For it to work in our environment with a server accepting only HTTPS connection I had to modify your source VBA to include --no-certcheck in the command to grab the workbooks.xml file:

                   

                  'Query the Server and store a list of all Tableau Server workbooks

                  Print #1, "tabcmd get workbooks.xml -f """ & sFilePath & "workbooks.xml"""

                   

                  changed to:

                  'Query the Server and store a list of all Tableau Server workbooks

                  Print #1, "tabcmd get workbooks.xml -f """ & sFilePath & "workbooks.xml""" & "--no-certcheck"

                  Interesting, because the line above that includes --no-certcheck

                  'Login to the Tableau Server
                  Print #1, "tabcmd login -s """ & sServer & """" & sSiteProxy & " -u """ & sUser & """ -p """ & sPass & """ --no-certcheck --timeOut 6000 "  'Log on to TS"
                  

                  Thanks, I'll update my code.

                  • 6. Re: What's in your workbooks? The TWB Auditor v2.0
                    Toby Erkson

                    Derek Arsenault wrote:

                     

                    As I wait for the script to complete now...we have a lot of workbooks...

                     

                    Would be cool if we could filter the job on Project Area so that is doesn't scan the entire site.

                     

                    ...

                     

                    So if you are working on a v2.1 I'd add the option for HTTPS connections I talked about above + the option to scan only a single project area (or a selection of project areas?).

                    For anyone not familiar with this tool:

                    You can specify a Site so that does allow for some filtering.  Make sure you have your list of Servers and Sites populated and saved in the Server_Settings table before you begin:

                    You get to select the specific Server/Site to pull from when you execute the "Pull workbooks from the Tableau Server" menu item:

                     

                     

                    Derek, I'll see what I can do to filter down by Project.

                    1 of 1 people found this helpful
                    • 7. Re: What's in your workbooks? The TWB Auditor v2.0
                      Andy Cotgreave

                      Awesome work Toby! I'm delighted to see people extending this. Making it work with Server is the logical next step. Many thanks! Andy

                      • 8. Re: What's in your workbooks? The TWB Auditor v2.0
                        Lazy Rambler

                        Wonderful. Thanks a lot, Toby.

                         

                        I hope this is the right place to post this question/issue.

                        It works great when I audit workbooks from a folder on my PC. But, I encounter some issues when pulling workbooks from the server.

                         

                        After running the audit(followed the instructions from your word doc), the powershell script gets created, but it does not create the workbooks.xml

                         

                        Anything I have been doing wrong? Noob here. It's be great if someone helped me out.

                        Thanks.

                        • 9. Re: What's in your workbooks? The TWB Auditor v2.0
                          Toby Erkson

                          It can be any of the following:

                          1.  You do not have tabcmd installed on the computer where you are running the TWB Auditor.

                          2.  The PATH environmental variable does not have the correct file path to tabcmd.exe on your computer.

                                Here's the path I used:  C:\Program Files\Tableau\Tableau Server\9.0\extras\Command Line Utility

                                Setting the environmental variable PATH for PowerShell  <-- this is the same method to set the tabcmd path!!!

                          3.  The login you are using doesn't have sufficient permissions.  You should be using a Tableau administrator login.

                          4.  You have insufficient permissions in the directory location that you are directing the output to (the files).

                           

                          I'm basing this on the snippet of VBA code that logs onto the Server and gets the necessary file information for the workbooks.xml file:

                          'Login to the Tableau Server.   --timeout 14400 = 4 hours
                          Print #1, "tabcmd login -s """ & sServer & """" & sSiteProxy & " -u """ & sUser & """ -p """ & sPass & """ --no-certcheck --timeout 14400 "  'Log on to TS"
                          
                          
                          'Query the Server and store a list of all Tableau Server workbooks
                          Print #1, "tabcmd get workbooks.xml -f """ & sFilePath & "workbooks.xml"" --no-certcheck --timeout 14400"
                          
                          • 10. Re: What's in your workbooks? The TWB Auditor v2.0
                            Lazy Rambler

                            Thanks Toby. I double check just in case.

                             

                            1.  You do not have tabcmd installed on the computer where you are running the TWB Auditor- Installed tabcmd on my personal machine.I'm able to run commands from my personal machine.

                             

                            2.  The PATH environmental variable does not have the correct file path to tabcmd.exe on your computer.

                                  Here's the path I used:  C:\Program Files\Tableau\Tableau Server\9.0\extras\Command Line Utility: Changed the path using the help doc provided.

                             

                                  Setting the environmental variable PATH for PowerShell  <-- this is the same method to set the tabcmd path!!!

                             

                            3.  The login you are using doesn't have sufficient permissions.  You should be using a Tableau administrator login. I have been using an admin account. We have SAML integrated, so it wouldn't accept my ID. So, I created a new server admin local account and have used it. It runs fine when I run a stand alone command.

                             

                            4.  You have insufficient permissions in the directory location that you are directing the output to (the files).- I have admin access to the folder/System.

                             

                             

                            So, I went ahead and was executing the powershell line by line. I was able to login, and I was able to get the workbooks.xml(individually)

                             

                            Not really sure what I have been doing wrong.

                            • 11. Re: What's in your workbooks? The TWB Auditor v2.0
                              Toby Erkson

                              So is it working for you now?

                              • 12. Re: What's in your workbooks? The TWB Auditor v2.0
                                Lazy Rambler

                                Sadly no. I cannot see a workbook.xml being created in the folder, just the powershell script.

                                • 13. Re: What's in your workbooks? The TWB Auditor v2.0
                                  Toby Erkson
                                  • Try the attached (you'll need to re-set up the Server_Settings table) and let me know if it works or not.

                                                 I made a change in the code that should fix the issue.  The adjustment worked for my test.

                                  • What version of Tableau Server are you running?  I'm currently on 9.0.
                                  • 14. Re: What's in your workbooks? The TWB Auditor v2.0
                                    Lazy Rambler

                                    Thanks Toby. I will try it out and keep you updated.

                                     

                                    We are on version 9.

                                    1 2 3 Previous Next