Exchange 2007 - Finding Users with Items Over 180 Days

I am currently working on a migration from an in-house Exchange 2007 infrastructure to a hosted environment. As part of this move, there are new restrictions being placed on the mailboxes. One of these restrictions is that all mail over 180 days old is automatically deleted from the system. Prior to migrating the mailboxes, I need to know how many and which users have items older than 180 days.
Finding this information was a two-step process:

1. Export all of the mailbox folder statistics to a CSV file
2. Scrub the resulting data with Excel to get a list of the users and their departments with mail items over 180 days old

The first part of this process was to export the folder statistics for each mailbox. I accomplished this using a combination of PowerShell cmdlets:

get-mailboxdatabase | get-mailbox -resultsize unlimited | get-mailboxfolderstatistics -folderscope all -includeoldestandnewestitems | export-csv mailbox_stats.csv

The cmdlets break down this way:

• get-mailboxdatabase fetches each database (this infrastructure employs multiple databases)


• get-mailbox runs through each mailbox in the current database


• get-mailboxfolderstatistics returns specifics about the folders in the mailbox. -folderscope all returns information about each folder under the top of information store and -includeoldestandnewestitems includes the dates of the oldest item in the folder and the newest item in the folder


• export-csv obviously exports the results to a CSV file
  

Once the information is in the CSV format:

1. I used a filter on the OldestItemReceiveDate field with "before" criteria for 180 days prior to the cut-off date


2. Once I had the list of results narrowed down to those with mail items over 180 days, I used some VBA functions to extract the user name and the department (the users are placed into OUs by department and the result of the get-mailboxfolderstatistics cmdlet is the full "path" to the users folder including domain and OUs, so it was pretty easy for me to get their department)


3. I then copied the results to a new list and used an advanced filter on this list to get unique records only, and I had the final list of users with items over 180 days.