How can we run a report of Exchange 2013 mailbox usage and quota?

We are running Exchange 2013.  We need to run a regular report that lists the following information:

Display Name
Mailbox size in MB
Deleted items in MB
Warning Quota in MB
Prohibit Send Quota in MB

I have experimented with various Exchange shell commands but cannot find one command that lists all this information; I have to run two and combine the results later.  Ideally the results would look something like this (in .txt or .csv format):

DisplayName     TotalItemSizeinMB TotalDeletedItemSize(MB) IssueWarningQuota ProhibitSendQuota                          
-----------     ----------------- ------------------------ ----------------- -----------------
Joe Bloggs                   6984                    59.39              9728             10240
Jane Doe                      159                     6.03         Unlimited         Unlimited

Open in new window

What command should I run to export this information to a file?  Thanks in advance!
LVL 2
David HaycoxAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Justin YeungSenior Systems EngineerCommented:
Get-Mailbox "a user"| Get-MailboxStatistics | Select DisplayName,TotalItemSize,TotalDeletedItemSize,@{N="IssueWarningQuota";E={(Get-Mailbox $_).IssueWarningQuota}},@{N="ProhibitSendQuota";E={(Get-Mailbox $_).prohibitsendquota}}
0
tolinromeCommented:
I used to use this, and it puts it in an excel spreadsheet for you.

Get-mailbox |Get-MailboxStatistics| select DisplayName,Database,TotalItemSize,ItemCount,storagelimitstatus | export-csv C:\mailboxsizes.csv -NoTypeInformation
0
Satish AutiSenior System AdministratorCommented:
that one is only for single user.

if you want it for entire server then use below command.

get-mailbox -server <server> -ResultSize unlimited |
 Where {$_.UseDatabaseQuotaDefaults -eq $false} |
   ft DisplayName,IssueWarningQuota,ProhibitSendQuota,@{label="TotalItemSize(MB)";expression={(get-mailboxstatistics $_).TotalItemSize.Value.ToMB()}}

And if you want to export it file then use below

get-mailbox -server <server> -ResultSize unlimited |
 Where {$_.UseDatabaseQuotaDefaults -eq $false} |
   ft DisplayName,IssueWarningQuota,ProhibitSendQuota,@{label="TotalItemSize(MB)";expression={(get-mailboxstatistics $_).TotalItemSize.Value.ToMB()}} > C:\file name.csv
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

David HaycoxAuthor Commented:
Justin Yeung: if I remove the "a user" from the command, this gives the right information, but not in the table format we want.

tolinrome: your command gives output in the required tabular format but doesn't display anything for quota (the "StorageLimitStatus" column is blank.

Satish Auti: if I remove the "-server" section (or specify it), this gives the correct information in columns, but only for users who are not set to use the database quota defaults.  If I miss out the "where" section though, this will display all mailboxes.

So we're nearly there - but can the output be formatted to use the same unit (MB or GB) for all columns, to make it easier to read?  For example, currently we are getting this:
DisplayName                   IssueWarningQuota             ProhibitSendQuota             TotalItemSize(MB)
-----------                   -----------------             -----------------             -----------------
Joe Bloggs                    9.5 GB (10,200,547,328 bytes) 10 GB (10,737,418,240 bytes)  7013
Jane Doe                      Unlimited                     Unlimited                     181

Open in new window

But would prefer something like this:
DisplayName                   IssueWarningQuota(MB)         ProhibitSendQuota(MB)         TotalItemSize(MB)
-----------                   ---------------------         ---------------------         -----------------
Joe Bloggs                    9728                          10240			  7013
Jane Doe                      Unlimited                     Unlimited                     181

Open in new window

0
Justin YeungSenior Systems EngineerCommented:
Get-Mailbox | Get-MailboxStatistics | ft DisplayName,@{N="Total Item Size (MB)";E={$_.TotalItemSize.value.tomb()}},@{N="Total Deleted Item Size (MB)";E={$_.TotalDeletedItemSize.value.tomb()}},@{N="IssueWarningQuota";E={(Get-Mailbox $_).IssueWarningQuota}},@{N="ProhibitSendQuota";E={(Get-Mailbox $_).prohibitsendquota}} 

Open in new window

0
David HaycoxAuthor Commented:
That gives us the total deleted item size in MB, but the quota columns are listed in GB and bytes, like so:
DisplayName                Total Item Size (MB)     Total Deleted Item  IssueWarningQuota       ProhibitSendQuota
                                                              Size (MB)
-----------                -------------------- ----------------------- -----------------       -----------------
Joe Bloggs                                 7006                      23 9.5 GB (10,200,547,3... 10 GB (10,737,418,24...
Jane Doe                                    182                       7 Unlimited               Unlimited

Open in new window

I know it's easy enough to compare e.g. 7006MB with 10GB, but if it's possible to get all figures in either MB or GB (or bytes, for that matter) then that would be the ideal solution for us.  Thanks!
0
Justin YeungSenior Systems EngineerCommented:
Try this.

Get-Mailbox | Get-MailboxStatistics | ft DisplayName,@{N="Total Item Size (MB)";E={$_.TotalItemSize.value.tomb()}},@{N="Total Deleted Item Size (MB)";E={$_.TotalDeletedItemSize.value.tomb()}},@{N="IssueWarningQuota (MB)";E={(Get-Mailbox $_).IssueWarningQuota.Value.toMB()}},@{N="ProhibitSendQuota (MB)";E={(Get-Mailbox $_).prohibitsendquota.Value.ToMB()}} 

Open in new window

0
David HaycoxAuthor Commented:
That's pretty much spot on, thanks!  Finally, is it possible to export this into CSV for easy import into Excel?
0
Justin YeungSenior Systems EngineerCommented:
if you want to export it, you will need to change "ft" to select
Get-Mailbox | Get-MailboxStatistics | Select DisplayName,@{N="Total Item Size (MB)";E={$_.TotalItemSize.value.tomb()}},@{N="Total Deleted Item Size (MB)";E={$_.TotalDeletedItemSize.value.tomb()}},@{N="IssueWarningQuota (MB)";E={(Get-Mailbox $_).IssueWarningQuota.Value.toMB()}},@{N="ProhibitSendQuota (MB)";E={(Get-Mailbox $_).prohibitsendquota.Value.ToMB()}} | export-csv "c:\export\exchangecsv.csv" -NoTypeInformation

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David HaycoxAuthor Commented:
Perfect!  Many thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Exchange

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.