Bulk User Changes

Whether it is due to organization changes, staff promotion, mobile carrier changes, or some other reason, keeping all of the user detail fields such as Title, Phone number, etc. can be a huge task especially as your organization grows over about 20 people. Luckily, there is an easier way to attack this problem than manually updating all of the user account object manually.

Today's script is an expansion and update of one that I found many years ago, I believe again at http://cwashington.netreach.net/. This script reads a spreadsheet as an input and updates the appropriate accounts and fields based upon the column names and contents. This script can be run from any 32-bit machine in the domain as long as the logged in user has domain admin rights. Neither Office nor Excel is required for execution of the script, but you will need them to create the source spreadsheet.
Creating the Source Spreadsheet
The source spreadsheet can be thought of as a simple table where the rows contain the data to be changed and the column names are what fields need to be changed. All of the data needs to be stored on a worksheet named Sheet1. Somewhere in this sheet, there needs to be a column named sAMaccountName (case insensitive) as this is the key field that all lookups are done from. The sheet should also contain one or more other columns that need to be changed/updated. Several example column names are listed in the comments at the top of the script to help you remember the field names within AD. Once you have your columns setup, simply fill in as many rows as you need with the values you wish the account in that row to be updated with.
Note: If you include multiple columns in your source spreadsheet, all of the columns need to be populated with the values you with the account to have post-execution. Leaving a column blank tells the script to clear the value for that field.
For example, if you have 3 accounts that you wish to update the title field for, your source spreadsheet will look something like the following:

Once you have your source spreadsheet ready, simply run the script with cscript. You will be prompted for the location of your source spreadsheet. Once a valid filename is provided, the script will loop through all of the accounts provided, changing the appropriate fields to the values provided. Debugging and Status output is provided on StdOut.


Script contents below.
'-------------------
' UpdateADUsers.vbs
'
'
' Updates user accounts within AD based on data provided in an Excel ' spreadsheet.
'
' The spreadsheet must have the following field:
' sAMAccountName = JSmith


' And can have any of the following fields:
' cn = John Smith - the same as name
' name = John Smith - the same as cn
' givenName = John
' initials =
' sn = Smith
' displayName = Smith, John
' userPrincipalName = John.Smith@somecompany.com
' distinguishedName = CN=John Smith,OU=...
' company = Some Company
' department = Information Technology
' title = Guru / Geek / Technical Wizard Extraordinare
' description =
' employeeID = 01010
' postOfficeBox = ' P.O. Box
' streetAddress = 1234 Some Blvd
' physicalDeliveryOfficeName = 753B
' l = SomeWhere
' st = HI
' postalCode = 12345
' c = US ' may be X.400 country code GB = United Kingdom
' co = UNITED STATES
' telephoneNumber = 419 555 1234
' facsimileTelephoneNumber = 419 555 1235
' pager = 419 555 1200
' mail = john.smith@somecompany.com
' accountExpires = some date
'
'-------------------


Option Explicit


' define ADO constants I might need 'cuz I'm lazy and didn't use a .wsf
CONST ADS_PROPERTY_CLEAR = 1, ADS_PROPERTY_UPDATE = 2, ADS_PROPERTY_APPEND = 3, ADS_PROPERTY_DELETE = 4


' define all our variables
dim oConnection, oCommand, oRoot, oDomain, oExcelDataFile, oExcelRS
dim sDomain, strSAMAccountName, sAttribsToReturn, sDepth, sFilter, sADsPath
dim iCount, strText, i, r
dim exFN
dim fso


' What attributes do we need back from the inital ADO query, we just need ADsPath because we're
' going to go back and getting the user account explicitly.


sAttribsToReturn = "ADsPath"


' How much of the directory are we going to search - the whole thing.
sDepth = "subTree"


' Setup variables and stuff that I know work so I moved them out of the way


Initialize
set fso=Wscript.CreateObject("Scripting.filesystemobject")


exFN = ""


while exFN = ""
' Prompt for Excel spreadsheet Name
exFN = InputBox("Input File:")
if exFN = "" then
set fso = Nothing
wscript.quit
End if
if not fso.FileExists(exFN) then exFN = ""
wend


set fso = nothing


' Open up the Excel data file we're using for our information.
set oExcelDataFile = CreateObject("ADODB.Connection")
oExcelDataFile.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & exFN & ";" & _
"Extended Properties=""Excel 8.0;"""


' Get all the records from the sheet named 'ADUsers'. To access a specific sheet in the
' work book, use the name of the sheet followed by a dollar sign ($).


Set oExcelRS = oExcelDataFile.Execute("SELECT * FROM [Sheet1$]")


do while NOT oExcelRS.EOF
' Ok, here is the big deal piece of code. We pass it the SAM Account name and the fields
' collection for the current item in the Excel recordset.
UpdateADUser oExcelRS.Fields("sAMAccountName").Value, oExcelRS.Fields


oExcelRS.MoveNext
loop


oExcelRS.Close
oExcelDataFile.Close


wscript.echo "Done."


wScript.Quit


'-------------------
' subroutines
'-------------------


sub Initialize
' This is a bunch of standard initilization stuff that you probably won't
' need to touch unless you want to modify a domain other than the one
' that you're logged in to. That's not anything I've tested so have fun.
' Most of the code is "inspired" from samples on Microsoft's site.


' Create ADO connection object for Active Directory
set oConnection = CreateObject("ADODB.Connection")
oConnection.Provider = "ADsDSOObject"
oConnection.Open "Active Directory Provider"
if Err.Number <> 0 then
BailOnFailure Err.Number, "establishing ADO Connection"
end if


' Create ADO command object for the connection.
set oCommand = CreateObject("ADODB.Command")
oCommand.ActiveConnection = oConnection
if Err.Number <> 0 then
BailOnFailure Err.Number, "establishing ADO Command"
end if


' Get the ADsPath for the domain to search. Essentially this finds a
' domain controller to use for searches.
set oRoot = GetObject("LDAP://rootDSE")
sDomain = oRoot.Get("defaultNamingContext")
set oDomain = GetObject("LDAP://" & sDomain)
if Err.Number <> 0 then
BailOnFailure Err.Number, "on GetObject for domain"
end if


'Build the ADsPath element of the commandtext
sADsPath = "<" & oDomain.ADsPath & ">"
end sub


sub BailOnFailure(ErrNum, ErrText)
dim strText
' report an error and crash
strText = "Error 0x" & Hex(ErrNum) & " " & ErrText
wScropt.Echo "ADSI Error: " & strText
wScript.Quit 1
end sub


function updateADUser(strSAMAccountName, oFields)


' This subroutine does the majority of the work. It takes two parameters,
' the NT4-style SAM account name and a fields collection with the data to change.


dim oRS, oADUser, oUserOU
dim strUserADsPath, strUserOUADsPath, strFieldName, strFieldValue, strOldFieldValue
dim i, pos, bFieldsMatch, bUpdateNeeded


wScript.Echo "Updating " & strSAMAccountName
wScript.Echo "-------------------"


' Build and execute our search command to find the specified sAMAccountName
sFilter = "(&(objectCategory=person)(objectClass=user)(sAMAccountName=" & strSAMAccountName & "))"
oCommand.CommandText = sADsPath & ";" & sFilter & ";" & sAttribsToReturn & ";" & sDepth


set oRS = oCommand.Execute


' Check the number of records returned from the search
select case oRS.RecordCount


case 0:


' No record found - no harm no foul
wscript.Echo "No record found!"


case 1:


' note we haven't made any changes yet
bUpdateNeeded = FALSE


' get ADSPath to the user object from the recordset and get the user
strUserADsPath = oRS.Fields("ADsPath").Value
set oADUser = GetObject( strUserADsPath )


' parse the ADSPath to find the OU/Container the user is in
pos = InStr( strUserADsPath, "OU=" ) ' for users in proper OU's
if pos = 0 then
pos = InStr(3, strUserADsPath, "CN=" ) ' for users in default Users container
end if
if pos = 0 then
pos = InStr( strUserADsPath, "DC=" ) ' for users in no container at all
end if


' store the user OU/Container path in case we need it
strUserOUADsPath = "LDAP://" & Mid( strUserADsPath, pos )




' for each field in the Fields collection, make necessary changes
for i = 0 to oFields.Count - 1


' get the field name/value we're changing
strFieldName = oFields(i).Name
strFieldValue = oFields(i).Value


' make sure the field value is a string
if NOT IsNull(strFieldValue) then strFieldValue = cStr( strFieldValue )


' get the existing ("old") value from AD
strOldFieldValue = Null
on error resume next
strOldFieldValue = Join( oADUser.GetEx( strFieldName ) )
on error goto 0


' determine if an update is necessary by comparing the two values being careful for nulls
if IsNull( strFieldValue ) AND IsNull( strOldFieldValue ) then
' both null, they're equal
bFieldsMatch = TRUE
elseif IsNull( strFieldValue ) OR IsNull( strOldFieldValue ) then
' one is null, they're different
bFieldsMatch = FALSE
elseif strFieldValue = strOldFieldValue then
' they're equal
bFieldsMatch = TRUE
else
' they're different
bFieldsMatch = FALSE
end if


' if the fields aren't equal we need to change AD
if NOT bFieldsMatch then


' change actual null to descriptive text string for display purposes
if IsNull(strOldFieldValue) then strOldFieldValue = "<null>"




' if we're changing the "name" field, we're actually renaming the whole
' user account and it's a bunch more work. The same can be said for the
' OU property of the account, but name and OU seem to be two ways to look
' at the same value, so I chose name 'cuz it's friendlier sounding.
if strFieldName = "name" then


' to rename somebody is more work
wScript.Echo "renaming user from " & strOldFieldValue & " to " & strFieldValue


' save information if we need to
if bUpdateNeeded then oADUser.SetInfo


' drop the current user object
set oADUser = Nothing


' bond to the user's OU
set oUserOU = GetObject( strUserOUADsPath )


' clean up the name field so this will work -
' names can't have commas, so they need to be escaped with a \
strFieldValue = Replace( strFieldValue, ",", "\," )


' move the user account to the new name and get the account back for us to use
set oADUser = oUserOU.MoveHere( strUserADsPath, "CN=" & strFieldValue )


' get rid of the OU object 'cuz we're done with it
set oUserOU = Nothing


' note that we've made changes - although that probably isn't necessary
bUpdateNeeded = TRUE


else


' check the new field value to figure out what we need to do
if IsNull(strFieldValue) then


' The new field value is empty, so delete the existing value
wScript.echo "Delete" & vbTab & strFieldName & " = <null> / was " & strOldFieldValue
oADUser.PutEx ADS_PROPERTY_CLEAR, strFieldName, vbNull


' note that we've made changes
bUpdateNeeded = TRUE


else


' The new field value is not empty, so update AD
wScript.echo "Update" & vbTab & strFieldName & " = " & strFieldValue & " / was " & strOldFieldValue
select case strFieldName
case "LoginScript":
wscript.echo "Modifying Login Script to " & strFieldValue
oADUser.LoginScript = strFieldValue
' case "HomeDirDrive":
' wscript.echo "Modifying HomeDirDrive to " & strFieldValue
' oADUser.put "HomeDirDrive", strFieldValue
' case "HomeDirectory":
' wscript.echo "Modifying Home Directory to " & strFieldValue
' oADUser.put "HomeDirectory", strFieldValue
case else:
wscript.echo "Modifying " & strFieldName & " to " & strFieldValue
oADUser.PutEx ADS_PROPERTY_UPDATE, strFieldName, Array( strFieldValue )
end select
' note that we've made changes
bUpdateNeeded = TRUE


end if


end if


else


' uncomment to display messages about unchanged fields
'wScript.echo "Ignore" & vbTab & strFieldName & " property is unchanged."


end if


next


' if we've made changes, save them by calling .SetInfo
if bUpdateNeeded then
wscript.echo "Commiting Changes"
oADUser.SetInfo
end if


case Else:


' The search returned more than 1 record which shouldn't happen so don't do anything
wScript.echo "Too many records (" & oRS.RecordCount & ") returned!"


end select


' some clean up
set oADUser = Nothing
wScript.Echo ""


end function