Let’s say you’re consolidating your Active Directory domains or merging with another company’s AD environment and you want to know if it’s possible to keep their same login IDs, etc. Sometimes it’s useful to know ahead of time whether or not this is possible. Some migration tools have mechanisms included to test for this, but if you cannot afford such tools and have to use cheaper (free) means the following script might help.
The input file is a spreadsheet with the desired information of the users you wish to migrate. In this example the header line starts on A3 and contains the sAMAccountName, displayName and mail attributes of the users.
sAMAccountName displayName mail
jdoe John Doe jdoe@contoso.com
Option Explicit
On Error Resume Next
Dim objExcel, objSheet, objFile, objFSO, objUser, objDIC
Dim objConnection, objCommand, objRecordSet
‘ Strings declared :
Dim strNTSam, strPathExcel, strDisplayName, strMail, strProxyAddr, strUser
Dim LogFile, flag, arrProxyAddresses, email
‘ Integers declared :
Dim intNumusers, intRow, intCol, intMatch
‘ Path to migration spreadsheet
‘ You may change the name and path of this spreadsheet file in the following line
strPathExcel = "D:migrationfindDuplicates.xls"
‘ Create log file
‘ You may change the name and path of this log file in the following line
LogFile = "D:migrationfindDuplicates.log"
Const ForWriting = 2
Const ForAppending = 8
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(LogFile) Then
Set objFile = objFSO.OpenTextFile(LogFile, ForAppending)
objfile.WriteLine
objFile.Writeline "Beginning search session " & Now
Else
Set objFile = objFSO.CreateTextFile(LogFile)
objFile.Close
Set objFile = objFSO.OpenTextFile(LogFile, ForWriting)
objfile.Writeline "Beginning search session " & Now
End If
‘ Connect to spreadsheet where users are stored
Set objExcel = CreateObject("Excel.Application")
‘ Open the Speadsheet (Error Handling Section).
On Error Resume Next
Err.Clear
objExcel.Workbooks.Open strPathExcel
objExcel.Visible = True
If Err.Number <> 0 Then
Err.Clear
On Error GoTo 0
Wscript.Echo "Edit the path to YOUR spreadsheet " & strPathExcel
Wscript.Quit
End If
On Error GoTo 0
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
‘ Create ADODB Connection
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open "Provider=ADsDSOObject;"
Set objCommand = CreateObject("ADODB.Command")
objCommand.ActiveConnection = objConnection
‘ LDAP query information
objCommand.CommandText = _
"<LDAP://dc=contoso,dc=com>;(&(objectCategory=person)(proxyAddresses=*));
_AdsPath,proxyAddresses,displayName,samAccountName,distinguishedName,mail;subtree"
‘ Increase page size for > 1,000 user objects
objCommand.Properties("Page Size") = 100000
‘ Execute the search
Set objRecordSet = objCommand.Execute
‘ Initialize variables
intRow=4
intMatch=0
‘ Loop through the spreadsheet until there are no more records
Do
flag=""
strNTSam = Trim(objSheet.Cells(intRow, 1).Value)
strDisplayName = Trim(objSheet.Cells(intRow, 2).Value)
strMail = Trim(objSheet.Cells(intRow, 3).Value)
WScript.Echo "Trying to find a match on…"
WScript.Echo
Wscript.Echo "samAccountName: " & strNTSam
WScript.Echo "displayName: " & strDisplayName
WScript.Echo "email address: " & strMail
WScript.Echo
‘ Check for any missing fields
If strNTSam = "" Then
WScript.Echo "Missing samAccountName in spreadsheet. Please populate all fields and execute script again."
WScript.Echo
cleanup
WScript.Quit
else if strDisplayName = "" Then
WScript.Echo "Missing displayName in spreadsheet. Please populate all fields and execute script again."
WScript.Echo
cleanup
WScript.Quit
else if strMail = "" Then
WScript.Echo "Missing email address in spreadsheet. Please populate all fields and execute script again."
WScript.Echo
cleanup
WScript.Quit
End If
End If
End If
‘ This is where we start comparing the values in the spreadsheet with those found in AD to see if there are
‘ any matches on the fields we’re interested in.If there are matches, they are written to the logfile and
‘ are displayed on the screen.
While Not objRecordSet.EOF
‘ Check for duplicate samAccountName
If strNTSam = objRecordSet.Fields("samAccountName") Then
objfile.writeline "Found matching samAccountName in AD: " & strNTSam &";"& objRecordSet.Fields("distinguishedName")
WScript.Echo "Found matching samAccountName in AD: " & strNTSam
‘objfile.writeline "DN of matching object: " & objRecordSet.Fields("distinguishedName")
WScript.Echo "DN of matching object: " & objRecordSet.Fields("distinguishedName")
WScript.Echo
intMatch=intMatch+1
flag="Found match"
End If
‘ Check for duplicate displayName
If strDisplayName = objRecordSet.Fields("displayName") Then
objfile.writeline "Found matching display name in AD: " & strDisplayName &";"& objRecordSet.Fields("distinguishedName")
WScript.Echo "Found matching display name in AD: " & strDisplayName
‘objfile.writeline "DN of matching object: " & objRecordSet.Fields("distinguishedName")
WScript.Echo "DN of matching object: " & objRecordSet.Fields("distinguishedName")
WScript.Echo
intMatch=intMatch+1
flag="Found match"
End If
objRecordset.MoveNext
Wend
‘ Check for duplicate email address
Dim rootDSE, domainContainer, conn, LDAPStr, rs, oPerson
Set rootDSE = GetObject("LDAP://RootDSE")
domainContainer = rootDSE.Get("defaultNamingContext")
Set conn = CreateObject("ADODB.Connection")
conn.Provider = "ADSDSOObject"
conn.Open "ADs Provider"
email = "smtp:" & strMail
LDAPStr = "<LDAP://" & DomainContainer & ">;(&(objectCategory=person)(proxyAddresses=" & email & "));adspath,distinguishedName;subtree"
Set rs = conn.Execute(LDAPStr)
If rs.RecordCount = 1 Then
Set oPerson = GetObject(rs.Fields(0).Value)
objfile.writeline "Found matching email address in AD: " & strMail&";"& objRecordSet.Fields("distinguishedName")
WScript.Echo "Found matching email address in AD: " & strMail
objfile.writeline "DN of matching object: " & rs.Fields("distinguishedName")
WScript.Echo "DN of matching object: " & rs.Fields("distinguishedName")
WScript.Echo
intMatch=intMatch+1
flag="Found match"
End If
‘ If no matches are found for any of the search criteria, echo no matches found to the screen
If flag = "" Then
WScript.Echo "No matches found for samAccountName: "& strNTSam
WScript.Echo "No matches found for displayName: "& strDisplayName
WScript.Echo "No matches found for email address: "& strMail
WScript.Echo
End If
‘ Move to the first record of the AD search and continue the search with the next user
objRecordset.MoveFirst
‘ Increment to next user in the spreadsheet.
intRow = intRow + 1
intNumusers = intNumusers + 1
Loop Until objSheet.Cells(intRow, 1).Value = ""
‘ Summarize results of search
WScript.Echo "There were " & intMatch & " matching records out of " & intNumusers & " users in the spreadsheet."
WScript.Echo
WScript.Echo "End of search session. Please review " & LogFile & " for details."
WScript.Echo
objfile.writeline
objfile.writeline "There were " & intMatch & " matching records out of " & intNumusers & " users in the spreadsheet."
objfile.writeline "End of search session " & Now
objfile.WriteLine
‘ Clean up and quit
CleanUp
WScript.Quit
‘ Clean up
Sub CleanUp
intRow=0
intMatch=0
intNumusers=0
flag=""
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
objConnection.Close
objFile.Close
End Sub
