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?
Comments