2

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

Andor
  • 152
  • 2
  • 8
  • There's not much you can do to "optimize" your one-liner. I'd combine the first two cmdlets in your pipeline to the following: `Get-Mailbox -RecipientTypeDetails UserMailbox -ResultSize Unlimited`, but that's only a minor improvement. You might improve things by switching the order of your `Sort-Object` and `Select-Object` cmdlets too. – pk. Jun 05 '12 at 20:27
  • I think both answers given (by pk. and my own results) are effective and good responses and provide the requested and necessary output. – Andor Jun 15 '12 at 10:01

1 Answers1

2

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.

pk.
  • 6,451
  • 2
  • 42
  • 63
  • Executing test-run. – Andor May 18 '12 at 14:21
  • Get the following error: "Sending data to a remote command failed with the following error message: The total data received from the remote client exceeded allowed maximum. Allowed maximum is 524288000", any suggestions how to circumvent this? – Andor May 18 '12 at 14:29
  • Hi there, my sincere apologies for not getting back to you anytime sooner: I have been away for a while. I will try to followup on this today – Andor May 25 '12 at 08:25
  • I am reluctant to use the wrapper you referred to and have changed the command to use Out-File instead. I am running the command now to see the results and I will report back regarding the outcome. – Andor Jun 05 '12 at 17:27
  • If you install PowerShell 3.0, you'll get access to the `-Append` parameter for `Export-XML` without having to resort to the wrapper. – pk. Jun 05 '12 at 18:32