PoshCode Logo PowerShell Code Repository

Find-DuplicateSMTP (modification of post by Kevin view diff)
diff | embed code: <script type="text/javascript" src="http://PoshCode.org/embed/1632"></script>download | new post

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.
http://get-scriptjunkie.blogspot.com/2010/02/find-duplicate-smtp-addresses.html

  1. #######################################################################
  2. ####        Written By:  Kevin Dunn                                ####
  3. ####        Date:        1/21/2009                                 ####
  4. ####                                                               ####
  5. ####                  FindDuplicateSMTPAddr.ps1                    ####
  6. ####                                                               ####
  7. ####        Requires Quest Active Directory cmdlets                ####
  8. ####        Requires Excel (tested on 2007)                        ####
  9. #######################################################################
  10.  
  11. #User defined Variables
  12.  
  13. $SMTPServer = "127.0.0.1"
  14. $SenderAddress = "FromAddress@yourdomain.com"
  15. $RecipientAddresses = "You@yourdomain.com"
  16. $Subject = "Duplicate SMTP Address Report"
  17. $Domain = "yourdomain.com"
  18.  
  19. #Set this to a literal path i.e. "C:\scripts\" if not running as a .ps1
  20. #This location is where the spreadsheet will be saved to
  21. #$ScriptPath = "C:\Scripts\"
  22. $ScriptPath = ($myInvocation.MyCommand.Path).Replace($myInvocation.MyCommand.Name, "")
  23.  
  24.  
  25. if ((Get-PSSnapin "Quest.ActiveRoles.ADManagement" -ErrorAction SilentlyContinue) -eq $null)
  26. {
  27.         Add-PSSnapin "Quest.ActiveRoles.ADManagement" -ErrorVariable Err -ErrorAction SilentlyContinue
  28.         if ($Err)
  29.         {
  30.                 Write-Host "`tError loading Quest.ActiveRoles.ADManagement" -ForeGroundColor Green
  31.                 exit
  32.         }
  33. }
  34.  
  35. #Gather proxyaddresses information from AD
  36. Write-Host "`n`tGathering email addresses from Active Directory" -ForeGroundColor Yellow
  37. $Filter = '(|(&(objectClass=user)(homeMDB=*))(&(mailNickName=*)(objectClass=Contact))(&(mailNickName=*)(objectClass=group)))'
  38. $MailObjects = get-qadobject -service $Domain -sizeLimit 0 -ldapfilter $Filter -DontUseDefaultIncludedProperties `
  39.         -IncludedProperties proxyAddresses | Select proxyAddresses, ClassName
  40. $NumberMailobjects = $MailObjects.count
  41. Write-Host "`tFound " -noNewline -ForeGroundColor Yellow
  42. Write-Host "$NumberMailobjects " -noNewLine -ForeGroundColor Green
  43. Write-Host "mail enabled objects" -ForeGroundColor Yellow
  44.  
  45. #Count and Write proxyaddresses information to hashtable
  46. Write-Host "`n`tCounting proxyAddresses Data" -ForeGroundColor Yellow
  47. $EmailCount = @{}
  48. $EmailTypeCount = @{}
  49. $EmailDomainCount = @{}
  50. $oldPos = $host.UI.RawUI.CursorPosition
  51. Foreach ($MailObject in $MailObjects){
  52.         $ObjectType = $MailObject.ClassName
  53.         $MailObject.ProxyAddresses | Foreach {
  54.                 #Count Type of Addresses
  55.                 $Type = [string]$_.split(":")[0]
  56.                 if($EmailTypeCount.ContainsKey($Type) -eq $False)
  57.                 {
  58.                         $EmailTypeCount.Add($Type, 1)
  59.                 }
  60.                 Else
  61.                 {
  62.                         $Count = $EmailTypeCount.Get_Item($Type)
  63.                         $Count++
  64.                         $EmailTypeCount.Set_Item($Type, $Count)
  65.                 }
  66.                
  67.                 #Count Unique Email Addresses
  68.                 if($EmailCount.ContainsKey($_) -eq $False)
  69.                 {
  70.                         $EmailCount.Add($_, 1)
  71.                 }
  72.                 Else
  73.                 {
  74.                         $Count = $EmailCount.Get_Item($_)
  75.                         $Count++
  76.                         $EmailCount.Set_Item($_, $Count)
  77.                 }
  78.                
  79.                 #Count Mail domains
  80.                 $Domain = [string]$_.Split("@")[1]
  81.                 if($Domain -ne $null)
  82.                 {
  83.                         if($EmailDomainCount.ContainsKey($Domain) -eq $False)
  84.                         {
  85.                                 $EmailDomainCount.Add($Domain, 1)
  86.                         }
  87.                         Else
  88.                         {
  89.                                 $Count = $EmailDomainCount.Get_Item($Domain)
  90.                                 $Count++
  91.                                 $EmailDomainCount.Set_Item($Domain, $Count)
  92.                         }
  93.                 }
  94.         }
  95.         #Keep the output refresh from eating CPU
  96.         $UpdateOutPut = $False
  97.         If ($NumberAddType -lt $EmailTypeCount.Count){$UpdateOutPut = $True}
  98.         elseIf ($NumberMailDomains -lt $EmailDomainCount.Count){$UpdateOutPut = $True}
  99.         elseIf (($EmailCount.Count % 100) -eq 0){$UpdateOutPut = $True}
  100.        
  101.         If ($UpdateOutPut -eq $True)
  102.         {
  103.                 $NumberAddType = $EmailTypeCount.Count
  104.                 $NumberAddresses = $EmailCount.Count
  105.                 $NumberMailDomains = $EmailDomainCount.Count
  106.                 $host.UI.RawUI.CursorPosition = $oldPos
  107.                 Write-Host "`tFound " -noNewline -ForeGroundColor Yellow
  108.                 Write-Host "$NumberAddresses " -noNewLine -ForeGroundColor Green
  109.                 Write-Host "unique email addresses" -ForeGroundColor Yellow
  110.                 Write-Host "`tFound " -noNewline -ForeGroundColor Yellow
  111.                 Write-Host "$NumberAddType " -noNewLine -ForeGroundColor Green
  112.                 Write-Host "address types" -ForeGroundColor Yellow
  113.                 Write-Host "`tFound " -noNewline -ForeGroundColor Yellow
  114.                 Write-Host "$NumberMailDomains " -noNewLine -ForeGroundColor Green
  115.                 Write-Host "mail domains`n" -ForeGroundColor Yellow
  116.         }
  117. }
  118. $EmailDomains = $EmailDomainCount.GetEnumerator() | Sort Key
  119. $EmailTypes = $EmailTypeCount.GetEnumerator() | Sort Key
  120.  
  121.  
  122. #Filter proxyaddresses data for duplicates
  123. Write-Host "`n`tFiltering for duplicate email addresses" -ForeGroundColor Yellow
  124. $Duplicates = $EmailCount.GetEnumerator() | ? {$_.Value -gt 1}
  125. $Duplicates  = $Duplicates | Sort
  126. $NumberDuplicates = $Duplicates.Count
  127. Write-Host "`tFound " -noNewline -ForeGroundColor Yellow
  128. Write-Host "$NumberDuplicates " -noNewLine -ForeGroundColor Green
  129. Write-Host "duplicate email addresses`n" -ForeGroundColor Yellow
  130.  
  131.  
  132. #Retrieve additional information about objects with duplicate proxyaddresses
  133. Write-Host "`n`tGathering information about the objects with duplicate email addresses" -ForeGroundColor Yellow
  134. $DupeOutput = @()
  135. $Count = 0
  136. $oldPos = $host.UI.RawUI.CursorPosition
  137. $Duplicates | Foreach {
  138.         $count++
  139.         [string]$Email = $_.Key
  140.         $Filter = "(proxyAddresses=*$Email*)"
  141.         $ObjectsWithDupes = get-qadobject -service $Domain -ldapFilter $Filter `
  142.                 -DontUseDefaultIncludedProperties -includedProperties extensionAttribute3 | `
  143.                 Select DisplayName, samAccountName, DN, ClassName, extensionAttribute3
  144.         $ObjectsWithDupes | foreach {
  145.                 $_ | add-member noteproperty -Name "DupeEmailAddress" -Value $Email
  146.         }
  147.         $DupeOutput += $ObjectsWithDupes
  148.         $DupesProcessed = ($DupeOutput | Select DN -Unique).Count
  149.         $UsersProcessed = ($DupeOutput | ? {$_.Classname -eq "user"} | Select DN -Unique).Count
  150.         $GroupsProcessed = ($DupeOutput | ? {$_.Classname -eq "group"} | Select DN -Unique).Count
  151.         $ContactsProcessed = ($DupeOutput | ? {$_.Classname -eq "contact"} | Select DN -Unique).Count
  152.         $host.UI.RawUI.CursorPosition = $oldPos
  153.         Write-Host "`tProcessed " -noNewline -ForeGroundColor Yellow
  154.         Write-Host "$DupesProcessed " -noNewLine -ForeGroundColor Green
  155.         Write-Host "objects and " -noNewLine -ForeGroundColor Yellow
  156.         Write-Host "$Count" -noNewLine -ForeGroundColor Green
  157.         Write-Host " addresses" -ForeGroundColor Yellow
  158. }
  159. $DupeCount = $DupeOutput.count
  160. $DupeOutput = $DupeOutput | Sort displayname, ClassName, DupeEmailAddress
  161.  
  162. #Open Excel
  163. Write-Host "`n`tGenerating spreadsheet" -ForeGroundColor Yellow
  164. $Excel = New-Object -comobject Excel.Application
  165. $Excel.Visible = $False
  166. $WB = $Excel.Workbooks.Add(1)
  167.  
  168. #Create First Worksheet
  169. $EmailParseData = $WB.Worksheets.Item(1)
  170. [void]$EmailParseData.Activate()
  171. $EmailParseData.Name = "SMTP Data"
  172.  
  173.  
  174. #Make the top row pretty
  175. [void]$Excel.Cells.Item(2,1).Select()
  176. $Excel.ActiveWindow.FreezePanes = $True
  177. [void]$Excel.Range($Excel.Cells.item((1),(1)),$Excel.cells.item((1),(2))).Select()
  178. $Excel.Selection.Interior.ColorIndex = 48
  179. [void]$Excel.Selection.Font.Bold
  180. $Excel.Selection.Font.Size = 12
  181. $Excel.Selection.HorizontalAlignment = -4108
  182. [void]$Excel.Range($Excel.Cells.item((1),(4)),$Excel.cells.item((1),(5))).Select()
  183. $Excel.Selection.Interior.ColorIndex = 48
  184. [void]$Excel.Selection.Font.Bold
  185. $Excel.Selection.Font.Size = 12
  186. $Excel.Selection.HorizontalAlignment = -4108
  187. $Row = 1
  188.  
  189. #Populate Top row
  190. $EmailParseData.Cells.Item($Row,1) = "Domain"
  191. $Excel.Columns.item("A:A").ColumnWidth = 45
  192. $EmailParseData.Cells.Item($Row,2) = "Number Of Occurances"
  193. $Excel.Columns.item("B:B").ColumnWidth = 23
  194. $EmailParseData.Cells.Item($Row,4) = "Address Type"
  195. $Excel.Columns.item("D:D").ColumnWidth = 16
  196. $EmailParseData.Cells.Item($Row,5) = "Number Of Occurances"
  197. $Excel.Columns.item("E:E").ColumnWidth = 23
  198. $Row = 2
  199.  
  200. #Write to first worksheet
  201. Write-Host "`n`tWriting Email Domains" -ForeGroundColor Yellow
  202. $EmailDomains | Foreach {
  203.         $EmailParseData.Cells.Item($Row,1) = $_.Key
  204.         $EmailParseData.Cells.Item($Row,2) = $_.Value
  205.         $Row++
  206. }
  207.  
  208. Write-Host "`n`tWriting Address Types" -ForeGroundColor Yellow
  209. $Row = 2
  210. $EmailTypes | Foreach {
  211.         $EmailParseData.Cells.Item($Row,4) = $_.Key
  212.         $EmailParseData.Cells.Item($Row,5) = $_.Value
  213.         $Row++
  214. }
  215.  
  216. #Add Second Worksheet
  217. Write-Host "`n`t`Creating Second Worksheet" -ForeGroundColor Yellow
  218. $DupeWS = $Excel.Worksheets.Add()
  219. [void]$DupeWS.Activate()
  220. $DupeWS.Name = "Duplicate Address Data"
  221. $Row = 1
  222.  
  223. #Make the top row pretty
  224. [void]$Excel.Cells.Item(2,1).Select()
  225. $Excel.ActiveWindow.FreezePanes = $True
  226. [void]$Excel.Range($Excel.Cells.item((1),(1)),$Excel.cells.item((1),(6))).Select()
  227. $Excel.Selection.Interior.ColorIndex = 48
  228. [void]$Excel.Selection.Font.Bold
  229. $Excel.Selection.Font.Size = 12
  230. $Excel.Selection.HorizontalAlignment = -4108
  231.  
  232. #Populate data in the top row
  233. $DupeWS.Cells.Item($row,1) = "DisplayName"
  234. $Excel.Columns.item("A:A").ColumnWidth = 35
  235.  
  236. $DupeWS.Cells.Item($row,2) = "samAccountName"
  237. $Excel.Columns.item("B:B").ColumnWidth = 25
  238.  
  239. $DupeWS.Cells.Item($row,3) = "DupeEmailAddress"
  240. $Excel.Columns.item("C:C").ColumnWidth = 60
  241.  
  242. $DupeWS.Cells.Item($row,4) = "ClassName"
  243. $Excel.Columns.item("D:D").ColumnWidth = 20
  244.  
  245. $DupeWS.Cells.Item($row,5) = "ExtensionAttribute3"
  246. $Excel.Columns.item("E:E").ColumnWidth = 35
  247.  
  248. $DupeWS.Cells.Item($row,6) = "DN"
  249. $Excel.Columns.item("F:F").ColumnWidth = 90
  250.  
  251. #Begin writing duplicate email address data
  252. $row++
  253. Write-Host "`n`tWriting Duplicate Address Data" -ForeGroundColor Yellow
  254. $oldPos = $host.UI.RawUI.CursorPosition
  255. $DupeOutput | Foreach {
  256.         $DupeWS.Cells.Item($row,1) = $_.Displayname
  257.         $DupeWS.Cells.Item($row,2) = $_.Samaccountname
  258.         $DupeWS.Cells.Item($row,3) = $_.DupeEmailAddress
  259.         $DupeWS.Cells.Item($row,4) = $_.ClassName
  260.         $DupeWS.Cells.Item($row,5) = $_.extensionAttribute3
  261.         $DupeWS.Cells.Item($row,6) = $_.DN
  262.         $row++
  263.        
  264.         If (($row % 5) -eq 0)
  265.         {
  266.                 $host.UI.RawUI.CursorPosition = $oldPos
  267.                 Write-Host "`tOutput " -noNewline -ForeGroundColor Yellow
  268.                 Write-Host "$row " -noNewLine -ForeGroundColor Green
  269.                 Write-Host "lines to Excel`n" -ForeGroundColor Yellow
  270.         }
  271. }
  272. $host.UI.RawUI.CursorPosition = $oldPos
  273. Write-Host "`tOutput " -noNewline -ForeGroundColor Yellow
  274. Write-Host "$row " -noNewLine -ForeGroundColor Green
  275. Write-Host "lines to Excel" -ForeGroundColor Yellow
  276.  
  277. #Save the spreadsheet and exit Excel
  278. $Excel.DisplayAlerts = $False
  279. $saveAs = $ScriptPath + "DupeEmailReport." + (get-date).dayofyear + ".xls"
  280. write-host "`tSaving Report to: $saveAS`n`n`n" -ForegroundColor Cyan
  281. $WB.SaveAs($saveAs, 1)
  282. $WB.Close()
  283. $Excel.Quit()
  284.  
  285. #Create the message
  286. $Body = "`<BR>" +`
  287.                 "  Mail Enabled Objects Found`t`t $NumberMailobjects " +`
  288.                 "`<BR>" +`
  289.                 "  Unique Email Addresses Found:`t`t $NumberAddresses " + `
  290.                 "`<BR>" + `
  291.                 "  Duplicated Email Addresses:`t`t $NumberDuplicates " + `
  292.                 "`<BR><BR>" + `
  293.                 "  Mail Objects Affected:`t`t $DupesProcessed " + `
  294.                 "`<BR>" + `
  295.                 "  Users Affected:`t`t $UsersProcessed " + `
  296.                 "`<BR>" + `
  297.                 "  Groups Affected:`t`t $GroupsProcessed " + `
  298.                 "`<BR>" + `
  299.                 "  Contacts Affected:`t`t $ContactsProcessed " + `
  300.                 "`<BR><BR>" +`
  301.                 "  Number of Address Types:`t`t $NumberAddType " + `
  302.                 "`<BR>" + `
  303.                 "  Number of Mail Domains:`t`t $NumberMailDomains"
  304.                
  305. $Attachment = new-object System.Net.Mail.Attachment($saveAs)
  306. $objMail = new-object System.Net.Mail.MailMessage
  307. $objMail.From = $SenderAddress
  308. $objMail.Sender = $SenderAddress
  309. $objMail.To.Add($RecipientAddresses)
  310. $objMail.Subject = $Subject
  311. $objMail.Body = $Body
  312. $objMail.IsBodyHTML = $true
  313. $objMail.Attachments.Add($Attachment)
  314.  
  315. #Send the message
  316. $objSMTP = New-Object System.Net.Mail.SmtpClient
  317. $objSMTP.Host = $SMTPServer
  318. $objSMTP.UseDefaultCredentials = $true
  319.  
  320. $objSMTP.Send($objMail)

Submit a correction or amendment below (click here to make a fresh posting)
After submitting an amendment, you'll be able to view the differences between the old and new posts easily.

Syntax highlighting:


Remember me