Exchange Server 2013 : Exploring useful EMS examples (part 1) - Outputting a CSV file

The first example shows how to discover users who might be suffering from performance problems because they have very large folders in their mailboxes. The number of items that is considered bad has grown over time in line with the updates Microsoft has made to tune the database schema. With Exchange 2000 or Exchange 2003, the danger mark is around 5,000 items. The threshold increases to 20,000 with Exchange 2007 and leaps to 100,000 for Exchange 2010 onward. The client used is also important because Outlook 2010 and Outlook 2013 are better at dealing with large folders than Outlook 2007 is. Having more than 20,000 items in a folder is evidence of solid pack-rat behavior by anyone, and it marks a folder that probably will never be cleaned out simply because it takes too much effort to explore the contents and decide what should be kept and what should be deleted. Assume that you want to flag potential issues to users who have more than 5,000 items in a folder. You can use code like this:

Get-Mailbox –Server ExServer2 | Get-MailboxFolderStatistics | Where {$_.ItemsInFolder –GT 5000} | Sort ItemsInFolder
–Descending | Format-Table Identity, ItemsInFolder
–AutoSize

Identity                                                      ItemsInFolder
---------                                                     -----------------
contoso.com/Exchange Users/Redmond, Eoin\I                    5271
contoso.com/Exchange Users/Ruth, Andy\Inbox                   5265
contoso.com/Exchange Users/Andrews, Ben\Inbox                 5263
contoso.com/Exchange Users/Pelton, David\Inbox                5230
contoso.com/Exchange Users/Simpson, David\Inbox               5218
contoso.com/Exchange Users/Redmond, Tony\Sent Items           5215

Of course, it would be impolite to send a note to these users to remind them that good filing practices lead to clean mailboxes, but you can still think about it!

This code does the following:

If you run this command against an Exchange 2010 or Exchange 2013 server, even details of the folders in the dumpster (for example, Deletions) will be shown that are not reported by an Exchange 2007 server.

Many examples of outputting CSV files from Exchange data use the Export-CSV cmdlet. For instance, here’s a two-line script that looks for any mailbox that has an ActiveSync partnership created, which indicates that the user has connected a mobile device to the mailbox by using ActiveSync. An expression is included to force a call to the Get-ActiveSyncDevice cmdlet to retrieve the count of devices associated with each user. This kind of information is useful when understanding how many people actually connect mobile devices to Exchange!

$Mbx = Get-CASMailbox –Filter {HasActiveSyncDevicePartnership –eq $True} | Get-Mailbox
$Mbx | Select DisplayName, UserPrincipalName, @{Name="Devices";Expression={(Get-ActiveSyncDevice –Mailbox $_.Identity).Count)} | Export-CSV "c:\temp\ActiveSync.csv" –NoTypeInformation

Export-CSV is great because it takes care of all the formatting issues required to create a valid CSV file that will be recognized by applications such as Excel. However, there are other ways to generate CSV data. This script creates a CSV file you can use to analyze mailbox usage (Figure 1). A check in the code limits processing to the mailboxes found in a specific database and ignores anything but user mailboxes. (Objects such as room or arbitration mailboxes are ignored.) This script could take quite a while to finish if there are more than a few hundred mailboxes in the selected database, so be sure to test it on perhaps a smaller group before you launch it to process larger collections.

Figure 1. User mailbox CSV data

$Outputfile = "C:\temp\Mailboxes.csv"
Out-File -FilePath $OutputFile -InputObject "UserPrincipalName, Items, Mailbox Size" -Encoding UTF8
$mbx = Get-Mailbox –Database DB2
Foreach ($M in $Mbx)
   {
   if ($M.RecipientTypeDetails -eq "UserMailbox")
        {
        # Fetch information about the mailbox
        $UserMailbox = Get-Mailboxstatistics -Identity $($M.Identity)
       $UserPrincipalName = $M.UserPrincipalName
       $ItemSizeString = $UserMailbox.TotalItemSize.ToString()
       $MailboxSize = "{0:N2}" -f ($ItemSizeString.SubString(($ItemSizeString.
IndexOf("(") + 1),($itemSizeString.IndexOf(" bytes") - ($ItemSizeString.IndexOf("(") + 1))).Replace(",","")/1024/1024)
       $ItemCount = $UserMailbox.ItemCount
    #Prepare the user details in CSV format for writing to file and append line
      $UserDetails = $UserPrincipalName + "," + $ItemCount + "," + $MailboxSize
      Out-File -FilePath $OutputFile -InputObject $UserDetails -Encoding UTF8 -append
        }
   }

This script generates fairly basic data about mailboxes, and if you scan the Internet, you can find many other approaches to the problem of mailbox reporting, some of which are much better than others. With anything to do with mailboxes, the key is speed because code that is quite good at processing one or two mailboxes might not be smart when confronted with a few thousand. It’s also a good idea to consider what information needs to be output and make sure that the data reported is formatted in a way that is most useful to the reader.