Audit and Control Management Server 2013 - Analyzing Change History Results

Microsoft Audit and Control Management Server 2013 monitors spreadsheets and reports the changes made. However, you have to be careful interpreting results of a workbook comparison on very simple test files. There is no guarantee that the change history details in ACM Server will reflect exactly the changes that were performed by the user, since the system is comparing two versions of the file, and not actually tracking user activity.

The workbook comparison analyzes across some of the rows that contain data to identify which columns have been affected, and it analyzes up and down the some of the columns that contain data to identify which rows were affected.   It also analyzes at the overall size of the sheet (total number of rows/columns).   When you have very simple workbooks, the sample size of data is not always enough to get results that reflect exactly the steps that the user performed, especially if you make changes that are beyond the area containing most data on the sheet.

Excel files do not contain any definitive markers to show which rows/columns were affected, so the comparison does the best that it can given the available data.  It uses differencing logic to determine the most likely reason for the differences between the two files.  Since Excel does not maintain absolute row/column markers and does not track keystrokes, the software must rely on the differencing logic to determine the most likely changes. The comparison looks for patterns of data to align the differences in the two files, and then reports the changes based on that.  If finding the patterns is difficult, especially in the case of very simple spreadsheets, then the comparison could report differences that don't match the exact steps the user took to make the changes. There are no hard and fast rules that you can use to know when the results will be an exact match for what the user did.

Also, there are some cases where the data is too complex for the comparison to be able to figure out which rows/columns were affected.  In ACM Server, the user has the option to specify these as Raw Data Sheets, which will cause ACM server to ignore the possibility of inserted/deleted rows and columns in the specified sheet.  An example would be a sheet where data is imported from a database and most of the rows have different data.  In this case, it may give more accurate results if you designate the sheet as a Raw Data Sheet.

In general, it's nearly impossible to know exactly how the user got to the end result, because the system is not tracking keystrokes.  If you make changes to a workbook and give it to a person to review, they would have no way to know whether you deleted 1 row and then another row, or if you deleted 3 rows and then added 1 row, as an example.  The workbook might look the same even though you arrived at it by different ways. Another example is that you could delete the last column in a workbook, or you could simply delete all the data from the cells in the last column.  If you give somebody the workbook before and after, there's no way for them to know which way the changes were made. The system is making the best guess at what could have happened to arrive at the end result.