pulling fields from multiple levels and formatting output on a one-liner

I needed a simple one-liner to list all huge email folders. I came up this one - simple, clear and self-documenting:

[PS]> get-mailbox | Get-MailboxFolderStatistics | where {$_.foldersize -gt 5Gb} | sort -desc foldersize | select identity, foldersize | ft -auto

Identity                                                                 FolderSize
--------                                                                  ----------
domain.local/MyBusiness/Users/Dave\Inbox             6.155 GB (6,608,825,034 bytes)
domain.local/MyBusiness/Users/John\Sent Items      5.112 GB (5,489,056,989 bytes)

What I want is this:

DisplayName  Name        FolderSize
-----------       ----           ----------
Dave             Inbox          6.155 GB
John             Sent Items   5.112 GB

or this, which is just trimming the Identity and FolderSize fields I already have:

Identity                   FolderSize
--------                    ----------
Dave\Inbox             6.155 GB
John\Sent Items      5.112 GB

In the first example, I need the DisplayName field, and If I'm on the folder level, DisplayName is blank; how can I pull it from the mailbox level? (In other words, a Format-List at the mailbox level shows the user name is Name; and at the folder level, the folder name is Name.)

In the second example, can I manipulate the output and have it still be a simple one-liner?

Any experts out there who can shed some light will be greatly appreciated. I really can use the report as-is, but it would be much cleaner with the repeating information removed.

Rob

February 23rd, 2015 12:30pm

Hi Rob,

If you really want to keep the "oneliner" (which I find less intuitive than an actual well laid out script), you can try this:

Get-Mailbox | 
    Get-MailboxFolderStatistics | 
        Where {$_.foldersize -gt 5Gb} | 
            Sort -Descending foldersize | 
                Select @{N='identity';E={($_.Identity -split '/')[-1]}}, foldersize | 
                    Format-Table -AutoSize

Free Windows Admin Tool Kit Click here and download it now
February 23rd, 2015 12:42pm

Mike,

thanks for the reply - now how would you do it with a script?  :^)

And for you or for any other takers out there, how to get data from two different levels - user and folder level?

Rob

February 23rd, 2015 4:02pm

I'd do something like this:

$mbxs = Get-Mailbox 

$output = foreach ($mbx in $mbxs) {

    $userDisplayName = $mbx.DisplayName

    $mbx | Get-MailboxFolderStatistics | 
        Where {$_.foldersize -gt 5Gb} | 
            Sort -Descending FolderSize | 
                Select @{N='User';E={$userDisplayName}},Name, FolderSize

} 

$output | Sort User | Format-Table -AutoSize

What do you mean by 'data from user and folder level'?

Free Windows Admin Tool Kit Click here and download it now
February 23rd, 2015 4:13pm

This is what I mean to the best of my understanding.

If I include DisplayName in the select statement below it will be blank. Is it because the select statement is operating at the folder level, and the DisplayName field is at the mailbox level?

[PS]> get-mailbox | Get-MailboxFolderStatistics | where {$_.foldersize -gt 5Gb} | sort -desc foldersize | select displayname, identity, foldersize | ft -auto

DisplayName    Identity                                                                 FolderSize
---------------    --------                                                                 ----------
                      domain.local/MyBusiness/Users/Dave\Inbox             6.155 GB (6,608,825,034 bytes)
                      domain.local/MyBusiness/Users/John\Sent Items      5.112 GB (5,489,056,989 bytes)

How I understand your script is, the outside foreach loop is at the mailbox level for the store, and the $mbx statement is looping at the folder level inside each mailbox, am I understanding that right?

And I totally don't understand the select statement using the @, etc. (And I did search the web for how that breaks down and can't find an explanation, do you have a source for that?)

signed, a 30-year-plus IT guy who grew up using COBOL, who glazes over when someone mentions the phrase 'object oriented' in casual conversation, who brought Bruce Payette's book but never quite wrapped his head around it, who sees the great utility of PowerShell but ends up cutting and pasting someone else's code,

Rob

 

February 23rd, 2015 5:24pm

Hi Rob,

If I include DisplayName in the select statement below it will be blank. Is it because the select statement is operating at the folder level, and the DisplayName field is at the mailbox level?

That happens because of where you are in the pipeline. At that point, there is no DisplayName property for Select-Object to select. That's why we grab it earlier and save it as a variable.

How I understand your script is, the outside foreach loop is at the mailbox level for the store, and the $mbx statement is looping at the folder level inside each mailbox, am I understanding that right?

The first command gets all mailboxes and stores them in $mbxs. The foreach loop then processes each mailbox individually. Inside of the loop that particular mailbox (stored in $mbx) is piped into Get-MailboxFolderStatistics. The Select-Object later in the pipeline acts on the objects returned from earlier in the pipeline. That's why we need to save the display name property from the mailbox object if we want to include it in the output.

And I totally don't understand the select statement using the @, etc. (And I did search the web for how that breaks down and can't find an explanation, do you have a source for that?)

That's a calculated property:

https://technet.microsoft.com/en-us/library/ff730948.aspx

Everything make a bit more sense now?

Free Windows Admin Tool Kit Click here and download it now
February 24th, 2015 3:33pm

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

Other recent topics Other recent topics