Get-MailboxStatistics to Include OU name in Exported CSV file
Hello, Can the Get-MailboxStatistics include the OUof the mailbox when you export it to a CSV? Right now the command I am running is:Get-MailboxStatistics | Sort-Object TotalItemSize -Descending | Select DisplayName, StorageGroupName, ItemCount, @{expression={$_.TotalItemSize.value.ToMB()}} | Export-CSV C:\MailboxSizes.csvI would like the CSV file to include the OU name for the mailbox, and can't seem what I need to add to the Select field.Thanks
October 30th, 2009 10:23pm

Hi, Get-mailboxstatistics does not return OU and StorageGroupName. You can you use custom object instead to club the properties of two cmdlets. you can try something like this below. Paste in notepad and save it as PS1 file. I have not tested it so let us know if this does not work as desired. #Script starts $MBxStats = @() Get-mailbox | foreach { $stats = get-mailboxstatistics -id $_ $MBx = new-object system.object $mbx | add-member -membertype noteproperty -value $_.organizationalUnit -name OrganizationalUnit $mbx | add-member -membertype noteproperty -value $stats.Displayname -name DisplayName $mbx | add-member -membertype noteproperty -value $stats.ItemCount -name ItemCount $mbx | add-member -membertype noteproperty -value @{exression={$stats.Totalitemsize.value.ToMb()}} -name TotalSize $mbx | add-member -membertype noteproperty -value $(get-mailboxdatabase -id $_.database).storagegroup -name StorageGroupName $MBxStats += $mbx } $MBxStats | Sort-object TotalSize | export-csv c:\MailboxSizes.csv #Script end Hope this helps Thanks.Vishal Ramnani | MCITP - Exchange 2007 | MCSE Messaging | MCTS - Win 2008 Config
Free Windows Admin Tool Kit Click here and download it now
October 30th, 2009 11:56pm

Thank you, it created the csv file with the OU column, but now the TotalSize is saying System.Collections.Hashtable for all users.Thanks
October 31st, 2009 12:09am

$mbx | add-member -membertype noteproperty -value @{exression={$stats.Totalitemsize.value.ToMb()}} -name TotalSize This line doesn't work (there's also a typo: not eXRession, but eXPRession). Anyhow, it should be:$mbx | add-member -membertype noteproperty -value $stats.Totalitemsize.value.ToMb() -name TotalSizeMCTS: Messaging | MCSE: S+M | Small Business Specialist
Free Windows Admin Tool Kit Click here and download it now
October 31st, 2009 1:21am

agreed.. :) Thanks for correcting it Jon.Vishal Ramnani | MCITP - Exchange 2007 | MCSE Messaging | MCTS - Win 2008 Config
October 31st, 2009 5:01am

Thanks for posting this script, Vishal! This is my second encounter with Select-Object vs. Add-Member. In a more generic sense, it show how to combine the results of three PowerShell cmdlets. Sooner or later each Exchange administrator will need to do this. Hence I commented the script a bit Sdamlo720 whishes to retrieve this information and add the OU for each user. Select DisplayName, StorageGroupName, ItemCount, TotalItemSize This could be done with Select-Object (select), but the Select-Object statement will kill the original objects and create new disposable ones instead, which you can no longer use in other operations, e.g. pipe to yet another cmdlet, as Export-Csv. I struggled with this problem for two days until I came across this blog Select is bad, Add-Member is good - Dmitrys PowerBlog: PowerShell and beyond http://dmitrysotnikov.wordpress.com/2008/08/27/select-object-vs-add-member/ And while you're at it, with more than an "one-liner at a time": My PowerShell IDE is PowerGUI (free tool) Tiding up and explaining the script: # Initalize a dynamic array (table). # This table will hold these records (retrieved with these PowerShell commands) # DisplayName (Get-MailboxStatistics), OrganizationalUnit (Get-Mailbox), StorageGroupName # (Get-MailboxDatabase), ItemCount and TotalSize (Get-MailboxStatistics) $mbxStats = @() # Retrieve all mailboxes (you might have more than 1000, which is the default) Get-Mailbox -ResultSize Unlimited | # Loop through each mailbox ForEach-Object { # Retrieve the identiry and store it in the variable $_ $stats = Get-MailboxStatistics -id $_ # Create a new instance of a .Net object $mbx = New-Object System.Object # Add user-defined customs members: the records retrieved with the three PowerShell commands $mbx | Add-Member -MemberType NoteProperty -Value $stats.Displayname -Name DisplayName $mbx | Add-Member -MemberType NoteProperty -Value $_.organizationalUnit -Name OrganizationalUnit $mbx | Add-Member -MemberType NoteProperty -Value $(Get-MailboxDatabase -id $_.database).storagegroup -Name StorageGroupName $mbx | Add-Member -MemberType NoteProperty -Value $stats.ItemCount -Name ItemCount $mbx | Add-Member -MemberType NoteProperty -Value $stats.TotalItemSize.value.ToMB() -Name TotalSize # Add right hand operand to value of variable ($mbx) and place result in variable ($mbxStats) $mbxStats += $mbx } # Pipe the result and sort by TotalSize. Export to .csv file. Use Unicode format $mbxStats | Sort-Object TotalSize | Export-Csv -Encoding 'Unicode' c:\MailboxSizes.csv Array Literals In PowerShell - Windows PowerShell Blog http://blogs.msdn.com/powershell/archive/2007/01/23/array-literals-in-powershell.aspx MCTS: Messaging | MCSE: S+M | Small Business Specialist
Free Windows Admin Tool Kit Click here and download it now
November 2nd, 2009 9:30am

Note that in the Exchange Management Shell for Exchange 2010, the following lines do not work: # Retrieve all mailboxes (you might have more than 1000, which is the default) Get-Mailbox -ResultSize Unlimited | # Loop through each mailbox ForEach-Object { If you modify them as follows, the script works as designed: # Retrieve all mailboxes (you might have more than 1000, which is the default) $Mailboxes = Get-Mailbox -ResultSize Unlimited # Loop through each mailbox $Mailboxes | ForEach-Object {
April 20th, 2011 2:54pm

Thank you very much for your comment. I did first see it this morning. And I do think you were right at the time you posted it, because I noticed myself that this kind of scripts did not tun on Exchange 2010. But now I just tested it on Exchange 2010 SP1 RU3. It does run. Of course Storage Groups don't make any sense, but that another story.MCTS: Messaging | MCSE: S+M
Free Windows Admin Tool Kit Click here and download it now
July 2nd, 2011 2:19am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics