locked
Get-MailboxStatistics to Include OU name in Exported CSV file RRS feed

  • Question

  • Hello,
          Can the Get-MailboxStatistics include the OU of 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.csv

    I 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
    Friday, October 30, 2009 7:23 PM

Answers

  • 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
    • Marked as answer by Alan.Gim Monday, November 2, 2009 6:21 AM
    Friday, October 30, 2009 8:56 PM
  • $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 TotalSize
    MCTS: Messaging | MCSE: S+M | Small Business Specialist
    • Marked as answer by Alan.Gim Monday, November 2, 2009 6:21 AM
    Friday, October 30, 2009 10:21 PM

All replies

  • 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
    • Marked as answer by Alan.Gim Monday, November 2, 2009 6:21 AM
    Friday, October 30, 2009 8:56 PM
  • Thank you, it created the csv file with the OU column, but now the TotalSize is saying System.Collections.Hashtable for all users.

    Thanks
    Friday, October 30, 2009 9:09 PM
  • $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 TotalSize
    MCTS: Messaging | MCSE: S+M | Small Business Specialist
    • Marked as answer by Alan.Gim Monday, November 2, 2009 6:21 AM
    Friday, October 30, 2009 10:21 PM
  • agreed.. :)

    Thanks for correcting it Jon.

    Vishal Ramnani | MCITP - Exchange 2007 | MCSE Messaging | MCTS - Win 2008 Config
    Saturday, October 31, 2009 2:01 AM
  • 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 - Dmitry’s 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
    Monday, November 2, 2009 6:30 AM
  • 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 {
    
    

    Wednesday, April 20, 2011 6:48 PM
  • 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
    Saturday, July 2, 2011 6:13 AM
  • Get-MailboxStatistics to Include OU name in Exported CSV file, you the script below:

    #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 $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

    Yomi Ogunbajo

    Microsoft Exchange Consultant 

    Friday, September 15, 2017 10:45 AM