There are countless third-party reporting tools available for Exchange Server, but many tend to be expensive. Some of the reporting tools for Exchange Server are really nothing more than GUI front ends to PowerShell. PowerShell reporting can expose a wealth of information about your Exchange Server environment if you know the right commands to use. This tip gives an introduction to some ways to use PowerShell for Exchange Server reporting.
Exchange makes so much statistical information available through PowerShell that your imagination and your PowerShell skills are your only limits. Having said that, I'll show a way to expose some useful mailbox data. Later, I'll show you how to write that data to a report file.
The easiest way to access some basic mailbox statistics with PowerShell reporting is to enter the following command:
Get-Mailbox | Get-MailboxStatistics
This command will return the display name, item count, storage limit status and last logon time for each mailbox. This information is helpful, but we can make it more useful.
Forcing Exchange to display the attributes you care about can make the command more useful. To show you what I mean, enter the following command. You can substitute any mailbox name if you don't have an Administrator mailbox.
Get-Mailbox Administrator | Get-MailboxStatistics | Select-Object *
This command reveals an unabridged list of statistics for the specified mailbox (Figure 1). The column on the left displays the attribute names. Any of these attributes can be included in your PowerShell reports. Suppose you want to create a simple report that shows the mailbox display name, item count and total size. You could do so by entering the following command:
Get-Mailbox | Get-MailboxStatistics | Select-Object DisplayName, ItemCount, TotalItemSize
The output of such a command for the Administrator's mailbox will appear soon afterward (Figure 2).
Figure 1. This is a full list of the mailbox statistics with which you can work.
Figure 2. You can customize PowerShell reports to include the most useful statistics.
PowerShell also has some great filtering capabilities. Suppose I want to know which two users out of my entire Exchange Server organization consume the most space on my mailbox server. I could use PowerShell reporting to show only the top two users by entering the following command:
Get-Mailbox | Get-MailboxStatistics | Select-Object –First 2 TotalItemSize, DisplayName, ItemCount
Exporting your PowerShell reports
PowerShell reporting has good capabilities, but we've only looked at Exchange Server data on a screen. In the real world, you'd probably want to export the data to a file for later viewing.
You can create a CSV file by using the Export-CSV cmdlet and providing the path and filename for the file you want to create. Suppose you wanted to create a CSV file containing all your Exchange mailboxes. You could enter the Get-Mailbox cmdlet with any filters, expressions and attributes you want to use, then pipe the output into the Export-CSV cmdlet. The command would look something like this:
Get-Mailbox | Export-CSV C:\Data\Report.csv
Creating an HTML-based report is slightly more involved. You can still use the same basic principle you use to create a CSV file, but you'll have to use the Select-Object cmdlet to explicitly specify the object attributes that you want to include in the output. You would then append the ConvertTo-HTML cmdlet, the Out-File cmdlet, and the path and filename of the report you want to create.
Suppose you want to create an HTML report that listed the display name and item count of each mailbox. The command might look something like this:
Get-Mailbox | Get-MailboxStatistics | Select-Object DisplayName, ItemCount | ConvertTo-HTML | Out-File C:\Data\Report.htm
PowerShell reporting has powerful capabilities. Even though I used mailbox statistics in my examples, you can use PowerShell to report on virtually any aspect of your Exchange organization.
About the author:
Brien Posey is an eight-time Microsoft MVP for his work with Windows Server, IIS, Exchange Server and file system storage technologies. Brien has served as CIO for a nationwide chain of hospitals and health care facilities, and was once responsible for IT operations at Fort Knox. He has also served as a network administrator for some of the nation's largest insurance companies.