Powershell: Wanting Exchange statistics and running into (output) limitations

exchange-2010powershellscripting

I am trying to gather mailbox statistics for all mailboxes in our organization, exporting it to an Excel /CSV file. This is the code I came up with /gathered so far:

Get-Mailbox -ResultSize Unlimited |Get-MailboxStatistics | where {$_.ObjectClass -eq “Mailbox”} | Sort-Object TotalItemSize -Descending |FT @{label=”User”;expression={$_.DisplayName}},ServerName,Database,@{label=”Total Size (MB)”;expression={$_.TotalItemSize.Value.ToMB()}} |Out-File -Append -FilePath C:\Users\Public\Documents\MailboxSize.log

  1. When using Export-Csv, the file written doesn't contain the requested information /output. It only shows 'gibberish'
  2. When running above script I am faced with a 'Allowed maximum is 524288000' Powershell message and only (a small) part of the requested data is logged

Can you help me solve the two remarks made?

= = = = = =

This is what I am currently using. It provides me the requested details and output. Although I am looking to further optimize this part, in a larger scheme of things, I am happy with the fact that it is a single-liner command:

Get-Recipient -ResultSize Unlimited |Where {$_.RecipientType –eq “UserMailbox”} |Get-MailboxStatistics | Sort-Object TotalItemSize –Descending |Select-Object @{label=”User”;expression={$_.DisplayName}},@{label=”Total Size (MB)”;expression={$_.TotalItemSize.Value.ToMB()}},@{label=”Items”;expression={$_.ItemCount}},@{label=”Storage Limit”;expression={$_.StorageLimitStatus}} |Export-CSV -NoTypeInformation -Path c:\users\public\documents\$Filename

I do have to adjust the PSSessionConfiguration:
Set-PSSessionConfiguration -name microsoft.powershell -MaximumReceivedObjectSizeMB 800 -Force

Best Answer

You can't use Export-CSV after Format-Table in your pipeline. If you want to use Export-CSV, change your Format-Table to a Select-Object.

Get-Mailbox -ResultSize Unlimited | Get-MailboxStatistics | where {$_.ObjectClass -eq “Mailbox”} | Sort-Object TotalItemSize -Descending | Select-Object @{label=”User”;expression={$_.DisplayName}},ServerName,Database,@{label=”Total Size (MB)”;expression={$_.TotalItemSize.Value.ToMB()}} | Export-CSV "C:\Users\Public\Documents\MailboxSize.log" -notypeinformation

As it stands now, you are not getting all of the mailboxes in your organization. Your query is filtering out Discovery Mailboxes, Room Mailboxes, Equipment Mailboxes, etc. You may have already realized this fact, but I just wanted to make it clear. If you'd like to improve the performance of your query, you might want to filter out those extraneous boxes at the beginning of your pipeline by modifying the Get-Mailbox cmdlet to filter on RecipientTypeDetails.

Get-Mailbox -RecipientTypeDetails UserMailbox -ResultSize Unlimited

As far as the 524288000 limit goes, you have a few options. You can modify the limit in the web.config for the maxAllowedContentLength property, but I wouldn't suggest it since we really don't know what the upper bound is that you'll need to pull this script off. Furthermore, that could change in the future as you add more mailboxes and your script will break again. Setting the limit too high could also cause some issues.

Your other option is the process the data in chunks. Since you're ultimately exporting to a CSV and there is no native -Append parameter in Export-CSV, you'll have to use a workaround. Dmitry Sotnikov has created a wrapper for the Export-CSV cmdlet that implements this functionality and that would allow you to export to CSV line by line.

Update: In PowerShell 3.0, Export-CSV has an append parameter.

Your command would be modified to something like the following --

$sortedStats = Get-Recipient -ResultSize Unlimited -RecipientTypeDetails UserMailbox | Get-MailboxStatistics | Sort-Object TotalItemSize -Descending

foreach ($s in $sortedStats) {
    $s | Select-Object @{label=”User”;expression={$_.DisplayName}},ServerName,Database,@{label=”Total Size (MB)”;expression={$_.TotalItemSize.Value.ToMB()}} | Export-CSV "C:\Users\Public\Documents\MailboxSize.csv" -notypeinformation -Append
}

This is all completely untested on my part, but that's how I imagine it working. There'll be a performance hit. I'd be interested to know your results if you end up going this route.

Related Topic