Combining multiple audit logs

Audit logs enable you to track all the file transfers and logins within your account. Learn how to enable logs. Enabling logs can only be accomplished by admins or users. Not managed users or contacts. Learn more about the differences between users. Combining multiple logs allows you to filter, navigate and organize your logs in one simple excel sheet. To learn how to combine logs, follow the instructions below:

1. Start by downloading your logs of choice. Using the web interface will allow you to download all your logs in a compressed folder. Extract all the logs to the directory of your choice. Our recommendation would be to use an FTP client such as FileZilla to select and download multiple logs simultaneously.
2. Next, create a folder in C://Users called “excelfolder”. Separate your transfer logs from your login logs and then select, copy and paste the logs you want to combine into the excelfolder just created. It’s important that the folder path is c:\users\excelfolder to ensure the script provided below works correctly.
   • Alternatively, you can save and name the folder anywhere – you will need to copy the folder path into the script below and replace: “C:\Users\excelfolder” (keep quotations) with your folder path.
3. Once complete, open a blank excel document. Click “file” and go to “options”. Click on “Customize Ribbon” and then enable “Developer” located on the right-hand side. Click OK to complete the change in options. Once this is done you will notice a new tab called developer.
4. Click on the developer tab and find visual basic and select “This WorkBook”. In the editor field, copy and paste the following script below:

Sub simpleXlsMerger()
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject(“Scripting.FileSystemObject”)

‘change folder path of excel files here
Set dirObj = mergeObj.Getfolder(“C:\Users\excelfolder”)
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)

‘change “A2” with cell reference of start point for every files here
‘for example “B3:IV” to merge all files start from columns B and rows 3
‘If your files using more than IV column, change it to the latest column
‘Also change “A” column on “A65536” to the same column as start point
Range(“A2:IV” & Range(“A65536”).End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate

‘Do not change the following column. It’s not the same column as above
Range(“A65536”).End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next
End Sub

Please note the importance of the folder path: “ c:/users/excelfolder” if the folder does not exist within the correct path the script will not work. If you would like a different folder path, you must change the folder path in the script above.

Finally, click the run button to populate the new excel document with the files inside the folder we created.

Now, with one giant excel worksheet, we can filter and search for specific users and view all the transfers and logins conducted by this user.

Learn how to enable logs in your Hosted~FTP~ account: click here.