Share via

Get Mailbox Statistics and Do Calculations to provide % of Used vs Available Space Exchange 2013

Question

Wednesday, August 3, 2016 8:21 AM

So i have this:

get-mailbox -server EXCH1 -ResultSize unlimited | Where {$_.UseDatabaseQuotaDefaults -eq $false
} | ft DisplayName,IssueWarningQuota,ProhibitSendQuota,@{label="TotalItemSize(MB)";expression={(get-mailboxstatistics $_
).TotalItemSize.Value.ToMB()}},@{label="IssueWarningQuota Value in MB";expression={$_.IssueWarningQuota.Value.toMB()}},@{label="Percentage Used(%)";expression={($_.ProhibitSendQuota / $_.TotalItemSize)}}

Which returns :

DisplayName         IssueWarningQuota   ProhibitSendQuota     TotalItemSize(MB) IssueWarningQuota   Percentage Used(%)
                                                                                Value in MB
             
Despina Panayiot... Unlimited           Unlimited                         55597
Anna-Frida Anast... 8.856 GB (9,509,... 9.907 GB (10,637...                6108 9068
Kalina Kaluska      8 GB (8,589,934,... 10 GB (10,737,41...                5891 8192
Eliza Georgiou      5.902 GB (6,337,... 6.954 GB (7,466,...                6542 6044
Manos Karaiskakis   4.702 GB (5,049,... 4.8 GB (5,153,95...                1121 4815
Dimitris Toskas     5 GB (5,368,709,... 6 GB (6,442,450,...                4501 5120
Theocharis Mylonas  10.94 GB (11,746... 11.71 GB (12,573...                2680 11202
Andreas Christof... 4.7 GB (5,046,58... 4.8 GB (5,153,96...                 446 4812

Eberything is ok no errors or anything . It just wont do the last part of the division in label Percentage Used . Its always empty.

Can you help ?

All replies (20)

Tuesday, August 9, 2016 2:01 PM âś…Answered

Hi Kleanthis,

wellllll ... as I mentioned, you need to combine information from the mailbox object for each mailbox statistics.

But alright, you went to all the trouble, guess I can show you how this can be done. I added quite a few other improvements, so give it some thought and you might learn a few other things as well:

$DatabaseList = "Mailbox Database 0993595259"

$date = get-date -Format MM-dd-yyy
$folderpath = "C:\Scripts\"
$outputfile = $folderpath + "UserQuotas_" + $Date + ".csv"

Function RunCommands
{
    [CmdletBinding()]
    Param (
        [string]
        $Output,
        
        $DatabaseList
    )
    
    $Mailboxes = Get-Mailbox
    ProcessMailboxes -Databases $DatabaseList | Process-UserInfo -Mailboxes $Mailboxes | Select-Object DisplayName, Database, TotalItemSize, "Total Size (MB)", SendQuota, "Send Quota (MB)", ItemCount, LastLogonTime, StorageLimitStatus, TotalDeletedItemSize | Export-Csv -Path $Output
}

Function ProcessMailboxes
{
    [CmdletBinding()]
    Param (
        $Databases
    )
    
    ForEach ($database in $Databases)
    {
        Get-MailboxStatistics -Database $Database | Sort-Object -Property TotalItemSize -Descending
    }
}

Function Process-UserInfo
{
    [CmdletBinding()]
    Param (
        [Parameter(ValueFromPipeline = $true)]
        $Statistics,
        
        $Mailboxes
    )
    
    Begin
    {
        
    }
    Process
    {
        foreach ($User in $Statistics)
        {
            $mb = $Mailboxes | Where-Object { $_.ExchangeGuid -eq $User.MailboxGuid } | Select-Object -First 1
            
            $strTotalItemSize = "{0:N2}MB" -f ($User.TotalItemSize.Value.ToBytes() / 1mb)
            $decTotalItemSize = $User.TotalItemSize.Value.ToBytes()
            $WarningQuota = $mb.IssueWarningQuota
            if (($WarningQuota -like "unlimited") -or ($WarningQuota -eq $null))
            {
                $strWarningQuota = "-1"
                $decWarningQuota = -1
            }
            else
            {
                $strWarningQuota = "{0:N2}MB" -f ($WarningQuota.Value.ToBytes() / 1mb)
                $decWarningQuota = $WarningQuota.Value.ToBytes()
            }
            $SendQuota = $mb.ProhibitSendQuota
            if (($SendQuota -like "unlimited") -or ($SendQuota -eq $null))
            {
                $strSendQuota = "-1"
                $decSendQuota = -1
            }
            else
            {
                $strSendQuota = "{0:N2}MB" -f ($SendQuota.Value.ToBytes() / 1mb)
                $decSendQuota = $SendQuota.Value.ToBytes()
            }
            
            New-Object -TypeName psobject -Property @{
                DisplayName = $User.DisplayName
                Database = $User.Database
                TotalItemSize = $decTotalItemSize
                "Total Size (MB)" = $strTotalItemSize
                SendQuota = $decSendQuota
                "Send Quota (MB)" = $strSendQuota
                ItemCount = $User.ItemCount
                LastLogonTime = $User.LastLogonTime
                StorageLimitStatus = $User.StorageLimitStatus
                TotalDeletedItemSize = $user.TotalDeletedItemSize
            }
        }
    }
    End
    {
        
    }
}
RunCommands -DatabaseList $DatabaseList -Output $outputfile
Send-MailMessage -SmtpServer EXCH1.domain.local -To it@balbala.com -From MailBoxesReport@blabla.com -Subject "Daily Mailbox Status Report [EXCH1]" -Body "Please Find Attached The Mailboxes Status Report" -attachment $outputfile
Remove-Item $outputfile

Cheers,
Fred

There's no place like 127.0.0.1

Wednesday, August 3, 2016 8:32 AM

Hi Kleanthis,

this is due to them trying to access Get-MailboxStatistics-Properties which simply aren't available there.

Two options:

  • Combine the data before formatting it (more code)
  • Retrieve the same statistics 3 times (slower execution)

Cheers,
Fred

There's no place like 127.0.0.1

Wednesday, August 3, 2016 8:54 AM

Can you show me examples based on my code?

Wednesday, August 3, 2016 9:09 AM

Hi Kleanthis,

sure, here's a simple example (Note: This is not good code, but for demonstration purposes):

get-mailbox -server EXCH1 -ResultSize unlimited | Where-Object { $_.UseDatabaseQuotaDefaults -eq $false } | Format-Table DisplayName, IssueWarningQuota, ProhibitSendQuota, @{ label = "TotalItemSize(MB)"; expression = { (get-mailboxstatistics $_).TotalItemSize.Value.ToMB() }}, @{ label = "IssueWarningQuota Value in MB"; expression = { (get-mailboxstatistics $_).IssueWarningQuota.Value.toMB() } }, @{ label = "Percentage Used(%)"; expression = { ((get-mailboxstatistics $_).TotalItemSize) / (get-mailboxstatistics $_).ProhibitSendQuota * 100 } }

Let's make it a bit better, by exporting it as a csv table you can now open in Excel:

get-mailbox -server EXCH1 -ResultSize unlimited | Where-Object { $_.UseDatabaseQuotaDefaults -eq $false } | Select-object DisplayName, IssueWarningQuota, ProhibitSendQuota, @{ label = "TotalItemSize(MB)"; expression = { (get-mailboxstatistics $_).TotalItemSize.Value.ToMB() }}, @{ label = "IssueWarningQuota Value in MB"; expression = { (get-mailboxstatistics $_).IssueWarningQuota.Value.toMB() } }, @{ label = "Percentage Used(%)"; expression = { ((get-mailboxstatistics $_).TotalItemSize) / (get-mailboxstatistics $_).ProhibitSendQuota * 100 } } | Export-csv export.csv

But in both cases you are flooding your Exchange Server with connections. Depending on throttling, this may well fail. Furthermore, even if it doesn't error out, the performance will suck horribly (pardon the language).

Also, it's not resilient - any mailbox without a limit will break this.

To make this work reliably, you need to keep connections in mind and process the results before displaying / returning them.

For the second (and recommended) way, here's an example:

$Mailboxes = get-mailbox -server EXCH1 -ResultSize unlimited | Where-Object { $_.UseDatabaseQuotaDefaults -eq $false }
$Statistics = $Mailboxes | Get-MailboxStatistics

$Mailboxes | ForEach-Object {
    # Find matching statistics
    # Gather data
    # Build and return object
} | Export-Csv export.csv

Notice the comments? Well, this is where your own homework starts: Try figuring out ...

  • how to find the correct statistics object
  • How to combine data from two objects into a single one

Cheers,
Fred

There's no place like 127.0.0.1

Wednesday, August 3, 2016 9:50 AM

First two example you given return empty labels as i mentioned above:

DisplayName         IssueWarningQuota   ProhibitSendQuota     TotalItemSize(MB) IssueWarningQuota   Percentage Used(%)
                                                                                Value in MB
             
Despina Panayiot... Unlimited           Unlimited                         55601
Anna-Frida Anast... 8.856 GB (9,509,... 9.907 GB (10,637...                6130
Kalina Kaluska      8 GB (8,589,934,... 10 GB (10,737,41...                5915
Eliza Georgiou      5.902 GB (6,337,... 6.954 GB (7,466,...                6565
Manos Karaiskakis   4.702 GB (5,049,... 4.8 GB (5,153,95...                1121
Dimitris Toskas     5 GB (5,368,709,... 6 GB (6,442,450,...                4504
Theocharis Mylonas  10.94 GB (11,746... 11.71 GB (12,573...                2686
Andreas Christof... 4.7 GB (5,046,58... 4.8 GB (5,153,96...                 446
Alexandros Poull... 5.5 GB (5,905,58... 5.8 GB (6,227,70...                4863
Eugenios Vardas     6 GB (6,442,450,... 7 GB (7,516,192,...                5880
Marina Kouloundi    5.949 GB (6,387,... 7 GB (7,516,192,...                5655

Wednesday, August 3, 2016 9:54 AM

That's odd, they really shouldn't.

Does it throw any errors?
Mind posting a screenshot of the console?

There's no place like 127.0.0.1

Wednesday, August 3, 2016 10:10 AM

Wednesday, August 3, 2016 12:00 PM

Damn, it really shouldn't be doing that and I can't see reason why it doesn't return a value. Looks like you really will have to go for option 2 (would have been the better option anyway).

Just for reference: When exporting to file with the second example two posts ago, does it export all information or are the same two columns empty again?

There's no place like 127.0.0.1

Wednesday, August 3, 2016 12:05 PM

Empty on csv too ..

Wednesday, August 3, 2016 12:11 PM

Hm ... one idea, just to verify it:

When you filter out the first entry (the one without limits), does it work for the other elements?

There's no place like 127.0.0.1

Wednesday, August 3, 2016 12:13 PM

how do i filter that?

Wednesday, August 3, 2016 12:16 PM

Modify your where-object:

# Old
Where-Object { $_.UseDatabaseQuotaDefaults -eq $false }

# New
Where-Object { ($_.UseDatabaseQuotaDefaults -eq $false) -and ($_.Alias -ne "Alias of first user") }

There's no place like 127.0.0.1

Wednesday, August 3, 2016 12:23 PM

Nope still empty ..

Wednesday, August 3, 2016 12:42 PM

Nope still empty ..

Alright, then I'm all out of ideas on how to make option one function properly, leaving you with option two...

There's no place like 127.0.0.1

Monday, August 8, 2016 12:54 PM

Still cant make it work . Anyone got any ideas?

Monday, August 8, 2016 1:07 PM

Hi Kleanthis,

have you tried doing option two? If you have, feel free to post your progress and show what you are having trouble with.

Cheers,
Fred

PS: Here's the framework for option two (as copied from my previous post):

$Mailboxes = get-mailbox -server EXCH1 -ResultSize unlimited | Where-Object { $_.UseDatabaseQuotaDefaults -eq $false }
$Statistics = $Mailboxes | Get-MailboxStatistics

$Mailboxes | ForEach-Object {
    # Find matching statistics
    # Gather data
    # Build and return object
} | Export-Csv export.csv

There's no place like 127.0.0.1

Tuesday, August 9, 2016 8:12 AM

Ok so i have managed to do this and it works fine :

 
$DatabaseList = "Mailbox Database 0993595259"
[hashtable]$User = @{}
 
$date = get-date -Format MM-dd-yyy
$folderpath = "C:\Scripts\"
$tempfile = $folderpath + "Userinfo.csv"
$outputfile = $folderpath + "UserQuotas_" + $Date + ".csv"
 
Function RunCommands {
    ProcessMailboxes
 
    }
 
 
 
 
Function ProcessMailboxes{
 
     ForEach ($database in $DatabaseList){
 
        Get-MailboxStatistics -Database $Database   | Sort -Property TotalItemSize -Descending | select Displayname,database,TotalItemSize,ItemCount,LastLogonTime,TotalDeletedItemSize,DatabaseIssueWarningQuota,DatabaseProhibitSendQuota | Export-CSV $tempfile -Append
    
        Process-UserInfo
     }
 
 
}
 
Function Process-UserInfo {
 
 
    Import-Csv $tempfile | ForEach-Object {
 
 
 
        $User.DisplayName = $_.DisplayName
        $User.Database = $_.Database
        $User.TotalItemSize = $_.TotalItemSize
       
        $User.ItemCount = $_.ItemCount
        $User.LastLogonTime = $_.LastLogonTime
        $User.TotalDeletedItemSize = $_.TotalDeletedItemSize
 
       
 
        $strTotalItemSize = "{0:N2}" -f ($User.TotalItemSize.SubString(($User.TotalItemSize.IndexOf("(") + 1),($User.TotalItemSize.IndexOf(" bytes") - ($User.TotalItemSize.IndexOf("(") + 1))).Replace(",","")/1024/1024)
        $decTotalItemSize = [decimal]$strTotalItemSize
        $WarningQuota = $_.DatabaseIssueWarningQuota
        $strWarningQuota = "{0:N2}" -f ($WarningQuota.SubString(($WarningQuota.IndexOf("(") + 1),($WarningQuota.IndexOf(" bytes") - ($WarningQuota.IndexOf("(") + 1))).Replace(",","")/1024/1024)
        $decWarningQuota = [decimal]$strWarningQuota
        $SendQuota = $_.DatabaseProhibitSendQuota
        $strSendQuota = "{0:N2}" -f ($SendQuota.SubString(($SendQuota.IndexOf("(") + 1),($SendQuota.IndexOf(" bytes") - ($SendQuota.IndexOf("(") + 1))).Replace(",","")/1024/1024)
        $decSendQuota = [decimal]$strSendQuota
 
 
 
 
        If (($decTotalItemSize -gt $decWarningQuota) -and ($decTotalItemSize -lt $decSendQuota)) {
            $User.StorageLimitStatus = "IssueWarning"
            }
 
        ElseIf ($decTotalItemSize -gt $decSendQuota) {
            $User.StorageLimitStatus = "OverQuota"
            }
       
       Else {
            $User.StorageLimitStatus = "BelowLimit"
            }
 
        New-Object -TypeName psobject -Property @{
            DisplayName = $User.DisplayName
            Database = $User.Database
            TotalItemSize = $decTotalItemSize
            SendQuota = $decSendQuota
            ItemCount = $User.ItemCount
            LastLogonTime = $User.LastLogonTime
            StorageLimitStatus = $User.StorageLimitStatus
            TotalDeletedItemSize = $user.TotalDeletedItemSize
            } | Select-Object DisplayName,Database,@{label="Total Size (MB)";expression={$decTotalItemSize}},@{label="Send Quota (MB)";expression={$decSendQuota}},ItemCount,LastLogonTime,StorageLimitStatus,TotalDeletedItemSize | Export-Csv $outputfile -Append
 
 
 
       
 
       }
 
    Remove-Item $tempfile
 
}
Remove-Item $outputfile
RunCommands
Send-MailMessage -SmtpServer EXCH1.domain.local -To it@balbala.com -From MailBoxesReport@blabla.com -Subject "Daily Mailbox Status Report [EXCH1]" -Body "Please Find Attached The Mailboxes Status Report" -attachment $outputfile

My problem now is i don't want to use : DatabaseIssueWarningQuota and DatabaseProhibitSendQuota which are the DB defaults but the specific custom values i have set for each user . Can anyone help ?

Wednesday, August 10, 2016 5:57 AM

Ok first of all ,

Not all my users have unlimited quota but still your output is this :

DisplayName Database TotalItemSize Total Size (MB) SendQuota Send Quota (MB) ItemCount LastLogonTime StorageLimitStatus
Mailbox Database 0993595259 58414407404 55,708.32MB -1 -1 312032 10/08/2016 08:36 163.7 MB (171,688,664 bytes)
Mailbox Database 0993595259 10438796039 9,955.21MB -1 -1 70709 10/08/2016 08:44 8.673 MB (9,094,297 bytes)
Mailbox Database 0993595259 9182469753 8,757.09MB -1 -1 40544 0 B (0 bytes)
Mailbox Database 0993595259 7527392665 7,178.68MB -1 -1 34022 08/08/2016 09:19 32.3 MB (33,868,382 bytes)
Mailbox Database 0993595259 7188304845 6,855.30MB -1 -1 31905 09/08/2016 17:27 37.2 MB (39,007,248 bytes)
Mailbox Database 0993595259 7063091983 6,735.89MB -1 -1 32523 10/08/2016 08:45 40.8 MB (42,778,878 bytes)
Mailbox Database 0993595259 6901808182 6,582.08MB -1 -1 28451 10/08/2016 08:50 51.1 MB (53,581,505 bytes)
Mailbox Database 0993595259 6513740946 6,211.99MB -1 -1 28919 10/08/2016 08:44 91.45 MB (95,888,458 bytes)
Mailbox Database 0993595259 6461438548 6,162.11MB -1 -1 48392 10/08/2016 08:44 26.39 MB (27,676,648 bytes)
Mailbox Database 0993595259 5100049080 4,863.79MB -1 -1 50780 04/08/2016 08:32 0 B (0 bytes)
Mailbox Database 0993595259 4852228888 4,627.45MB -1 -1 63738 10/08/2016 08:44 364 KB (372,744 bytes)
Mailbox Database 0993595259 4747260097 4,527.34MB -1 -1 16079 10/08/2016 08:46 1.983 MB (2,079,218 bytes)
Mailbox Database 0993595259 4272887441 4,074.94MB -1 -1 18716 10/08/2016 08:46 7.997 KB (8,189 bytes)
Mailbox Database 0993595259 4085088420 3,895.84MB -1 -1 18423 10/08/2016 08:44 40.47 MB (42,433,962 bytes)
Mailbox Database 0993595259 3047424692 2,906.25MB -1 -1 18842 09/08/2016 20:20 14.21 MB (14,895,364 bytes)
Mailbox Database 0993595259 2934300790 2,798.37MB -1 -1 30429 10/08/2016 08:44 836.9 KB (856,981 bytes)
Kyrykos Ioannis Mailbox Database 0993595259 2465853592 2,351.62MB -1 -1 26412 10/08/2016 08:42 1.615 MB (1,693,696 bytes)
























































It is -1 for everyone which is wrong and thus i cannot do my calculations like in my prevous post :

   If (($decTotalItemSize -gt $decWarningQuota) -and ($decTotalItemSize -lt $decSendQuota)) {
            $User.StorageLimitStatus = "IssueWarning"
            }
 
        ElseIf ($decTotalItemSize -gt $decSendQuota) {
            $User.StorageLimitStatus = "OverQuota"
            }
       
       Else {
            $User.StorageLimitStatus = "BelowLimit"
            }






I have removed names for privacy .







































































Wednesday, August 17, 2016 7:30 AM

Anyone can help ?

Wednesday, August 17, 2016 7:55 AM

Hi Kleanthis,

sorry it took me a while to get back at you:
I had a little typo in there that messed with the results:

# Typo present:
if (($SendQuota -like "unlimited") -or ($SendQuote -eq $null))

# Typo fixed
if (($SendQuota -like "unlimited") -or ($SendQuota -eq $null))

Just replace this line and the results should be fine.

Cheers,
Fred

There's no place like 127.0.0.1

  • Last updated on