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.
Best Answer
Last Logon time being blank usually means it hasn't been used at all. However I personally would adjust the script to remove the requirement for the time limit
Then export those results to CSV and sort the results by the dates involved. That will give you a better idea if the results you are getting back are expected.
This script on the TechNet gallery would give you better results though. https://gallery.technet.microsoft.com/scriptcenter/List-Inactive-Mailboxes-on-1ac82ddf as it is based on the last sent item, rather than logon date. If you have a BES or other product that scans the mailboxes then the logon date can be very inaccurate.