Mailbox Quota Statistics (Network Steve Forum)

Mailbox Quota Statistics

Hi, I use following command to get mailbox quota for all users in exchange 2007. Get-MailboxStatistics | Sort-Object TotalItemSize -Descending | Select-Object DisplayName, @{expression={$_.TotalItemSize.Value.ToMB()}}, ItemCount, Database | Export-CSV -encoding "unicode" c:\temp\mailbox_size.csv However, I've Global users and this time i only want to get mailbox quota for Singapore users. Can some one help me to input parameter to extract "Office name" as well. Fyi, in every user property under office Tab user country is specified like for singapore "SG is specified under user properties in Office TAB". I've used following command, though i can see the Tab called "OfficeName" but no country inside. Get-MailboxStatistics | Sort-Object TotalItemSize -Descending | Select-Object DisplayName, OfficeName , @{expression={$_.TotalItemSize.Value.ToMB()}}, ItemCount, Database | Export-CSV -encoding "unicode" c:\temp\mailbox_size.csv Ps. I don't want to extract only users has SG in Office TAB, I want all user mailbox size. But I want Office details (Like SG, USA etc) to be in CSV file so I can Sort it out. Help in this regard much appreciated.Nauman Ansari ITIL, MCSE (2K & 2K3), MCSA: Windows & Messaging (2K & 2K3) | http://mdnaumanansari.spaces.live.com/

August 31st, 2009 11:26am

Check this... Get-Mailbox -ResultSize Unlimited | Get-MailboxStatistics | Sort-Object TotalItemSize -Descending | Select-Object DisplayName,Office, @{expression={$_.TotalItemSize.Value.ToMB()}}, ItemCount, Database | Export-CSV -encoding "unicode" c:\temp\mailbox_size.csvAmit Tank | MVP Exchange Server | MCITP: EMA | MCSA: M | http://ExchangeShare.WordPress.com

Free Windows Admin Tool Kit Click here and download it now

August 31st, 2009 11:37am

Thanks Amit for reply. I got same result please find it below. I can't attach file (i don't know how to attach) in the forum else i wanted to share with you my user properties in AD/Exchange that Office Tab is set to "SG/SIN/IBP". DisplayName Office $_.TotalItemSize.Value.ToMB() ItemCount Muhammad Nauman Ansari 0 12Nauman Ansari ITIL, MCSE (2K & 2K3), MCSA: Windows & Messaging (2K & 2K3) | http://mdnaumanansari.spaces.live.com/

September 1st, 2009 5:57am

Yep, you are correct. This should work, I just tested it... Get-Mailbox -ResultSize Unlimited | Get-MailboxStatistics | Sort-Object TotalItemSize -Descending | Select-Object DisplayName,@{Name="Office Name";expression={(Get-Mailbox $_).office}}, @{expression={$_.TotalItemSize.Value.ToMB()}}, ItemCount, Database | Export-CSV -encoding "unicode" c:\temp\mailbox_size.csvAmit Tank | MVP Exchange Server | MCITP: EMA | MCSA: M | http://ExchangeShare.WordPress.com

Free Windows Admin Tool Kit Click here and download it now

September 1st, 2009 6:17am

Waoooooooooooo!, Thanks alot Amit. It worked finally. but actually i've not used "Get-Mailbox -ResultSize Unlimited", only start from "Get-MailboxStatistics | Sort-Object TotalItemSize -Descending | Select-Object DisplayName,@{Name="Office Name";expression={(Get-Mailbox $_).office}} , @{expression={$_.TotalItemSize.Value.ToMB()}}, ItemCount, Database | Export-CSV -encoding "unicode" c:\temp\mailbox_size.csv" and i got office details. Thank you so much!Nauman Ansari ITIL, MCSE (2K & 2K3), MCSA: Windows & Messaging (2K & 2K3) | http://mdnaumanansari.spaces.live.com/

September 1st, 2009 12:50pm

Hi, I got one more requirement to sort user's with "Office - SG" & "Company - ABC Cell" details, appreciate the command. the command (below) earlier given by Amit which extracted the user with "Office" details, similarly i want now to extract user with "office & company" details.  Get-MailboxStatistics | Sort-Object TotalItemSize -Descending | Select-Object DisplayName,@{Name="Office Name";expression={(Get-Mailbox $_).office}} , @{expression={$_.TotalItemSize.Value.ToMB()}}, ItemCount, Database | Export-CSV -encoding "unicode" c:\temp\mailbox_size.csv. Is using following command will give the result, I can't test in my test environment as it's down and will not be up soon. Appreciate if someone can test or provide the correct parameter.  Get-MailboxStatistics | Sort-Object TotalItemSize -Descending | Select-Object DisplayName,@{Name="Office Name";expression={(Get-Mailbox $_).office}} , @{Name="Company Name";expression={(Get-Mailbox $_).Company}} , @{expression={$_.TotalItemSize.Value.ToMB()}}, ItemCount, Database | Export-CSV -encoding "unicode" c:\temp\mailbox_size.csv Thanks! Nauman Ansari ITIL, MCSE (2K & 2K3), MCSA: Windows & Messaging (2K & 2K3) | http://mdnaumanansari.spaces.live.com/

Free Windows Admin Tool Kit Click here and download it now

September 30th, 2009 12:36pm

Company field can not befetchedwith Get-Mailbox, instead use Get-User and it should work... ;) @{Name="Company Name";expression={(Get-User $_).Company}} ,Amit Tank | MVP Exchange Server | MCITP: EMA | MCSA: M | http://ExchangeShare.WordPress.com

September 30th, 2009 1:09pm

The command returned with error "Select-Object : A parameter cannot be found that matches parameter name 'Microsoft.Exchange.Data.Mapi.MailboxStatistics'." I think MS exchange don't recognize Get-Mailbox and Get-user with parameter together with Get-MailboxStatistics. However, I export the details through query on Active Directory where i search users with "Office & Company" details. But in case if we can fetch the users through exchange shell would be very useful. Fyi, The command i used was: Get-MailboxStatistics | Sort-Object TotalItemSize -Descending | Select-Object DisplayName, @{Name="Office Name";expression={(Get-Mailbox $_).office}} , @{Name="Company Name";expression={(Get-User $_).Company}} , @{expression={$_.TotalItemSize.Value.ToMB()}}, ItemCount, Database | Export-CSV -encoding "unicode" c:\temp\mailbox_size.csvNauman Ansari ITIL, MCSE (2K & 2K3), MCSA: Windows & Messaging (2K & 2K3) | http://mdnaumanansari.spaces.live.com/

Free Windows Admin Tool Kit Click here and download it now

October 1st, 2009 7:32am

can someone please correct the following command, i've requirement to get mailbox statistics extract with Office and company details so after export it can be filter by Office or company. Get-MailboxStatistics | Sort-Object TotalItemSize -Descending | Select-Object DisplayName,@{Name="Office Name";expression={(Get-Mailbox $_).office}}, @{Name="Company Name";expression={(Get-User $_).Company}} , @{expression={$_.TotalItemSize.Value.ToMB()}}, ItemCount, Database | Export-CSV -encoding "unicode" c:\temp\mailboxsize.csvNauman Ansari ITIL, MCSE (2K & 2K3), MCSA: Windows & Messaging (2K & 2K3) | http://mdnaumanansari.spaces.live.com/

December 4th, 2009 7:20am

On Fri, 4-Dec-09 04:20:55 GMT, Nauman Ansari wrote:" c:\temp\mailboxsize.csv>Nauman Ansari ITIL, MCSE (2K & 2K3), MCSA: Windows & Messaging (2K & 2K3) | http://mdnaumanansari.spaces.live.com/ Get-MailboxStatistics | Sort-Object TotalItemSize -Descending |Select-Object DisplayName, @{Name="Office Name";expression={(Get-User$_.DisplayName).office}}, @{Name="Company Name";expression={(Get-User$_.DisplayName).Company}},@{Name='TotalItemSize';expression={$_.TotalItemSize.Value.ToMB()}},ItemCount, Database | Export-CSV -encoding "unicode"c:\temp\mailboxsize.csv---Rich MatheisenMCSE+I, Exchange MVP--- Rich Matheisen MCSE+I, Exchange MVP

Free Windows Admin Tool Kit Click here and download it now

December 5th, 2009 9:50pm

Ansari Nauman:Get-MailboxStatistics | Sort-Object TotalItemSize -Descending | Select-Object DisplayName,@{Name="Office Name";expression={(Get-Mailbox $_).office}}, @{Name="Company Name";expression={(Get-User $_).Company}} , @{expression={$_.TotalItemSize.Value.ToMB()}}, ItemCount, Database | Export-CSV -encoding "unicode" c:\temp\mailboxsize.csv You cannot do it that way. Select-Object is for fast one-liners, not code that combines / joins several tables. This should work: $mbxStats = @() Get-Mailbox -ResultSize Unlimited | ForEach-Object { $stats = Get-MailboxStatistics -id $_ $mbx = New-Object System.Object $mbx | Add-Member -MemberType NoteProperty -Value $stats.Displayname -Name 'Display Name' $mbx | Add-Member -MemberType NoteProperty -Value $(Get-User -id $_).Company -Name Comapny $mbx | Add-Member -MemberType NoteProperty -Value $(Get-User -id $_).Office -Name Office $mbx | Add-Member -MemberType NoteProperty -Value $stats.ItemCount -Name 'Item Count' $mbx | Add-Member -MemberType NoteProperty -Value $stats.TotalItemSize.value.ToMB() -Name 'Total Size' $mbx | Add-Member -MemberType NoteProperty -Value $stats.Database -Name Database $mbxStats += $mbx } $mbxStats | Sort-Object TotalSize -Descending | Export-Csv -noTypeInformation -Encoding 'Unicode' -Path c:\temp\test_stat.csv See also: Select is bad, Add-Member is good http://dmitrysotnikov.wordpress.com/2008/08/27/select-object-vs-add-member/ MCTS: Messaging | MCSE: S+M | Small Business Specialist

December 6th, 2009 4:53pm

On Sun, 6-Dec-09 13:53:27 GMT, Jon-Alfred Smith wrote:>Ansari Nauman:Get-MailboxStatistics | Sort-Object TotalItemSize -Descending | Select-Object DisplayName, @{Name="Office Name";expression={(Get-Mailbox $_).office}}, @{Name="Company Name";expression={(Get-User $_).Company}} , @{expression={$_.TotalItemSize.Value.ToMB()}}, ItemCount, Database | Export-CSV -encoding "unicode" c:\temp\mailboxsize.csv >>You cannot do it that way. He sure can! :-)>Select-Object is for fast one-linersThat's pretty much what he seems to want.ot code that combines / joins several tables.If you're writing something that will require maintenance, or youexpect someone else to undestand what you've done (or you expectyourself to remember why you did something one way versus another waysix months after you wrote it), then "one-liners" are NOT the way tocreate the code. Comments, meaningful ones, liberally sprinkledthroughout the code are imperative. Descriptions of the expectedinput, output, format, parameters, etc. may seem obvious while you'rewriting the code but they're life-savers later.>This should work: It will, but at the expense of lots of extra typing (and typos).The use of the ForEach-Object also limits the number of objects in thepipeline to one, reducing the concurrency of the other cmdlets. Ifyou're going to the trouble of writing the code, why not write themeat of it in a filter and pipe the objects through it and put yourown object into the pipeline as output. It's a little more work but are faster and the code's reusable. :-)---Rich MatheisenMCSE+I, Exchange MVP--- Rich Matheisen MCSE+I, Exchange MVP

Free Windows Admin Tool Kit Click here and download it now

December 6th, 2009 7:33pm

$mbx | Add-Member -MemberType NoteProperty -Value $stats.TotalItemSize.value.ToMB() -Name 'Total Size' This line returns an error and aborts the script if there is no data to return for the specified mailbox, because it has not been logged on to: "You cannot call a method on a null-valued expression!" Work-around is to drop the ToMB() method.Sorry for lack of comments. Was and am still in a rush. Comments and a better solution to empty mailboxes will come later this night, also a discussion on why I think you can't do this with Select-Object and have to use Add-Member and a dynamic array instead (I am very eager to learn if I'm wrong). MCTS: Messaging | MCSE: S+M | Small Business Specialist

December 6th, 2009 9:54pm

On Sun, 6-Dec-09 18:54:58 GMT, Jon-Alfred Smith wrote: on why I think you can't do this with Select-Object and have to use Add-Member and a dynamic array instead (I am very eager to learn if I'm wrong).Why not look at my reply from 12/5?To fix your problem I think all you have to do is wrap your customobject creation in a conditional "if ($stats -isnot [object]{your-code-here }". No mailbox, no stats. If that doesn't work, thenthis might work:$size = 0if ($stats.TotalItemSize -is [object]){ $size = $stats.TotalItemSize.ToMB()}Add-Member -MemberType NoteProperty -Value $ize -Name 'Total Size'Or just add "-ea silentlycontinue" to the problematic Add-Member?The problem with most code written by non-programmers (me included) isthe assumption that it'll never fail. :-)---Rich MatheisenMCSE+I, Exchange MVP--- Rich Matheisen MCSE+I, Exchange MVP

Free Windows Admin Tool Kit Click here and download it now

December 7th, 2009 12:38am

The code in your reply from 12/5 does not work. -ErrorAction SilentlyContinue does no good with this kind of exceptions. A conditional statement could be used, a bit different though; I opted, however, forfirst trying wrapping trap .. continue around, which does not do its job.try .. catch would have been even better, but this is v1.0. http://blogs.msdn.com/powershell/archive/2009/06/17/traps-vs-try-catch.aspx MCTS: Messaging | MCSE: S+M | Small Business Specialist

December 7th, 2009 6:19am

On Mon, 7-Dec-09 03:19:20 GMT, Jon-Alfred Smith wrote:>The code in your reply from 12/5 does not work. And I can see why! The software I'm using to read/post in the forum isclassified as "beta", but I think it's probably closer to "alpha". Itlikes to arbitrarily remove white space, drop out bits of text hereand there, and generally make things look like I'm learning how towrite in English and how to type at the same time.Let's try it again:Get-MailboxStatistics | Sort-Object TotalItemSize -DescendingName, @{Name="Office Name";expression={(Get-User$_.DisplayName).office}}, @{Name="Company Name";expression={(Get-User$_.DisplayName).Company}},@{Name='TotalItemSize';expression={$_.TotalItemSize.Value.ToMB()}},ItemCount,Database | Export-CSV -encoding "unicode" -notypeinfo -pathc:\temp\mailboxsize.csvThe code is just a touch-up of what the original poster entered -- Ididn't try to make it "better", just to make it work. As the OP postedit, it works only if you run it on an Exchange mailbox server. The CSVfile is in unicode which Excel seems to treat as if it's a plain textfile. Other than that, adding "get-exchangeserver servername |" at thebeginning of the code gets it to run from a non-Exchange server.I ran it on a machine with about 4000 mailboxes and it produces theexpected results. Changing "unicode" to "ascii" even makes itacceptable to Excel.---Rich MatheisenMCSE+I, Exchange MVP--- Rich Matheisen MCSE+I, Exchange MVP

Free Windows Admin Tool Kit Click here and download it now

December 7th, 2009 7:36am

Get-MailboxStatistics | Sort-Object TotalItemSize -DescendingName, @{Name="Office Name";expression={(Get-User$_.DisplayName).office}}, @{Name="Company Name";expression={(Get-User$_.DisplayName).Company}},@{Name='TotalItemSize';expression={$_.TotalItemSize.Value.ToMB()}},ItemCount,Database | Export-CSV -encoding "unicode" -notypeinfo -pathc:\temp\mailboxsize.csv As posted above:A parameter cannot be found that matches parameter name 'System.Object[]'.At :line:1 char:36+ Get-MailboxStatistics | Sort-Object <<<< TotalItemSize -Descending Name, @{Name="Office Name";expression={(Get-User $_.DisplayName).office}}, @{Name="Company Name";expression={(Get-User $_.DisplayName).Company}},@{Name='TotalItemSize';expression={$_.TotalItemSize.Value.ToMB()}},ItemCount, Correctedwith code from5/12The operation could not be performed because object 'Microsoft System Attendant' could not be found on domain controller 'xxx.smith.xxx'.At :line:1 char:204This is a SBS 2008 box. Perhaps you could use the web interface. Unicode works fine with Excel if you import the file. Norwegian characters get lost with ACSII.MCTS: Messaging | MCSE: S+M | Small Business Specialist

December 7th, 2009 8:09am

The commented version: # Initialize a dynamic array (a two-dimensional table). This table will hold these columns: # Display Name, Company, Office, Item Count, Total Size, Database # The rows will consist of all mailboxes in the domain. # Display Name, Item Count, Total Size, Database will be retrieved with Get-MailboxStatistics # Company, Office will be retrieved with Get-User $mbxStats = @() # Get all mailboxes in the domain Get-Mailbox -ResultSize Unlimited | # Loop through every mailbox. The $_ is a system variable holding the identity of each mailbox ForEach-Object { # Get statistics for each mailbox user. $stats will hold all values retrieved with the cmdlet, # such as DisplayName ($stats.DisplayName), ItemCount ($stats.ItemCount), etc. $stats = Get-MailboxStatistics -id $_ # Create a new object of type .NET $mbx = New-Object System.Object # Add these properties to the system object. These six properties will make up a row # Equivalent to Get-MailboxStatistics -id <user mailbox> | Select-Object DisplayName $mbx | Add-Member -MemberType NoteProperty -Value $stats.DisplayName -Name 'Display Name' # Equivalent to Get-User -id <user mailbox> | Select-Object Company $mbx | Add-Member -MemberType NoteProperty -Value $(Get-User -id $_).Company -Name Company $mbx | Add-Member -MemberType NoteProperty -Value $(Get-User -id $_).Office -Name Office $mbx | Add-Member -MemberType NoteProperty -Value $stats.ItemCount -Name 'Item Count' # Mailbox might not be logged into and $stats.ItemCount and $stats.TotalItemSize.Value# will return $null. The method To.MB() will fail and abort the script if ($stats.ItemCount -ne $null) { $mbx | Add-Member -MemberType NoteProperty -Value $stats.TotalItemSize.Value.ToMB() -Name 'Total Size' } else { $mbx | Add-Member -MemberType NoteProperty -Value 0 -Name 'Total Size' } $mbx | Add-Member -MemberType NoteProperty -Value $stats.Database -Name Database # Add right hand operand to value of variable ($mbx) and place result in variable ($mbxStats) # In short: add the row to the two-dimensional array (table) $mbxStats += $mbx } # End of Loop # Pipe the array to the Export-Csv cmdlet. Drop type information. Default is ASCII $mbxStats | Export-Csv -noTypeInformation -Encoding Unicode -Path c:\temp\test_stat.csv Error handling is rudimentary, bur works in our production environment with about 1900 mailboxes MCTS: Messaging | MCSE: S+M | Small Business Specialist

Free Windows Admin Tool Kit Click here and download it now

December 7th, 2009 8:49am

On Mon, 7-Dec-09 05:09:55 GMT, Jon-Alfred Smith wrote:>Get-MailboxStatistics | Sort-Object TotalItemSize -DescendingName, @{Name="Office Name";expression={(Get-User$_.DisplayName).office}}, @{Name="Company Name";expression={(Get-User$_.DisplayName).Company}},@{Name='TotalItemSize';expression={$_.TotalItemSize.Value.ToMB()}},ItemCount,Database | Export-CSV -encoding "unicode" -notypeinfo -pathc:\temp\mailboxsize.csv>istics | Sort-Object <<<< TotalItemSize -Descending Name, @{Name="Office Name";expression={(Get-User $_.DisplayName).office}}, @{Name="Company Name";expression={(Get-User $_.DisplayName).Company}},@{Name='TotalItemSize';expression={$_.TotalItemSize.Value.ToMB()}},ItemCount, I'm about ready to give up on testing other people's software.:-(What you see isn't what I posted (again).I doubt posting it again will help, but maybe if I put spaces beforeand after the pipes and commas it won't throw up (again).Get-MailboxStatistics | Sort-Object TotalItemSize -Descending |Select-Object DisplayName, @{Name="Office Name";expression={(Get-User $_.DisplayName).office}},@{Name="Company Name";expression={(Get-User $_.DisplayName).Company}},@{Name='TotalItemSize';expression={$_.TotalItemSize.Value.ToMB()}},ItemCount, Database | Export-Csv -Path c:\temp\mailboxsize.csv-encoding unicodeNote that there SHOULD be a space between the 'Get-User' and the '$_'that follows.with code from 5/12The operation could not be performed because object 'Microsoft System Attendant' could not be found on domain controller 'xxx.smith.xxx'.At :line:1 char:204Interesting point about the system attendant mailbox. Try substituting'legacyExchangeDN' where I've used 'DisplayName' in the two 'Get-User'cmdlets. If the mailbox is there it should have a corresponding ADobject.>This is a SBS 2008 box. I don't thnk that matters.>Perhaps you could use the web interface. The problem with "forums" as opposed to newsgroups is that you have tovisit individual web sites if you deal with multiple topics (i.e.vendors). The "tried and true" NNTP and various newsreaders let me putall of what I do in one place.>Unicode works fine with Excel if you import the file. Yeah, but it doesn't if you just double-click the file. :-(>Norwegian characters get lost with ACSII.Having worked for multi-national companies for more than 30 years, alle. :-) ---Rich MatheisenMCSE+I, Exchange MVP--- Rich Matheisen MCSE+I, Exchange MVP

December 7th, 2009 5:07pm

On Mon, 7-Dec-09 05:09:55 GMT, Jon-Alfred Smith wrote: [ snip ]>Corrected with code from 5/12The operation could not be performed because object 'Microsoft System Attendant' could not be found on domain controller 'xxx.smith.xxx'.At :line:1 char:204The object in the AD for the System Attendant mailbox is found in theConfiguration naming context of the AD. Is it possible that your userdoesn't have permission to read information from that part of the AD??You should be able to verify that the AD object exists by descendingto the Configuration container in the AD:Services Microsoft Exchange Administrative Groups Servers Microsoft System Attendant---Rich MatheisenMCSE+I, Exchange MVP--- Rich Matheisen MCSE+I, Exchange MVP

Free Windows Admin Tool Kit Click here and download it now

December 7th, 2009 6:39pm

Sorry for the delay. I'll study the script tomorrow, test it in our production environment as well. I'm very interested in being wrong in my opinion about Select-Object, at least get a correction. My three guidelines have mainly been:* Own scripts with a lot of trial and error (Selectruns much faster than Add-Member, but Add-Member is much clearer in the syntax as soon as you understand it, and easy extensible and easy to troubleshoot ...)* Select is bad, Add-Member is goodhttp://dmitrysotnikov.wordpress.com/2008/08/27/select-object-vs-add-member/ * Microcode: PowerShell Scripting Tricks: Select-Object (Note Properties) vs Add-Member (Script Properties)http://blogs.msdn.com/mediaandmicrocode/archive/2008/11/26/microcode-powershell-scripting-tricks-select-object-note-properties-vs-add-member-script-properties.aspxP.S. As to news groups and news readers: yes .. this web-stuff is the closest I come to my wife's indulgence with Facebook, but the strange thing: I've come to like it. As to Unicode, I could quote 1 1/2 pages from one of my favorite books (still): Helen Custer, Inside Windows NT ... perhaps Excel 2010? MCTS: Messaging | MCSE: S+M | Small Business Specialist

December 8th, 2009 3:00am

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