Create a report of Exchange 2007 User Quotas via Powershell

I created this script to keep me informed of the mail quotas for a small subset of my current customer’s VIPs.

I’ll break down the different pieces of this script and explain them on different pages. View the full script after the break.




untitled

<##############################################################################
This script reads an input file w/ one email address per line and:
-- Checks to see if a matching mailbox can be found
-- Gathers some data about the mailbox
   -- Mailbox size
   -- Mailbox quota
   -- Percent full
-- Sends an HTML table via email.

Thom Rosario
http://thom.rosar.io/

3.22.2013  
-- Added the ability to send email results as an HTML table

3.18.2013
-- Added the results as an attachment
##############################################################################>

# Initialize
$input   = "c:\Scripts\VIPQuotas\vip.csv"
$output  = "c:\Scripts\VIPQuotas\output.txt"

# Get an email ready 
$smtpServer = "smtp.foo.com"
$msg = new-object Net.Mail.MailMessage
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$msg.From = "admins@foo.com"
$msg.To.Add("admins@foo.com")
$msg.Subject = "VIP Quotas Report"
$msg.Body = @()
$html += "<table cellpadding='3'>
            <tr><td>Owner</td>
            <td>Size</td>
            <td>Quota</td>
            <td>Percent</td>
            <td>Sent</td>
            <td>Calendar</td>
            <td>Contacts</td>
            <td>Deleted</td>
            <td>Drafts</td>
            <td>Notes</td>
            <td>Outbox</td>
            <td>Tasks</td></tr>"

# Create a table object to hold the data
$table = New-Object system.Data.DataTable “table”

$ownerCol    = New-Object system.Data.DataColumn Owner,([string])
$sizeCol     = New-Object system.Data.DataColumn Size,([decimal])
$quotaCol    = New-Object system.Data.DataColumn Quota,([decimal])
$percentCol  = New-Object system.Data.DataColumn Percent,([int])
$sentCol     = New-Object system.Data.DataColumn Sent,([int])
$calendarCol = New-Object system.Data.DataColumn Calendar,([int])
$contactsCol = New-Object system.Data.DataColumn Contacts,([int])
$deletedCol  = New-Object system.Data.DataColumn Deleted,([int])
$draftsCol   = New-Object system.Data.DataColumn Drafts,([int])
$notesCol    = New-Object system.Data.DataColumn Notes,([int])
$outboxCol   = New-Object system.Data.DataColumn Outbox,([int])
$tasksCol    = New-Object system.Data.DataColumn Tasks,([int])

$table.columns.add($ownerCol)
$table.columns.add($sizeCol)
$table.columns.add($quotaCol)
$table.columns.add($percentCol)
$table.columns.add($sentCol)
$table.columns.add($calendarCol)
$table.columns.add($contactsCol)
$table.columns.add($deletedCol)
$table.columns.add($draftsCol)
$table.columns.add($notesCol)
$table.columns.add($outboxCol)
$table.columns.add($tasksCol)

# Loop through the addresses in the file
Import-CSV $input | ForEach-Object {
   $mail = $_.email
   Try {
      $mb = Get-Mailbox $mail

      # Get mailbox stats
      $mbstats = Get-MailboxStatistics $mail
      $size = $mbstats.TotalItemSize.Value.ToMB()
      $mailDB = Get-MailboxDatabase -Identity $mb.Database
      If ($mb.UseDatabaseQuotaDefaults -eq $true) {
         $quota = $mailDB.ProhibitSendQuota.Value.ToMB()
      } else { # Not using default quota
         Try {
            $quota = $mb.ProhibitSendQuota.Value.ToMB()
         }
         Catch {
            $quota = 0 # No quota set
         }
      }
      If ($quota -ne 0) {
         $percent = ($size/$quota)
      } else {
         $percent = 0 # Avoid dividing by zero
      }
      
      # Add a row full of data to the table
      $row         = $table.NewRow()
      $row.Owner   = $mb.DisplayName
      $row.Size    = $size
      $row.Quota   = $quota
      $row.Percent = [Math]::Floor($percent*100)
      
      # If they're above 75% quota, let's see where the space is being used
      If ($percent -gt .75) {
         $html += "<tr>"
         $html += "<td>" + $row.Owner + "</td>"
         $html += "<td>" + $row.Size + "</td>"
         $html += "<td>" + $row.Quota + "</td>"
         $html += "<td>" + $row.Percent + "</td>"
         $folder = Get-MailboxFolderStatistics $mail -FolderScope SentItems
         $row.Sent = $folder.FolderSize.ToMB()
         $html += "<td>" + $row.Sent + "</td>"
         $folder = Get-MailboxFolderStatistics $mail -FolderScope Calendar
         $row.Calendar = $folder.FolderSize.ToMB()
         $html += "<td>" + $row.Calendar + "</td>"
         $folder = Get-MailboxFolderStatistics $mail -FolderScope Contacts
         $row.Contacts = $folder.FolderSize.ToMB()
         $html += "<td>" + $row.Contacts + "</td>"
         $folder = Get-MailboxFolderStatistics $mail -FolderScope DeletedItems
         $row.Deleted = $folder.FolderSize.ToMB()
         $html += "<td>" + $row.Deleted + "</td>"
         $folder = Get-MailboxFolderStatistics $mail -FolderScope Drafts
         $row.Drafts = $folder.FolderSize.ToMB()
         $html += "<td>" + $row.Drafts + "</td>"
         $folder = Get-MailboxFolderStatistics $mail -FolderScope Notes
         $row.Notes = $folder.FolderSize.ToMB()
         $html += "<td>" + $row.Notes + "</td>"
         $folder = Get-MailboxFolderStatistics $mail -FolderScope Outbox
         $row.Outbox = $folder.FolderSize.ToMB()
         $html += "<td>" + $row.Outbox + "</td>"
         $folder = Get-MailboxFolderStatistics $mail -FolderScope Tasks
         $row.Tasks = $folder.FolderSize.ToMB()
         $html += "<td>" + $row.Tasks + "</td>"
         $html += "</tr>"
      }   # End of percent > 75 if-then
      $table.Rows.Add($row)
   }   # End of the Try statement
   Catch [System.Exception] {
      echo "Something went wrong with $mail."
   }
}  # Done looping through the csv
$html += "</table><br /><br />"

# Display the table
$outTable = $table | Sort-Object Percent -descending | format-table -AutoSize

# To see the output, uncomment the next line. Not useful when running as a
# scheduled task, so it's disabled in production :p
#$outTable

# Write to disk.
$outTable | Out-File $output  
Copy-Item -Path $output $remote

# Send the file via email
$attachment = new-object Net.Mail.Attachment($output)
$msg.IsBodyHTML = $true
$msg.Attachments.Add($attachment)
$msg.Body += "Here are the VIPs over 75% of their quota.<br /><br />" + $html
$smtp.Send($msg)
$attachment.Dispose()


Leave a Reply

Your email address will not be published. Required fields are marked *