PowerShell can be a very powerful tool when applied correctly, however it has some quirks – one of which is formatting size numbers for remote PowerShell connections to Office 365.
By default, when you get a list of item sizes (e.g. mailbox sizes, or mailbox folder sizes etc), PowerShell helpfully formats the size for you as a human-readable value. This is nice when you’re outputting to the console, but infuriating when you’re outputting to, say, a CSV that you then need to manipulate.
As an example – I’m trying to add up the size of all Public Folders in a client’s Exchange Online tenancy.
This command does exactly what I want:
Get-PublicFolderStatistics | Select-Object Name, FolderPath, ItemCount, TotalItemSizeMB
Well, almost – it displays the sizes like this:
422.2 MB (442,706,389 bytes)
This is OK when I’m reading it on the screen, not so much when I want to export use Export-CSV and manipulate it in Excel.
As it turns out, TotalItemSize has a property whereby it can convert to a standard, consistent unit like MB. This property only works however when running it against an on-prem Exchange server, not with Exchange Online. it doesn’t return an error, it just doesn’t return any sizes if you use something simple like this:
@{Name="TotalItemSizeMB";Expression={$_.TotalItemSize.Value.ToMB()}}
So, we need to get considerably more complicated and work with the string value that we’re given for the size instead
@{Name="TotalItemSizeMB"; Expression={[math]::Round(($_.TotalItemSize.ToString().Split("(")[1].Split(" ")[0].Replace(",","")/1MB),0)}}
That was simple, wasn’t it?
The full command, including exporting it to a CSV then becomes:
Get-PublicFolderStatistics | Select-Object Name, FolderPath, ItemCount, @{Name="TotalItemSizeMB"; Expression={[math]::Round(($_.TotalItemSize.ToString().Split("(")[1].Split(" ")[0].Replace(",","")/1MB),0)}} | Export-CSV .\foldersizes.csv
Couldn’t be easier, right?
Thanks for this command and switches, very useful.! Just what I needed
Is there a way to add the Get-PublicFolder command to add the public folder identity? This could be done in Exchange 2010 public folders with something like;
Get-PublicFollder “\” -Recurse |Select Identity, @{Label=”TotalItemSize KB”;Expression={$_.TotalItemSize.Value.ToKB()}
Hi Sophie,
It’s my understanding that when you’re running PowerShell on an Exchange server (on-prem Exchange, not Exchange Online) then the PowerShell cmdlets are returning actual objects that can be sorted by size correctly.
When you’re running PowerShell against Exchange Online however, the output is serialised over the ssh or WSMan connection and it arrives in your local PowerShell session as text.
Things like sorting by item size are then not operating on actual sizes in bytes, but rather operating on text strings. It’s annoying, and I wish Microsoft would have some option to return all sizes as bytes that could then be converted to kB, MB or GB in the local PowerShell session, rather than than returning a formatted text string.
Can the Get-PublicFolder Identity value be included? This was possible in Exchange 2010.
Get-PublicFolder “\” -Recurse -ResultSize unlimited | Get-PublicFolderStatistics | sort-Object -Descending |select name,Identity,MailEnabled,ParentPath,Itemcount, @{expression={$.totalitemsize.value.ToKB()};label=”TotalItemSiZe(KB)”}, @{expression={$.TotalDeletedItemSize.value.ToKB()};label=”TotalDELItemSiZe(KB)”}