DotNetPanel Hosted Exchange Quota Report

I was asked to write a script that exports a report from our hosting departments DotNetPanel managed Exchange environment. The report should include all the different quota limits and create a csv file with data ordered by hosted company and quota limit. Here is the result:

# Get-MailboxQuota.ps1
# Script for exporting mailbox size and qouta.

# Add the Microsoft Exchange PSSnapIn
Add-PSSnapIn Microsoft.Exchange.Management.PowerShell.Admin

# Prepare the CSV file
Write-Host "Preparing csv…"
$FilePath = "C:\quota.csv"
$Date = get-date
$stream = [System.IO.StreamWriter] $FilePath
$stream.Writeline($Date)
$stream.Writeline("Company,100MB,200MB,300MB,400MB,500MB,1GB,2GB,5GB,10GB,Unlimited")
Write-Host "Done!"
Write-Host ""

# Get the OUs from AD
Write-Host "Gathering AD and mailbox information…"
$adpath = [ADSI]"LDAP://OU=hostingOrganisationOu,DC=domain,DC=local"
foreach ($base in $adpath.psbase.Children)
{
    if ($base.ObjectCategory -like ‘*Organizational-Unit*’)
    {
        Write-Host $base.name

        $childou = "LDAP://"
        $childou += $base.DistinguishedName
        $childou = [ADSI]$childou

        $parentou = "hostingOrganisationOu/" + $base.name

        $100MB = get-mailbox | Select Name,ProhibitSendQuota,OrganizationalUnit | where {$_.OrganizationalUnit -like "*$parentou*"} | where {$_.ProhibitSendQuota -eq "100MB"}
        if ($100MB -eq $null)
        {
            $100MB = "0"
        }
        elseif ($100MB.Count)
        {
            $100MB = $100MB.Count
        }
        else
        {
            $100MB = "1"
        }
        $200MB = get-mailbox | Select Name,ProhibitSendQuota,OrganizationalUnit | where {$_.OrganizationalUnit -like "*$parentou*"} | where {$_.ProhibitSendQuota -eq "200MB"}
        if ($200MB -eq $null)
        {
            $200MB = "0"
        }
        elseif ($200MB.Count)
        {
            $200MB = $200MB.Count
        }
        else
        {
            $200MB = "1"
        }

        $300MB = get-mailbox | Select Name,ProhibitSendQuota,OrganizationalUnit | where {$_.OrganizationalUnit -like "*$parentou*"} | where {$_.ProhibitSendQuota -eq "300MB"}
        if ($300MB -eq $null)
        {
            $300MB = "0"
        }
        elseif ($300MB.Count)
        {
            $300MB = $300MB.Count
        }
        else
        {
            $300MB = "1"
        }

        $400MB = get-mailbox | Select Name,ProhibitSendQuota,OrganizationalUnit | where {$_.OrganizationalUnit -like "*$parentou*"} | where {$_.ProhibitSendQuota -eq "400MB"}
        if ($400MB -eq $null)
        {
            $400MB = "0"
        }
        elseif ($400MB.Count)
        {
            $400MB = $400MB.Count
        }
        else
        {
            $400MB = "1"
        }

        $500MB = get-mailbox | Select Name,ProhibitSendQuota,OrganizationalUnit | where {$_.OrganizationalUnit -like "*$parentou*"} | where {$_.ProhibitSendQuota -eq "500MB"}
        if ($500MB -eq $null)
        {
            $500MB = "0"
        }
        elseif ($500MB.Count)
        {
            $500MB = $500MB.Count
        }
        else
        {
            $500MB = "1"
        }
        $1000MB = get-mailbox | Select Name,ProhibitSendQuota,OrganizationalUnit | where {$_.OrganizationalUnit -like "*$parentou*"} | where {$_.ProhibitSendQuota -eq "1000MB"}
        if ($1000MB -eq $null)
        {
            $1000MB = "0"
        }
        elseif ($1000MB.Count)
        {
            $1000MB = $1000MB.Count
        }
        else
        {
            $1000MB = "1"
        }
        $2000MB = get-mailbox | Select Name,ProhibitSendQuota,OrganizationalUnit | where {$_.OrganizationalUnit -like "*$parentou*"} | where {$_.ProhibitSendQuota -eq "2000MB"}
        if ($2000MB -eq $null)
        {
            $2000MB = "0"
        }
        elseif ($2000MB.Count)
        {
            $2000MB = $2000MB.Count
        }
        else
        {
            $2000MB = "1"
        }
        $5000MB = get-mailbox | Select Name,ProhibitSendQuota,OrganizationalUnit | where {$_.OrganizationalUnit -like "*$parentou*"} | where {$_.ProhibitSendQuota -eq "5000MB"}
        if ($5000MB -eq $null)
        {
            $5000MB = "0"
        }
        elseif ($5000MB.Count)
        {
            $5000MB = $5000MB.Count
        }
        else
        {
            $5000MB = "1"
        }
        $10000MB = get-mailbox | Select Name,ProhibitSendQuota,OrganizationalUnit | where {$_.OrganizationalUnit -like "*$parentou*"} | where {$_.ProhibitSendQuota -eq "10000MB"}
        if ($10000MB -eq $null)
        {
            $10000MB = "0"
        }
        elseif ($10000MB.Count)
        {
            $10000MB = $10000MB.Count
        }
        else
        {
            $10000MB = "1"
        }
        $Unlimited = get-mailbox | Select Name,ProhibitSendQuota,OrganizationalUnit | where {$_.OrganizationalUnit -like "*$parentou*"} | where {$_.ProhibitSendQuota -eq "unlimited"}
        if ($Unlimited -eq $null)
        {
            $Unlimited = "0"
        }
        elseif ($Unlimited.Count)
        {
            $Unlimited = $Unlimited.Count
        }
        else
        {
            $Unlimited = "1"
        }       
        $quotaLine = $base.name.ToString()
        $quotaLine += ","
        Write-Host "100MB: " $100MB
        $quotaLine += $100MB
        $quotaLine += ","
        Write-Host "200MB: " $200MB
        $quotaLine += $200MB

        $quotaLine += ","
        Write-Host "300MB: " $300MB
        $quotaLine += $300MB

        $quotaLine += ","
        Write-Host "400MB: " $400MB
        $quotaLine += $400MB

        $quotaLine += ","
        Write-Host "500MB: " $500MB
        $quotaLine += $500MB

        $quotaLine += ","
        Write-Host "1000MB: " $1000MB
        $quotaLine += $1000MB

        $quotaLine += ","
        Write-Host "2000MB: " $2000MB
        $quotaLine += $2000MB

        $quotaLine += ","
        Write-Host "5000MB: " $5000MB
        $quotaLine += $5000MB
        $quotaLine += ","
        Write-Host "10000MB: " $10000MB
        $quotaLine += $10000MB
        $quotaLine += ","
        Write-Host "Unlimited: " $Unlimited
        $quotaLine += $Unlimited
        Write-Host "Writing the following to file : " $quotaLine
        $stream.Writeline($quotaLine)

        Write-Host ""
    }       
}
$stream.close()

#Send an email using a user with an exchange mailbox

Write-Host "Sending report via e-mail…"

$filename = “c:\quota.csv"
$smtpServer = “exchangeHUB"

$message = new-object Net.Mail.MailMessage
$attachment = new-object Net.Mail.Attachment($filename)
$smtpClient = new-object Net.Mail.SmtpClient($smtpServer)

$credentials = new-object System.Net.networkCredential
$credentials.domain = "domain"
$credentials.UserName = "username"
$credentials.Password = "password"
$smtpClient.Credentials = $credentials

$message.From = “from@domain.com"
$message.To.Add("to@domain.com")
$message.Subject = “Mailbox Quota Report"
$message.Body = “Attached you will find the mailbox quota report. Import the .csv in Excel."
$message.Attachments.Add($attachment)

$smtpClient.Send($message)

Write-Host "Done!"
Write-Host ""

If you have any use for it in you DNP environment feel free to do so. Let me know if you run in to any trouble using it!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: