Every so often I see folks run into issues with scripts/one-liners that they obtained from a blog or crafted themselves. One common issue is when they think the command is perfect and then when they go to dump the output to a file, the content is mince. **
Imagine your surprise when you open up the output file expecting pristine data, and it starts with:
#TYPE Microsoft.PowerShell.Commands.Internal.Format.FormatStartData
As an example, we can use the below script that I saw a customer try last week:
Get-Mailbox –Database DB01 –Resultsize Unlimited | Get-MailboxFolderStatistics | Where {$_.ItemsInFolder -GT 50000} | Sort-Object -Property ItemsInFolder -Descending | Format-List Identity, ItemsInFolder | Export-CSV $PWDNaughtyMailboxes.csv
This is meant to get a list of mailboxes in a given database, then look at each folder in turn to see if any of them have a number of items in excess of 50,000. This code looks to run successfully and produces the following output.
You proclaim “This is excellent – job done!” However, when you open up the .CSV file in Excel, the results appear to be less than excellent…..
The output below is not really what you wanted to see…
#TYPE Microsoft.PowerShell.Commands.Internal.Format.FormatStartData
"ClassId2e4f51ef21dd47e99d3c952918aff9cd","pageHeaderEntry","pageFooterEntry","autosizeInfo","shapeInfo","groupingEntry"
"033ecb2bc07a4d43b5ef94ed5a35d280",,,,"Microsoft.PowerShell.Commands.Internal.Format.ListViewHeaderInfo",
"9e210fe47d09416682b841769c78b8a3",,,,,
"27c87ef9bbda4f709f6b4002fa4af63c",,,,,
"27c87ef9bbda4f709f6b4002fa4af63c",,,,,
"27c87ef9bbda4f709f6b4002fa4af63c",,,,,
"27c87ef9bbda4f709f6b4002fa4af63c",,,,,
"27c87ef9bbda4f709f6b4002fa4af63c",,,,,
"27c87ef9bbda4f709f6b4002fa4af63c",,,,,
"27c87ef9bbda4f709f6b4002fa4af63c",,,,,
"27c87ef9bbda4f709f6b4002fa4af63c",,,,,
"27c87ef9bbda4f709f6b4002fa4af63c",,,,,
"27c87ef9bbda4f709f6b4002fa4af63c",,,,,
"27c87ef9bbda4f709f6b4002fa4af63c",,,,,
"27c87ef9bbda4f709f6b4002fa4af63c",,,,,
"4ec4f0187cb04f4cb6973460dfe252df",,,,,
"cf522b78d86c486691226b40aa69e95c",,,,,
Where did the wheels fall off the bus??
Stuck In The Middle With You
<courtesy link to Gerry Rafferty and Stealers Wheel>
Taking a closer look at the PowerShell code, carefully read through it and think about what each cmdlet does. The heading for this section is a clue….
Get-Mailbox –Database DB01 –Resultsize Unlimited | Get-MailboxFolderStatistics | Where {$_.ItemsInFolder -GT 50000} | Sort-Object -Property ItemsInFolder -Descending | Format-List Identity, ItemsInFolder | Export-CSV $PWDNaughtyMailboxes.csv
At this point you should be thinking why is there a Format-List in the middle of this? If so then you are on the money.
As discussed previously, in this series of posts, PowerShell does not pass raw text down the pipeline. It passes .NET objects. Format-List, Format-Table and Format-Wide convert the underlying objects so that they can then be rendered for output. The format cmdlets, such as Format-List, arrange the data to be displayed but do not display it. The data is displayed by the output features of Windows PowerShell and by the cmdlets that contain the Out verb (the Out cmdlets), such as Out-Host, Out-File, and Out-Printer. If you do not use a format cmdlet, Windows PowerShell applies that default format for each object that it displays.
Whilst this looks OK on the screen, as soon as you pipe this to the next cmdlet that expects .NET objects bad things happen….
The Format-List, Format-Table and Format-Wide cmdlets should be the last ones that are on the pipeline, not in the middle.
With that in mind, how do we then select particular objects in the middle of the pipeline if Format-List cannot be used? We use the Select-Object cmdlet instead. This does not prepare the object for output, and they are left as objects which allows them to be piped to the next cmdlet.
Breakthrough
This is why if you remove the Export-CSV cmdlet, the output looks OK on the screen:
My lab does not have humungous mailboxes which is why the above item count was changed to 5, but that is irrelevant for the issue here.
And if you pipe to Get-Member to look at the objects at the end of the pipeline, you will notice that one they have passed through Format-List they are no longer the native Exchange objects:
TypeName: Microsoft.PowerShell.Commands.Internal.Format.FormatStartData
Now compare this with replacing the Format-List with Select-Object. Note that the output object type is still a native Exchange class:
TypeName: Selected.Microsoft.Exchange.Management.Tasks.MailboxFolderConfiguration
If we change the PowerShell code, replacing the Format-List with Select-Object we get:
Get-Mailbox –Database DB01 –Resultsize Unlimited | Get-MailboxFolderStatistics | Where {$_.ItemsInFolder -GT 50000} | Sort-Object -Property ItemsInFolder -Descending | Select-Object Identity, ItemsInFolder | Export-CSV $PWDNaughtyMailboxes.csv
And the output looks like what we need/expect:
Bonus Tip
Adding –NoTypeInformation to the end of the above command means that you will not see the type information in the output CSV file. In this case this would be:
#TYPE Selected.Microsoft.Exchange.Management.Tasks.MailboxFolderConfiguration
Cheers,
Rhoderick
** – This is a Scottish technical term stating the said item does not meet or exceed the functional spec. There are indeed other more colourful phrases, but I can’t really post them here!
