Audit and Control Management Server 2013 - 'User' or 'Last Saved By' column does not show the username

  • Article

Microsoft Audit and Control Management Server 2013 (ACM) determines the username of the user that last modified a file by communicating with the operating system at the time the file is saved. Sometimes that process is not successful, at which point ACM reads the Last Saved By file property of the Excel workbook. This is different than the Author.

 

 

The Last Saved By property gets updated automatically when a workbook is saved. It gets populated with whatever value the user has in the User name field within Excel options:

 

 

Here are the scenarios in which ACM may not get the identity of the user from the operating system and instead use the value in the Last Saved By field: 

1. High simultaneous activity. This can cause an excessive number of operating system events between the file server and the ACM server. 

2. Network and processor latency. When the event is thrown for a modified file, the system is notified and must turn around and ask the operating system for the name of the user that currently has the file locked. If there is sufficient latency in the system, the user may no longer have the file locked by the time the request is made. 

3. Too many monitored network locations. ACM has addressed this in software by consolidating the UNC paths as much as possible with a site, but with multiple sites, it may be possible to define enough distinct monitored locations to exceed the OS limit. There is no hard limit on this. 

4. First version of the file. This could happen if you copied and pasted the files to a monitored folder.

5. Service account not an admin. If the ACM service account is not in the Administrators group on the file server where the file is saved, the username may not be recorded.

 

In those cases, it will use the Last Saved By value in the file properties, and it will append the phrase (from file properties) to the name in the User or Last Saved By column.

 

 

If the User Name field in Excel Options is blank, then it will not populate the Last Saved By field in the file properties. If this happens, and ACM does not get the username notification, ACM will show the following in the User or Last Saved By column: Unavailable on file rescan

 

There are a few methods you can use to make sure users have the proper username in their Excel workbook Last Saved By property, so that the correct name will always be recorded in the file properties when they save a file.

1. Make sure that users have the User name field populated in Excel Options > General tab . You can use a GPO to force this setting to populate automatically with the user's name even if they try to delete it.

2. If you are unable to use a GPO, you can deploy some custom VBA code to always update this user property just before the user saves a file. So if they change their username in Excel, it will get corrected just before they save any file. 

3. If you store files in SharePoint or some other document management system, then SharePoint will get the correct identity and report it to ACM.

4. Make sure that the following setting is disabled in Excel (this is a document specific setting): Options > Trust Center > Trust Center Settings > Privacy Options > Remove personal information from file properties on save

When opening certain Excel workbooks and looking at the Privacy Options, the Remove personal information from file properties on save is unchecked and grayed out. This prevents users from enabling this setting, so the Last Saved By field should be populated. This is the default behavior for workbooks in Excel 2013. However, in other Excel workbooks this setting may not be grayed out and could be enabled by users, and this prevents the Last Saved By field from getting populated.

When you create a new workbook in Excel by going to File > New within Excel, this setting is grayed out and unchecked. But when you go to Windows Explorer, right-click and go to New > Microsoft Excel Worksheet to create a new workbook, the Remove personal information from file properties on save setting is enabled and checked, and the checkbox setting can be changed by the user.

 

 

This setting is document specific, and the default behavior of the setting is different depending on how you create a new workbook.

There is a way to disable the privacysetting when creating a new workbook by using the Windows Explorer menu item New > Microsoft Excel Worksheet. On your machine, open Excel 2013 and create a new workbook by going to File > New. Keep the workbook blank and just save it to your machine with the name EXCEL12.XLSX. Then go to one of the following paths in Windows Explorer (path depends on whether Office 2013 is MSI or Click-to-Run based installation):

C:\Program Files\Microsoft Office 15\root\vfs\Windows\SHELLNEW

or

C:\Windows\SHELLNEW

You will see a file called EXCEL12.XLSX. This file template is an from an older version of Excel, and this template is used to create new workbooks through Windows Explorer. The older version of Excel files allows users to change the privacy setting. Rename this file to something else, then copy the new file that you created earlier into this folder to replace the original file. Now when you go to New > Microsoft Excel Worksheet and check the Privacy Options of the file, the Remove personal information from file properties on save option will be unchecked and grayed out as expected as it is using the new version of the EXCEL12.XLSX template file. The new file can be pushed out to all domain computers to replace the old one so that no one can create a new workbook with that setting enabled.

Note that Access database files do not have the Last Saved By property, so the username will show up as Unavailable on file rescan in ACM if it did not get the identity from Windows.