# encoding: ascii
# api: powershell
# title: Find-DuplicateSMTP
# description: This script will query your AD for all users, groups and contacts.  It will then process through each of the SMTP addresses for all objects and count the number of occurances of each address.  The scope expanded as I kept thinking of other interesting questions regarding the SMTP addresses that were in my domain.  Once the script has finished gathering interesting data regarding the SMTP addresses in your domain, it will dump that information out to a spreadsheet (requires Excel), then send the spreadsheet as an email to you.
# version: 0.1
# type: script
# author: Kevin
# license: CC0
# x-poshcode-id: 1631
# x-archived: 2017-02-12T10:02:52
# x-published: 2010-02-08T19:34:00
#
#
#######################################################################
####        Written By:  Kevin Dunn                                ####
####        Date:        1/21/2009                                 ####
####                                                               ####
####                  FindDuplicateSMTPAddr.ps1                    ####
####                                                               ####
####        Requires Quest Active Directory cmdlets                ####
####        Requires Excel (tested on 2007)                        ####
#######################################################################

#User defined Variables

$SMTPServer = "127.0.0.1" 
$SenderAddress = "FromAddress@yourdomain.com"
$RecipientAddresses = "You@yourdomain.com"
$Subject = "Duplicate SMTP Address Report"
$Domain = "yourdomain.com"
#Set this to a literal path i.e. "C:\scripts\" if not running as a .ps1
#This location is where the spreadsheet will be saved to
#$ScriptPath = "C:\Scripts\"
$ScriptPath = ($myInvocation.MyCommand.Path).Replace($myInvocation.MyCommand.Name, "")


if ((Get-PSSnapin "Quest.ActiveRoles.ADManagement" -ErrorAction SilentlyContinue) -eq $null) 
{
	Add-PSSnapin "Quest.ActiveRoles.ADManagement" -ErrorVariable Err -ErrorAction SilentlyContinue
	if ($Err) 
	{
		Write-Host "`tError loading Quest.ActiveRoles.ADManagement" -ForeGroundColor Green
		exit
	}
} 

#Gather proxyaddresses information from AD
Write-Host "`n`tGathering email addresses from Active Directory" -ForeGroundColor Yellow
$Filter = '(|(&(objectClass=user)(homeMDB=*))(&(mailNickName=*)(objectClass=Contact))(&(mailNickName=*)(objectClass=group)))'
$MailObjects = get-qadobject -service $Domain -sizeLimit 0 -ldapfilter $Filter -DontUseDefaultIncludedProperties `
	-IncludedProperties proxyAddresses | Select proxyAddresses, ClassName
$NumberMailobjects = $MailObjects.count
Write-Host "`tFound " -noNewline -ForeGroundColor Yellow
Write-Host "$NumberMailobjects " -noNewLine -ForeGroundColor Green
Write-Host "mail enabled objects" -ForeGroundColor Yellow

#Count and Write proxyaddresses information to hashtable
Write-Host "`n`tCounting proxyAddresses Data" -ForeGroundColor Yellow
$EmailCount = @{}
$EmailTypeCount = @{}
$EmailDomainCount = @{}
$oldPos = $host.UI.RawUI.CursorPosition
Foreach ($MailObject in $MailObjects){
	$ObjectType = $MailObject.ClassName
	$MailObject.ProxyAddresses | Foreach {
		#Count Type of Addresses
		$Type = [string]$_.split(":")[0]
		if($EmailTypeCount.ContainsKey($Type) -eq $False)
		{
			$EmailTypeCount.Add($Type, 1)
		}
		Else
		{
			$Count = $EmailTypeCount.Get_Item($Type)
			$Count++
			$EmailTypeCount.Set_Item($Type, $Count)
		}
		
		#Count Unique Email Addresses
		if($EmailCount.ContainsKey($_) -eq $False)
		{
			$EmailCount.Add($_, 1)
		}
		Else
		{
			$Count = $EmailCount.Get_Item($_)
			$Count++
			$EmailCount.Set_Item($_, $Count)
		}
		
		#Count Mail domains
		$Domain = [string]$_.Split("@")[1]
		if($Domain -ne $null)
		{
			if($EmailDomainCount.ContainsKey($Domain) -eq $False)
			{
				$EmailDomainCount.Add($Domain, 1)
			}
			Else
			{
				$Count = $EmailDomainCount.Get_Item($Domain)
				$Count++
				$EmailDomainCount.Set_Item($Domain, $Count)
			}
		}
	}
	#Keep the output refresh from eating CPU
	$UpdateOutPut = $False
	If ($NumberAddType -lt $EmailTypeCount.Count){$UpdateOutPut = $True}
	elseIf ($NumberMailDomains -lt $EmailDomainCount.Count){$UpdateOutPut = $True}
	elseIf (($EmailCount.Count % 100) -eq 0){$UpdateOutPut = $True}
	
	If ($UpdateOutPut -eq $True)
	{
		$NumberAddType = $EmailTypeCount.Count
		$NumberAddresses = $EmailCount.Count
		$NumberMailDomains = $EmailDomainCount.Count
		$host.UI.RawUI.CursorPosition = $oldPos
		Write-Host "`tFound " -noNewline -ForeGroundColor Yellow
		Write-Host "$NumberAddresses " -noNewLine -ForeGroundColor Green
		Write-Host "unique email addresses" -ForeGroundColor Yellow
		Write-Host "`tFound " -noNewline -ForeGroundColor Yellow
		Write-Host "$NumberAddType " -noNewLine -ForeGroundColor Green
		Write-Host "address types" -ForeGroundColor Yellow
		Write-Host "`tFound " -noNewline -ForeGroundColor Yellow
		Write-Host "$NumberMailDomains " -noNewLine -ForeGroundColor Green
		Write-Host "mail domains`n" -ForeGroundColor Yellow
	}
}
$EmailDomains = $EmailDomainCount.GetEnumerator() | Sort Key
$EmailTypes = $EmailTypeCount.GetEnumerator() | Sort Key


#Filter proxyaddresses data for duplicates
Write-Host "`n`tFiltering for duplicate email addresses" -ForeGroundColor Yellow
$Duplicates = $EmailCount.GetEnumerator() | ? {$_.Value -gt 1}
$Duplicates  = $Duplicates | Sort
$NumberDuplicates = $Duplicates.Count
Write-Host "`tFound " -noNewline -ForeGroundColor Yellow
Write-Host "$NumberDuplicates " -noNewLine -ForeGroundColor Green
Write-Host "duplicate email addresses`n" -ForeGroundColor Yellow


#Retrieve additional information about objects with duplicate proxyaddresses
Write-Host "`n`tGathering information about the objects with duplicate email addresses" -ForeGroundColor Yellow
$DupeOutput = @()
$Count = 0
$oldPos = $host.UI.RawUI.CursorPosition
$Duplicates | Foreach {
	$count++
	[string]$Email = $_.Key
	$Filter = "(proxyAddresses=*$Email*)"
	$ObjectsWithDupes = get-qadobject -service $Domain -ldapFilter $Filter `
		-DontUseDefaultIncludedProperties -includedProperties extensionAttribute3 | `
		Select DisplayName, samAccountName, DN, ClassName, extensionAttribute3
	$ObjectsWithDupes | foreach {
		$_ | add-member noteproperty -Name "DupeEmailAddress" -Value $Email
	}
	$DupeOutput += $ObjectsWithDupes
	$DupesProcessed = ($DupeOutput | Select DN -Unique).Count
	$UsersProcessed = ($DupeOutput | ? {$_.Classname -eq "user"} | Select DN -Unique).Count
	$GroupsProcessed = ($DupeOutput | ? {$_.Classname -eq "group"} | Select DN -Unique).Count
	$ContactsProcessed = ($DupeOutput | ? {$_.Classname -eq "contact"} | Select DN -Unique).Count
	$host.UI.RawUI.CursorPosition = $oldPos
	Write-Host "`tProcessed " -noNewline -ForeGroundColor Yellow
	Write-Host "$DupesProcessed " -noNewLine -ForeGroundColor Green
	Write-Host "objects and " -noNewLine -ForeGroundColor Yellow
	Write-Host "$Count" -noNewLine -ForeGroundColor Green
	Write-Host " addresses" -ForeGroundColor Yellow
}
$DupeCount = $DupeOutput.count
$DupeOutput = $DupeOutput | Sort displayname, ClassName, DupeEmailAddress

#Open Excel
Write-Host "`n`tGenerating spreadsheet" -ForeGroundColor Yellow
$Excel = New-Object -comobject Excel.Application
$Excel.Visible = $False
$WB = $Excel.Workbooks.Add(1)

#Create First Worksheet
$EmailParseData = $WB.Worksheets.Item(1)
[void]$EmailParseData.Activate()
$EmailParseData.Name = "SMTP Data"


#Make the top row pretty
[void]$Excel.Cells.Item(2,1).Select()
$Excel.ActiveWindow.FreezePanes = $True
[void]$Excel.Range($Excel.Cells.item((1),(1)),$Excel.cells.item((1),(2))).Select()
$Excel.Selection.Interior.ColorIndex = 48
[void]$Excel.Selection.Font.Bold
$Excel.Selection.Font.Size = 12
$Excel.Selection.HorizontalAlignment = -4108
[void]$Excel.Range($Excel.Cells.item((1),(4)),$Excel.cells.item((1),(5))).Select()
$Excel.Selection.Interior.ColorIndex = 48
[void]$Excel.Selection.Font.Bold
$Excel.Selection.Font.Size = 12
$Excel.Selection.HorizontalAlignment = -4108
$Row = 1

#Populate Top row
$EmailParseData.Cells.Item($Row,1) = "Domain"
$Excel.Columns.item("A:A").ColumnWidth = 45
$EmailParseData.Cells.Item($Row,2) = "Number Of Occurances"
$Excel.Columns.item("B:B").ColumnWidth = 23
$EmailParseData.Cells.Item($Row,4) = "Address Type"
$Excel.Columns.item("D:D").ColumnWidth = 16
$EmailParseData.Cells.Item($Row,5) = "Number Of Occurances"
$Excel.Columns.item("E:E").ColumnWidth = 23
$Row = 2

#Write to first worksheet
Write-Host "`n`tWriting Email Domains" -ForeGroundColor Yellow
$EmailDomains | Foreach {
	$EmailParseData.Cells.Item($Row,1) = $_.Key
	$EmailParseData.Cells.Item($Row,2) = $_.Value
	$Row++
}

Write-Host "`n`tWriting Address Types" -ForeGroundColor Yellow
$Row = 2
$EmailTypes | Foreach {
	$EmailParseData.Cells.Item($Row,4) = $_.Key
	$EmailParseData.Cells.Item($Row,5) = $_.Value
	$Row++
}

#Add Second Worksheet
Write-Host "`n`t`Creating Second Worksheet" -ForeGroundColor Yellow
$DupeWS = $Excel.Worksheets.Add()
[void]$DupeWS.Activate()
$DupeWS.Name = "Duplicate Address Data"
$Row = 1

#Make the top row pretty
[void]$Excel.Cells.Item(2,1).Select()
$Excel.ActiveWindow.FreezePanes = $True
[void]$Excel.Range($Excel.Cells.item((1),(1)),$Excel.cells.item((1),(6))).Select()
$Excel.Selection.Interior.ColorIndex = 48
[void]$Excel.Selection.Font.Bold
$Excel.Selection.Font.Size = 12
$Excel.Selection.HorizontalAlignment = -4108

#Populate data in the top row
$DupeWS.Cells.Item($row,1) = "DisplayName"
$Excel.Columns.item("A:A").ColumnWidth = 35

$DupeWS.Cells.Item($row,2) = "samAccountName"
$Excel.Columns.item("B:B").ColumnWidth = 25

$DupeWS.Cells.Item($row,3) = "DupeEmailAddress"
$Excel.Columns.item("C:C").ColumnWidth = 60

$DupeWS.Cells.Item($row,4) = "ClassName"
$Excel.Columns.item("D:D").ColumnWidth = 20

$DupeWS.Cells.Item($row,5) = "ExtensionAttribute3"
$Excel.Columns.item("E:E").ColumnWidth = 35

$DupeWS.Cells.Item($row,6) = "DN"
$Excel.Columns.item("F:F").ColumnWidth = 90

#Begin writing duplicate email address data
$row++
Write-Host "`n`tWriting Duplicate Address Data" -ForeGroundColor Yellow
$oldPos = $host.UI.RawUI.CursorPosition
$DupeOutput | Foreach {
	$DupeWS.Cells.Item($row,1) = $_.Displayname
	$DupeWS.Cells.Item($row,2) = $_.Samaccountname
	$DupeWS.Cells.Item($row,3) = $_.DupeEmailAddress
	$DupeWS.Cells.Item($row,4) = $_.ClassName
	$DupeWS.Cells.Item($row,5) = $_.extensionAttribute3
	$DupeWS.Cells.Item($row,6) = $_.DN
	$row++
	
	If (($row % 5) -eq 0)
	{
		$host.UI.RawUI.CursorPosition = $oldPos
		Write-Host "`tOutput " -noNewline -ForeGroundColor Yellow
		Write-Host "$row " -noNewLine -ForeGroundColor Green
		Write-Host "lines to Excel`n" -ForeGroundColor Yellow
	}
}
$host.UI.RawUI.CursorPosition = $oldPos
Write-Host "`tOutput " -noNewline -ForeGroundColor Yellow
Write-Host "$row " -noNewLine -ForeGroundColor Green
Write-Host "lines to Excel" -ForeGroundColor Yellow

#Save the spreadsheet and exit Excel
$Excel.DisplayAlerts = $False
$saveAs = $ScriptPath + "DupeEmailReport." + (get-date).dayofyear + ".xls"
write-host "`tSaving Report to: $saveAS`n`n`n" -ForegroundColor Cyan
$WB.SaveAs($saveAs, 1)
$WB.Close()
$Excel.Quit()

#Create the message
$Body = "`<BR>" +`
		"  Mail Enabled Objects Found`t`t $NumberMailobjects " +`
		"`<BR>" +`
		"  Unique Email Addresses Found:`t`t $NumberAddresses " + `
		"`<BR>" + `
		"  Duplicated Email Addresses:`t`t $NumberDuplicates " + `
		"`<BR><BR>" + `
		"  Mail Objects Affected:`t`t $DupesProcessed " + `
		"`<BR>" + `
		"  Users Affected:`t`t $UsersProcessed " + `
		"`<BR>" + `
		"  Groups Affected:`t`t $GroupsProcessed " + `
		"`<BR>" + `
		"  Contacts Affected:`t`t $ContactsProcessed " + `
		"`<BR><BR>" +`
		"  Number of Address Types:`t`t $NumberAddType " + `
		"`<BR>" + `
		"  Number of Mail Domains:`t`t $NumberMailDomains" 
		
$Attachment = new-object System.Net.Mail.Attachment($saveAs)
$objMail = new-object System.Net.Mail.MailMessage
$objMail.From = $SenderAddress
$objMail.Sender = $SenderAddress
$objMail.To.Add($RecipientAddresses)
$objMail.Subject = $Subject
$objMail.Body = $Body
$objMail.IsBodyHTML = $true
$objMail.Attachments.Add($Attachment)

#Send the message
$objSMTP = New-Object System.Net.Mail.SmtpClient
$objSMTP.Host = $SMTPServer
$objSMTP.UseDefaultCredentials = $true

$objSMTP.Send($objMail)
