Analyzing Storage Quotas with Excel 2007

With Windows Server 2003, the RECYCLER folder (a hidden folder located on the network drive) that is created when a user's documents are redirected counts against the user's quota. Although there are some positives to this (such as, giving the user the ability to restore deleted files, and an easy way to clean up a user's folder to get them under quota), the down-side is with the implementation of quotas in an existing environment. Here is the scenario:

• Existing users don't have storage quotas
• Even when users try to get their storage below the quota, the files are moved to the RECYCLER folder
  
• Windows Server 2003 R2 with My Documents redirected to a share
• Soft Quotas of 500 MB on user folders (no notification set)
  

The method I chose to get this information was a combination of:

• File Server Resource Manager Storage Reports
• DU.exe (SysInternals Tool for reporting folder sizes)
• Excel 2007
  

1. Run a storage report for users that used 100% of quota
2. Copy that information into a new sheet in Excel
3. Run DU.exe on the root of the user folders (this will show every folder, even if hidden)
   
4. Copy that information into a new sheet in Excel
5. Create a new sheet to merge the data for analysis

The specifics of how I merged the data using Excel's built-in functions is as follows:

• On the sheet with the DU output:

1. Create a new column and use =Find("RECYCLER", A1). Copy this formula to every row that has an entry. This will show the position number for any entry that contains the word "RECYCLER". (This is column B)
   
2. Create a new column and use =LEFT(A1,FIND("\RECYCLER",A1)). Copy this formula to every row that has an entry. This will return all the contents of the row up to "\RECYCLER". An Example would be "2,316,645 D:\Users$\Agents\John.Doe\". (This is column C)
   
3. Create a new column and use =LEFT(E22,LEN(E22)-1). Copy this formula to every row that has an entry. This will Parse the username from the folder path. In the above example it would be "John.Doe". (This is column D)
4. Create a final column to parse the size of the folder. =LEFT(C22,FIND("D:\",C22)-1) will return "2,316,645". Copy this formula to all rows that contain data. (This is column E)
5. Now put a filter on the columns to filter out the following:

• Column B - Only rows with a value greater than 0 (zero). These are only the rows that have "RECYCLER" in the folder path.
  
• Column D - Only rows with a username
• Column E - Only rows with a value greater than 0 (zero)

Now that the data is parsed for the RECYCLER information, a similar action needs to be completed for the users that are over their 500 MB quota.

• On the sheet with the results of the Storage Report:

1. Hide all columns with data with the exception of column A - this will leave only the user folder path
2. Hide all rows that do not contain the folder path (this is just for the ease of reading)
3. In column G, enter =MID(A2,18,25) to parse the username from the path. Copy this formula to every row that has an entry.

On a new sheet, used for the analysis and comparison of the two reports, copy the username and folder size from the DU output. In column C, enter =VLOOKUP(A2,'Over Quota'!G2:G84,1,FALSE). This will lookup the values for the users that are over quota and if the username appears in column C, they have a RECYCLER folder as well as being over the quota. Copy this formula to every row that has an entry. If no entry is matched "#N/A" will be displayed in column C. This means that the user doesn't have a RECYCLER folder, but is still over their quota.

The way the data is interpreted:

• Users that have a RECYCLER and are over their quota need to have the RECYCLER folder emptied. This is a quick way to help the user get below their quota without having to go through all of their files.
• Users that do not have a RECYCLER folder, but are over their quota will have to begin removing files from their folder to get under the limit

Some additional analysis (if needed) can be a comparison of the RECYCLER folder size to the usage from the storage report. This can provide important information about how much space will be freed from emptying the RECYCLER folder, thus further defining which users will be under their quota by performing this action. The quick steps for this analysis are:

1. On the storage report data sheet, unhide all columns
2. In the row with the user name, enter =LEFT(C3,FIND("MB",C3)-1), Copy this formula to every row that has an entry. NOTE: the row used in this formula is not the same row as the user's name, it is one row below due to the format of the storage report.
3. On the analysis sheet, create a new column and perform another VLookup against the username with the following: =VLOOKUP(A2,'Over Quota'!G2:H84,2,FALSE), Copy this formula to every row that has an entry.This will copy the storage usage for each of the users.

Comparing the new column that contains the usage from the storage report with the RECYCLER folder size will give a good indication of which users will need to be directed to clean up their storage and which users can just empty the RECYCLER folder to get under the limit. Using some filters here could make the analysis easier to read by filtering out users that aren't over their quota or filtering out users that don't have a RECYCLER folder.

NOTE: There is a way to turn this feature off by using Group Policy to 'disable moving files to the Recycle Bin'. This policy is applied to the user and also disables the Recycle Bin on the user's workstation.