Get-DatabaseDistribution.ps1

#msexchange #powershell

How cr@p am I? I totally forgot about this baby.  So this is rough and ready Disappointed smile not had a chance to sort the help out at the beginning, but I’m sure you will get the idea.  Basically run this with one of the switches ByMailboxSize or ByMailboxCount and it will look at your exchange 2010 mailboxes and try and load balance the users.  It doesn’t actually DO anything, what it will do is generate a CSV that you use as input for Move-RequestFromCSV that is here: http://flaphead.dns2go.com/?p=2945

Let me know what you think.  In the test I have done so far ByMailboxCount is shweet, ByMailboxSize isn’t perfect.  Enjoy

PARAM([String]$Database="", [String]$BatchName, [Switch]$ByMailboxCount, [Switch]$ByMailboxSize, [String]$SizePercentage=2, [String]$ExportCSV="$pwdmiguserlist.csv")

#http://stackoverflow.com/questions/5863772/powershell-round-down-to-nearest-whole-number
function round( $value, [MidpointRounding]$mode = ‘AwayFromZero’ ) {   [Math]::Round( $value, $mode ) }

#$DB=@();Get-MailboxDatabase * | ForEach{$db += $_.name}

$xPsCheck = Get-PSSnapin | Select Name | Where {$_.Name -Like "*Exchange*"}
If ($xPsCheck -eq $Null) {Add-PsSnapin Microsoft.Exchange.Management.PowerShell.e2010}

Write-Host "Database:……."$Database
Write-Host "ByMailboxCount:."$ByMailboxCount
Write-Host "ByMailboxSize:.."$ByMailboxSize
Write-Host "SizePercentage:."$SizePercentage
Write-Host "ExportCSV:……"$ExportCSV
Write-Host ""

If ($Database -ne ""){
  $tmpDatabaseArray = $database.split(" ")
  $i=0
  $DatabaseHashTable = @{}
  ForEach($item in $tmpDatabaseArray){; $DatabaseHashTable.Add($i,$item); $i++}
}

$tmpDatabaseArray

$MailboxArray=@()
ForEach($item in $tmpDatabaseArray){
  Write-Host "`nChecking Database" $item -Foregroundcolor Green
  $position=$host.ui.rawui.cursorposition
  $tmpMailbox = Get-Mailbox -Database $item -ResultSize unlimited
  $mbxcnt   = 0
  $mbxItems = 0
  $mbxSize  = 0
  ForEach($xMailbox in $tmpMailbox){
    $tmpUser = $xmailbox.PrimarySmtpAddress.toString()
    $host.ui.rawui.cursorposition=$position;
    Write-Host "-" $tmpuser "                                                                          "
    $tmpMailboxArray = "" | Select DisplayName, Email, ServerName, Database, Items, Size, Target, Status 
    $tmpMailboxArray.DisplayName = $xMailbox.DisplayName
    $tmpMailboxArray.Email       = $tmpUser
    $tmpMailboxArray.ServerName  = $xMailbox.ServerName
    $tmpMailboxArray.Database    = $xMailbox.Database
    $tmpMailboxArray.Target      = $xMailbox.Database
    $tmpMailboxStatistics        = Get-Mailbox $tmpUser | Get-MailboxStatistics
    $tmpMailboxArray.Items       = $tmpMailboxStatistics.ItemCount
    $tmpMailboxArray.Size        = $tmpMailboxStatistics.TotalItemSize.Value.ToMB()
    $MailboxArray += $tmpMailboxArray
    $mbxcnt ++
    $mbxItems += $tmpMailboxArray.Items
    $mbxSize  += $tmpMailboxArray.Size
  }
  $host.ui.rawui.cursorposition=$position;
  Write-Host "-> Complete! " -NoNewLIne
  Write-Host $mbxcnt -NoNewLine -ForegroundColor Yellow
  Write-Host " Mailboxes Found with " -NoNewline
  Write-Host $mbxItems -NoNewLine -ForegroundColor Yellow
  Write-host " Items and " -NoNewLine
  Write-Host $mbxSize -ForegroundColor Yellow -NoNewLine
  Write-Host "MB            "
}

#Okay lets count the number of databases
$DatabaseCount = $tmpdatabasearray.count
$MailboxCount  = $MailboxArray.count
$MailboxSize   = ($MailboxArray | Measure-Object Size -sum).Sum
$MailboxesPerDatabase = round ($MailboxCount / $DatabaseCount )
$MBPerDatabase = round ($MailboxSize / $DatabaseCount )

Write-Host "Database Count:……………."$DatabaseCount
Write-Host "Mailbox Count:…………….."$MailboxCount
Write-Host "Ideal Mailboxes Per Database:.."$MailboxesPerDatabase
Write-Host "Total Size:……………….."$MailboxSize
Write-Host "Ideal MB Per Database:………"$MBPerDatabase
$MBPerDatabase = round ($MBPerDatabase + (($MBPerDatabase /100)*$SizePercentage))
Write-Host "`nMB Per Database (+"$SizePercentage"%) = "$MBPerDatabase

#Group MailboxArray by Database
$DatabaseCountArray = $MailboxArray | Group Database
$DatabaseMatrix = @()
ForEach($Item in $DatabaseCountArray){
  $tmpDatabaseMatrix = "" | Select Name, Count, Difference1, Difference2, MB , Difference3, Difference4
  $tmpDatabaseMatrix.Name        = $Item.Name
  $tmpDatabaseMatrix.Count       = $Item.Count
  $tmpDatabaseMatrix.Difference1 = $MailboxesPerDatabase – $Item.Count
  $tmpDatabaseMatrix.Difference2 = $Item.Count – $MailboxesPerDatabase
  $tmpDatabaseMatrix.MB    &#160
;     = ($Item.Group | Measure-Object Size -Sum).Sum
  $tmpDatabaseMatrix.Difference3 = $MBPerDatabase – $tmpDatabaseMatrix.MB
  $tmpDatabaseMatrix.Difference4 = $tmpDatabaseMatrix.MB – $MBPerDatabase

  $DatabaseMatrix += $tmpDatabaseMatrix
}
$DatabaseMatrix | ft

If ($ByMailboxSize){
  Write-Host "`nBY MAILBOX SIZE" -Foregroundcolor Red
  Write-Host "—————"
  Write-Host "`nFinding databases with excess mailboxes"
  $Database2MoveOff = $DatabaseMatrix | Where {$_.Difference4 -ge 0}
  ForEach($db in $Database2MoveOff){
    Write-Host $db.Name -Foregroundcolor Blue
    $tmpExcess = $MailboxArray | where {$_.Database -eq $db.Name} | Sort Size -Descending
    $targetMB=0
    ForEach($yUser in $MailboxArray | where {$_.Database -eq $db.Name} | Sort Size -Descending){
      $tmpMB = $yUser.Size
      If ($tmpMB + $targetMB -gt $MBPerDatabase){
        $yUser.Target = ""
      } ELSE {
        $yuser.Status = "Keep"
        $targetMB += $tmpMB
      } #If ($tmpMB + $targetMB -gt $MBPerDatabase)
    } #ForEach $yUser
  } #ForEach $db

  Write-Host "`nAssigning excess mailboxes"
  ForEach($item in $DatabaseMatrix | Where {$_.Difference3 -gt 0}){
    $Mailboxes2Move =  $DatabaseMatrix | Where {$_.Target -eq ""}  
    $targetMB=$item.MB
    ForEach($yUser in $MailboxArray | where {$_.Target -eq ""} | Sort Size -Descending){
      $tmpMB = $yUser.Size
      If ($tmpMB + $targetMB -gt $MBPerDatabase){
        $yUser.Target = ""
      } ELSE {
        $yuser.Target = $item.Name
        $yuser.Status = "Move"
        $targetMB += $tmpMB
      } #If ($tmpMB + $targetMB -gt $MBPerDatabase)
    } #ForEach $yUser
  } #ForEach $item

  Write-Host "`nSUMMARY" -Foregroundcolor Green
  Write-Host "Ideal MB Per Database:………"$MBPerDatabase
  $MailboxArray | Group Target | %{
    New-Object psobject -Property @{
        Item = $_.Name
        Sum = ($_.Group | Measure-Object Size -Sum).Sum
    }
  }
} #If ($ByMailboxSize)

If ($ByMailboxCount){
  Write-Host "`nBY MAILBOX COUNT" -Foregroundcolor Red
  Write-Host "—————-"
  Write-Host "Check to see if the Excess Mailboxes = Required " -NoNewLine
  $tmpcheck = ($DatabaseMatrix | Measure-Object Difference1 -Sum).sum
  Write-Host $tmpcheck -ForegroundColor Green

  Write-Host "`nFinding databases with excess mailboxes"
  $Database2MoveOff = $DatabaseMatrix | Where {$_.Difference2 -ge 0}
  $ExcessTotal = 0
  ForEach($db in $Database2MoveOff){
    Write-Host $db.Name -Foregroundcolor Blue
    Write-Host "Removing"$db.Difference2 "users`n"
    $ExcessTotal += $db.Difference2
    $MailboxArray | Where {$_.Database.Name -eq $db.name} | Select -First $db.Difference2 | ForEach{$_.Target = "";$_.Status = "Move"}
  }

  Write-Host "Total Excess Mailbox " $ExcessTotal
  $i=0
  ForEach($item in $DatabaseMatrix | Where {$_.Difference1 -gt 0}){
    $Mailboxes2Add = $item.Difference1
    $MailboxArray | Where {$_.Target -eq ""} | Select -First $Mailboxes2Add | ForEach{$_.Target = $item.Name}
  }

  Write-Host "`nSUMMARY" -Foregroundcolor Green
  $MailboxArray | Group Target
}#ByMailboxCount

#Foreach($line in $MailboxArray){Write-Host $line.Email"`t"$line.database}

Write-Host "`nMove Mailboxes"
$outCSV = @()
$ExcessMailboxes = $MailboxArray | where {$_.Status -eq "Move"}

ForEach($item in $ExcessMailboxes){
  $tmpOutCsv                = "" | Select email, TargetDatabase
  $tmpOutCSV.email          = $item.email
  $tmpOutCsv.TargetDatabase = $Item.Target
  $outCSV += $tmpOutCSV
}

ForEach($line in $outCSV){
  $position=$host.ui.rawui.cursorposition
  Write-Host $line.email
  $position.x = 50
  $host.ui.rawui.cursorposition=$position;
  Write-host $line.target
}
$outCSV | Export-csv $ExportCSV -NoTypeInformation

Comments

  1. That’s funny, I was just thinking of coding this out myself. I’m grateful you beat me to it, thanks for the excellent (and sorely needed) script!

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: