How to use TWB Auditor with TableauFS: Audit Tableau Server files directly -Databoss

FOR ANALYSTS FOR BI PROS FOR EVERYONE Tableau

How to use TWB Auditor with TableauFS: Audit Tableau Server files directly

July 15, 2015

audit, data source, tableau server, tableaufs, twb, twb auditor, twbx

If you never heard about this powerful and extremely useful tool by Andy Cotgreave then you missed something serious. It makes life easier with auditing all your tableau workbooks on your computer, getting information like which workbook connects to where, what are the used tables and fields, calculations – things what we desperately need during our daily professional life. This is an example what can you do with it: Here’s a link to a published version of the Viz workbook. However, it missed one important function:

Audit workbooks directly on the server

This is not the case anymore. With TableauFS which runs perfectly on Windows with Docker you can directly index/audit your remote server.

So, let’s get it started

Firstly get the latest version of TWB Auditor from (http://community.tableau.com/thread/118450) and mount your Tableau Server as window share. For more information about mounted drives of Tableau Server please check my previous note here: https://databoss.starschema.net/tableaufs-on-windows-with-docker/ Mount it as a drive and check if you can access the files.

Adding stuff to make it “TableauFS compliant”

Due to two things (first is file naming differences on local, windows file systems vs network file systems and second is read only file system adds read only flag for all files) you need slightly change Andy’s tool. No worries, few lines only.

You should change the file mask in FileList module:

Option Compare Database
Option Explicit

'list files to tables
'http://allenbrowne.com/ser-59alt.html

Dim gCount As Long ' added by Crystal

Sub runListFiles()
    'Usage example.
    Dim strPath As String _
    , strFileSpec As String _
    , booIncludeSubfolders As Boolean
    
    strPath = "E:\"
    strFileSpec = "*.*"
    booIncludeSubfolders = True
    
    ListFilesToTable strPath, strFileSpec, booIncludeSubfolders
End Sub

Public Function AddFileToTable(strFile As String, Optional bEmptyFilesTableFirst As Boolean = True)
    Dim strName As String
    Dim strFolder As String
    Dim strSQL As String
    Dim fso As FileSystemObject
    
    If bEmptyFilesTableFirst Then
        CurrentDb.Execute ("DELETE * FROM FilesToAudit")
    End If
    
    Set fso = New FileSystemObject
    
    strName = fso.GetFileName(strFile)
    strFolder = Replace(strFile, strName, "")
    
    strSQL = "INSERT INTO FilesToAudit (FName, FPath) SELECT '" & strName & "', '" & strFolder & "'"
    
    CurrentDb.Execute (strSQL)
End Function


'crystal modified parameter specification for strFileSpec by adding default value
' http://allenbrowne.com/ser-59alt.html
Public Function ListFilesToTable(strPath As String _
    , Optional strFileSpec As String = "*.*" _
    , Optional bIncludeSubfolders As Boolean _
    , Optional bEmptyFilesTableFirst As Boolean _
    , Optional bShowResultMessage As Boolean _
    ) As Long
On Error GoTo Err_Handler
    'Purpose:   List the files in the path.
    'Arguments: strPath = the path to search.
    '           strFileSpec = "*.*" unless you specify differently.
    '           bIncludeSubfolders: If True, returns results from subdirectories of strPath as well.
    'Method:    FilDir() adds items to a collection, calling itself recursively for subfolders.
    
    Dim colDirList As New Collection
    
   Dim mStartTime As Date _
      , mSeconds As Long _
      , mMin As Long _
      , mMsg As String
      
   mStartTime = Now()
   gCount = 0
   
   If bEmptyFilesTableFirst Then
    CurrentDb.Execute ("DELETE * FROM FilesToAudit")
   End If
   '--------
    
    Call FillDirToTable(colDirList, strPath, strFileSpec, bIncludeSubfolders)
      
   mSeconds = DateDiff("s", mStartTime, Now())
   
   mMin = mSeconds \ 60
   If mMin > 0 Then
      mMsg = mMin & " min "
      mSeconds = mSeconds - (mMin * 60)
   Else
      mMsg = ""
   End If
   
   mMsg = mMsg & mSeconds & " seconds"
   
   If bShowResultMessage Then
    MsgBox "Done adding " & Format(gCount, "#,##0") & " files from " & strPath & IIf(Len(Trim(strFileSpec)) > 0, " for file specification --> " & strFileSpec, "") & vbCrLf & vbCrLf & mMsg, , "Done"
   End If
   
   ListFilesToTable = gCount
  
Exit_Handler:
   SysCmd acSysCmdClearStatus
   '--------
    
    Exit Function

Err_Handler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, , "ERROR"
    
    'remove next line after debugged -- added by Crystal
    Stop: Resume 'added by Crystal
    
    Resume Exit_Handler
End Function

Private Function FillDirToTable(colDirList As Collection _
    , ByVal strFolder As String _
    , strFileSpec As String _
    , bIncludeSubfolders As Boolean)
   
    'Build up a list of files, and then add add to this list, any additional folders
    On Error GoTo Err_Handler
    
    Dim strTemp As String
    Dim colFolders As New Collection
    Dim vFolderName As Variant
    Dim strSQL As String

    'Add the files to the folder.
    strFolder = TrailingSlash(strFolder)
    strTemp = Dir(strFolder & "*.TW*")
    Do While strTemp <> vbNullString
         gCount = gCount + 1
         SysCmd acSysCmdSetStatus, gCount
         strSQL = "INSERT INTO FilesToAudit " _
          & " (FName, FPath, Extension) " _
          & " SELECT """ & strTemp & """" _
          & ", """ & strFolder & """" _
          & ", """ & Right$(strTemp, Len(strTemp) - InStrRev(strTemp, ".")) & """;"
         CurrentDb.Execute strSQL
        colDirList.Add strFolder & strTemp
        strTemp = Dir
    Loop

    If bIncludeSubfolders Then
        'Build collection of additional subfolders.
        strTemp = Dir(strFolder, vbDirectory)
        Do While strTemp <> vbNullString
            If (strTemp <> ".") And (strTemp <> "..") Then
                If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0& Then
                    colFolders.Add strTemp
                End If
            End If
            strTemp = Dir
        Loop
        'Call function recursively for each subfolder.
        For Each vFolderName In colFolders
            Call FillDirToTable(colDirList, strFolder & TrailingSlash(vFolderName), strFileSpec, True)
        Next vFolderName
    End If

Exit_Handler:
    
    Exit Function

Err_Handler:
    strSQL = "INSERT INTO FilesToAudit " _
    & " (FName, FPath) " _
    & " SELECT ""  ~~~ ERROR ~~~""" _
    & ", """ & strFolder & """;"
    CurrentDb.Execute strSQL
    
    Resume Exit_Handler
End Function

Public Function TrailingSlash(varIn As Variant) As String
    If Len(varIn) > 0& Then
        If Right(varIn, 1&) = "\" Then
            TrailingSlash = varIn
        Else
            TrailingSlash = varIn & "\"
        End If
    End If
End Function

116

117

118

119

120

121

122

123

124

125

126

127

128

Dim strTemp As String

Dim colFolders As New Collection

Dim vFolderName As Variant

Dim strSQL As String

'Add the files to the folder.

strFolder = TrailingSlash(strFolder)

strTemp = Dir(strFolder & "*.TW*")

Do While strTemp <> vbNullString

gCount = gCount + 1

SysCmd acSysCmdSetStatus, gCount

strSQL = "INSERT INTO FilesToAudit " _

& " (FName, FPath, Extension) " _

The DeleteUnpackagedWorkbook function was unable to delete temp files from my temp folder(c:\usres…\temp\..). It is caused by that, on TableuFS shared drive every file is read only. So when the files were unzipped by the TWB Auditor was also read only. I have inserted a short code to remove read-only attribute from files if there is any:

Option Compare Database

Public g_Tableau_EXE As String


' This function takes unpacks a TWBX and returns the name of the unpacked TWB
'---------------------------------------------------------------------------------------
' Procedure : UnpackageTWBX
' Author    : Andy
' Date      : 17/05/2012
' Purpose   :
'---------------------------------------------------------------------------------------
'
Public Function UnpackageTWBX(txtWorkbook As String, iTimeOut As Integer) As String
    Dim strWorkbookOnlyName As String
    Dim strParentFolder As String
    Dim strUnpackagedWorkbook As String
    Dim fso As FileSystemObject
    Dim f As File
    Dim fList As Object
    Dim iPause As Integer
    Dim tmpFolder As String
        
   On Error GoTo UnpackageTWBX_Error

    SetUpLoggingFunctionName "UnpackageTWBX"
    LogSomething fnEntry, "unpacking " & txtWorkbook, True
    ' set up some variables
    Set fso = New FileSystemObject
    strWorkbookOnlyName = fso.GetBaseName(txtWorkbook)
    strParentFolder = fso.GetParentFolderName(txtWorkbook)
    strUnpackagedWorkbook = fso.BuildPath(strParentFolder, strWorkbookOnlyName & "." & TABLEAU_UNPACKAGED_EXTENSION)
    UnpackageTWBX = strUnpackagedWorkbook
    iPause = iTimeOut
    tmpFolder = GetTemporaryFolderName(True)
    If tmpFolder = vbNullString Then
        UnpackageTWBX = "fail"
        LogSomething fnExitOnFail, "Could not create temp folder"
    Else
        ' create a copy in the temp folder (renamed to have ZIP at the end)
        fso.CopyFile txtWorkbook, tmpFolder & "\"
        Set f = fso.GetFile(fso.BuildPath(tmpFolder, fso.GetFileName(txtWorkbook)))
        f.Name = Replace(f.Name, ".TWBX", ".ZIP")
        
        ' extract the files in the temp folder
        Extract f.Path, tmpFolder
        
        UnpackageTWBX = Replace(f.Path, ".ZIP", ".TWB")
        If fso.FileExists(UnpackageTWBX) = False Then
            UnpackageTWBX = "fail"
            
            ' can't find the file. maybe the TWB is not the same name as the TWBX
            Set fList = fso.GetFolder(f.ParentFolder).Files
            For Each f In fList
                If fso.GetExtensionName(f) = "TWB" Then
                    UnpackageTWBX = f.Path
                    Exit For
                End If
            Next 'f
            
            If UnpackageTWBX = "fail" Then
                LogSomething fnError, "Didn't find a TWB file in the TWBX"
            End If
        End If
        LogSomething fnExit
    End If

    On Error GoTo 0
    
    Exit Function

UnpackageTWBX_Error:

    UnpackageTWBX = "fail"
    LogSomething fnExitOnFail, Err.Number & ", " & Err.Description
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure UnpackageTWBX of Module UnpackageWorkbooks"
    
End Function

' this function deletes the workbook and its related data file
'---------------------------------------------------------------------------------------
' Procedure : DeleteUnpackagedWorkbook
' Author    : Andy
' Date      : 17/05/2012
' Purpose   :
'---------------------------------------------------------------------------------------
'
Public Function DeleteUnpackagedWorkbook(txtWorkbook As String) As Boolean
    Dim fso As FileSystemObject
    
    ' set up some variables
   On Error GoTo DeleteUnpackagedWorkbook_Error

    Set fso = New FileSystemObject
    
    If g_bDidNotUnpackagedThisTWBX Then
        ' Don't need to delete anything because we used the unpackaged version of the workbook
        DeleteUnpackagedWorkbook = True
    Else
        ' does the TWB exist
        If fso.FileExists(txtWorkbook) Then
            Set objFSO = CreateObject("Scripting.FileSystemObject")
            Set objFolder = objFSO.GetFolder(fso.GetFile(textWorkbook).ParentFolder)
            Set colFiles = objFolder.Files
            
            For Each objFile In colFiles
                If objFile.Attributes And ReadOnly Then
                    objFile.Attributes = objFile.Attributes Xor ReadOnly
                End If
            Next
                        
            ' yes - delete it's folder
            fso.DeleteFolder (fso.GetFile(txtWorkbook).ParentFolder)
        End If
    End If

   On Error GoTo 0
   Exit Function

DeleteUnpackagedWorkbook_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure DeleteUnpackagedWorkbook of Module UnpackageWorkbooks"
End Function

' we kept a record of all the TWBX files we unpacked in
' admnPackagedWorkbooks. They should all have been deleted as we went along
' but this function just checks that nothing fell through the net.
'---------------------------------------------------------------------------------------
' Procedure : SweepUpTheTWBXFiles
' Author    : Andy
' Date      : 17/05/2012
' Purpose   :
'---------------------------------------------------------------------------------------
'
Public Function SweepUpTheTWBXFiles()
    Dim rst As DAO.Recordset
    Dim sObject As String
    Dim fso As FileSystemObject
   On Error GoTo SweepUpTheTWBXFiles_Error

    Set fso = New FileSystemObject
    Set rst = CurrentDb.OpenRecordset("SELECT * From admnPackagedWorkbooks")
    
    SetUpLoggingFunctionName "SweepUpTheTWBXFiles"
    LogSomething fnEntry, "Sweeping up ", True
    
    If Not (rst.EOF And rst.BOF) Then
        rst.MoveFirst
        Do Until rst.EOF = True
            sObject = rst("UnpackagedTWBFile")
            LogSomething fnNote, "Checking for: " & sObject
            If fso.FileExists(sObject) Then
                LogSomething fnNote, "File exists. Deleting: " & sObject
                fso.DeleteFile (sObject)
            End If
            
            sObject = rst("UnpackagedFileFolder")
            If fso.FolderExists(sObject) Then
                LogSomething fnNote, "Folder exists. Deleting: " & sObject
                fso.DeleteFolder (sObject)
            End If

            rst.MoveNext
        Loop
    End If
    
    LogSomething fnExit

   On Error GoTo 0
   Exit Function

SweepUpTheTWBXFiles_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure SweepUpTheTWBXFiles of Module UnpackageWorkbooks"
    
End Function

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

Public Function DeleteUnpackagedWorkbook(txtWorkbook As String) As Boolean

Dim fso As FileSystemObject

' set up some variables

On Error GoTo DeleteUnpackagedWorkbook_Error

Set fso = New FileSystemObject

If g_bDidNotUnpackagedThisTWBX Then

' Don't need to delete anything because we used the unpackaged version of the workbook

DeleteUnpackagedWorkbook = True

Else

' does the TWB exist

If fso.FileExists(txtWorkbook) Then

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFolder = objFSO.GetFolder(fso.GetFile(textWorkbook).ParentFolder)

Set colFiles = objFolder.Files

For Each objFile In colFiles

If objFile.Attributes And ReadOnly Then

objFile.Attributes = objFile.Attributes Xor ReadOnly

End If

' yes - delete it's folder

fso.DeleteFolder (fso.GetFile(txtWorkbook).ParentFolder)

End If

Just change/add the highlighted lines and move forward.

How to use the TWB Auditor

After downloading & unzipping TWB Auditor, open the TWB Auditor - data.accdb Please note that you will need an MS Access to use it, obviously.

Here you can easily start with a double click the audit of your workbooks, with the “Audit all workbooks in a folder”. Then select the proper option for audit:

Check for twbx files in subdirectories

As you can see I would like to search for TWBX files (check the “include packaged workbooks”) on my Z:\ shared drive where my Tableau Server was previously mounted.

After the audit is completed you will see the results of the audit, which workbooks has been audited, and what is the workbook path. After the run is completed, just close TWB Auditor.

These are not local files if you know what I mean

Using the results

Open the TWB auditor - viz.twb workbook which is located where you have unzipped the Twb Auditor. Please do read the TWB Auditor introduction worksheet.

Do not forget to read the instructions and refresh the datasources

And refresh all the data sources. (Data / Refresh all extracts)

What’s in my workbooks

On “What’s in my workbooks” will provide you information about each workbook and their worksheets.

On other sheet you can check the connection type of the workbooks:

Or you can easily check “Where is that field used” :

One word: lovely.

Rerunning the audit

Just one more important thing: if you need to refresh the audit database and you don’t want to accumulate your audit history, just hit “Delete all data from TWB Auditor” before the next run.

That’s it, simple and easy (sometimes even fast). In case if you have any questions or issues just drop a comment.

Author
Recent Posts

Tamás Földi

Director of IT Development at Starschema

Decades of experience with data processing and state of the art programming. From nuclear bomb explosion simulation to distributed file systems. ethical hacking, real time stream processing practically I always had a great fun with those geeky ones and zeros.