PoshCode Logo PowerShell Code Repository

Find-DuplicateSMTP by Kevin 24 months ago
View followups from Kevin | embed code: <script type="text/javascript" src="http://PoshCode.org/embed/1631"></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.

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