Update active directory user attributes from a CSV File. (See Notes section for additional information and instructions)
Notes
Introduction
This script can be used to update Active Directory User attributes from a CSV file. One column in the CSV file is used to match rows in the CSV file to user accounts in Active Directory and the other columns are used to update attributes. Normally the Pre-Windows 2000 username (sAMAccountName) attribute is used to match rows in the CSV file to user accounts in Active Directory, but you can easily modify the script to use a different attribute if required. For example, you might want to identify the user account you want to update in the CSV file by email address (mail attribute) rather than username. As an alternative to this script, you might also to consider using the free Bulk AD Users application available on this website. Bulk AD Users supports updating users via CSV file and it includes some other tools for updating users accounts such as the Edit In Excel and Bulk Modify features. The Bulk AD Users application can make it easier for you to apply the update and it also produces an XML log file of the update which can be used to rollback changes. That’s not to say that Bulk AD Users is a complete replacement for this script as the script allows you to customise the code for your own requirements, and it can also be automated via task manager if required.
Bulk Update Considerations
There is always an element of risk involved in performing bulk updates to Active Directory. Please be sure to test updates in a QA environment before applying them to your live domain. Also, it’s a good idea to have a plan of action prepared should something go wrong with the update. Taking a backup of your Active Directory database is a sensible precaution, and it also pays to be familiar with backup and restore techniques. If you have a large domain or a domain that is connected over slow WAN links, you might also need to consider the impact of replication traffic when performing bulk updates. Bulk updates can save an enormous amount of time and they normally run very smoothly if planned correctly.
Setup
The first step in updating user accounts from a CSV file is to produce a valid CSV input file for the script. A CSV file is simply a text file containing rows and columns of data like a spreadsheet and they can be read and written to by spreadsheet applications like Microsoft Excel. There is normally a way to extract data from databases into CSV text files. It’s also possible to produce a text file manually by using an application such as notepad. See here for more information on producing CSV files. As well as taking care to ensure that the CSV file you have produced is valid, you also need to ensure that the CSV file includes a header row. The header row is used by the script to identity the column names and the column names should correspond to the attribute names used by Active Directory. The attribute names are not always obvious, so please take care to ensure that you are using the correct names. For example, the surname attribute is “sn” and the first name attribute is “givenName”. As mentioned previously, the username (pre windows 2000) is used by default to match rows in the CSV file to the user accounts in Active Directory. This attribute name is “sAMAccountName” and must be included in your CSV file (unless you plan to use a different search attribute). If you don’t want to edit the script, the CSV file should be called “usermod.csv” and it should be located on the root of the “C:\” drive. If you want to use a different filename or location, edit the strCSVFolder and strCSVFile attributes in the setup section of the script. Change the strSearchAttribute if you want to use a different attribute for matching rows in the CSV file to users in Active Directory (e.g. userPrincipalName or mail attribute). The attribute you choose for the strSearchAttribute should be unique as the script expects a maximum of 1 user to be returned for any attribute value. If multiple users are found with the same attribute value, no user accounts will be updated. The configuration of the script and the CSV file is now complete. The script is designed to run in cscript mode (described in this article). If you double click the script, the script will run in wscript mode – the script will work ok, but you will get a lot of annoying prompts. To run in cscript mode, open a command prompt and change the current directory to the one that contains the script. Use the following command to run the script: cscript csv_user_update.vbs The results of the script will be output to the console window. If you prefer to have the results stored in a text file, use the following command instead: cscript csv_user_update.vbs >> results.txt
Extracting CSV Data From Active Directory
If you would like to extract data as a CSV file from Active Directory as a base for your update, please refer to the scripts below:
Extract User Data To a CSV File
Extract User Data To a CSV File (Inc Terminal Services Attributes)
Custom Attribute Handling
The script should support most single-valued attributes by default. In addition, special handling has been added for the following attributes:
| Attribute Name | Description |
| cn | This attribute is the “Name” column that is shown in Active Directory Users & Computers and it’s a standard attribute in AD. The cn (common name) forms part of an objects distinguished name. This attribute must be unique within it’s parent container (organizational unit). This attribute can be read the same as any other attribute, but to change the value you need to use the “MoveHere” method. |
| TerminalServicesProfilePath | Terminal Services attributes are stored in the “userparameters” attribute in Active Directory. Modifying this attribute directly isn’t recommended, but it is possible to modify these attributes via the IADsTSUserEx interface. The TsUserEx.dll is required to modify these values, otherwise you will get a “Object doesn’t support this property or method” error. To fix this you will need to install the Windows 2000/2003 Administration Tools Pack. |
| TerminalServicesHomeDirectory | |
| TerminalServicesHomeDrive | |
| AllowLogon | |
| Password | Microsoft doesn’t provide any access to user passwords stored in Active Directory via ADSI, either in encrypted or unencrypted format. It is possible to change a password by calling the “SetPassword” method though. The script won’t output the previous value as it does with other attributes. Note: Please take care to ensure passwords meet length and complexity requirements. |
| Manager_sAMAccountName | The manager attribute in active directory requires a distinguished name. e.g. CN=David.Wiseman,OU=MyUsers,DC=WiseSoft,DC=co,DC=UKTo make this attribute easier to modify, you can specify “Manager_sAMAccountName” as the attribute name in the CSV header instead of “manager”. This allows you to use the username (Pre Windows 2000) instead of the manager’s distinguished name. e.g.David.Wiseman |
You might want to refer to the Active Directory Schema Guide to help identify the correct attribute names for other attributes. Note: Bulk AD Users provides better support for multi-valued attributes if required.
Troubleshooting
Provider cannot be found error
The script uses the Microsoft.Jet.OLEDB.4.0 provider. If you are using x64, you will need to use the 32bit script host for this provider to be available. To do this, open a command prompt and navigate to the “SysWow64” folder in your Windows directory. e.g.
C:\Windows\SysWOW64\ You can now run cscript from this location, passing the full location of your script file. e.g. cscript “C:\update_users_csv.vbs”
Data Not Imported as Expected
This script uses the Microsoft.Jet.OLEDB.4.0 provider for parsing the CSV file. Sometimes the data type gets mis-interpreted and the data is not imported as expected. An example of this is where you are updating the telephoneNumber attribute and your telephone numbers start with a leading zero. The provider is likely to interpret this as a number rather than a string which results in the leading zeros getting truncated. To get around this issue, you can create a schema.ini file. If you have a CSV file consisting only of sAMAccountName and telephoneNumber columns, your schema.ini file might look something like this: [usermod.csv]
ColNameHeader=True
Format=CSVDelimited
Col1=sAMAccountName Text
Col2=telephoneNumber Text For more information on schema.ini files, please refer to this article.
Version History
| Version | Date | Notes |
| 1.1 | 2009-11-19 | Changed script to use CSV header Added support for terminal services attributes Added support for changing passwords |
| 1.0 | 2008-01-21 | Initial release. |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 |
OPTION EXPLICIT ' Variables must be declared ' ************************************************* ' * Instructions ' ************************************************* ' Edit the variables in the "Setup" section as required. ' Run this script from a command prompt in cscript mode. ' e.g. cscript usermod.vbs ' You can also choose to output the results to a text file: ' cscript usermod.csv >> results.txt ' ************************************************* ' * Constants / Decleration ' ************************************************* CONST adOpenStatic = 3 CONST adLockOptimistic = 3 CONST adCmdText = &H0001 CONST ADS_PROPERTY_CLEAR = 1 DIM strSearchAttribute DIM strCSVHeader, strCSVFile, strCSVFolder DIM strAttribute, userPath DIM userChanges DIM cn,cmd,rs DIM objUser DIM oldVal, newVal DIM objField DIM blnSearchAttributeExists ' ************************************************* ' * Setup ' ************************************************* ' The Active Directory attribute that is to be used to match rows in the CSV file to ' Active Directory user accounts. It is recommended to use unique attributes. ' e.g. sAMAccountName (Pre Windows 2000 Login) or userPrincipalName ' Other attributes can be used but are not guaranteed to be unique. If multiple user ' accounts are found, an error is returned and no update is performed. strSearchAttribute = "sAMAccountName" 'User Name (Pre Windows 2000) ' Folder where CSV file is located strCSVFolder = "C:\" ' Name of the CSV File strCSVFile = "usermod.csv" ' ************************************************* ' * End Setup ' ************************************************* ' Setup ADO Connection to CSV file SET cn = CREATEOBJECT("ADODB.Connection") SET rs = CREATEOBJECT("ADODB.Recordset") cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & strCSVFolder & ";" & _ "Extended Properties=""text;HDR=YES;FMT=Delimited""" rs.Open "SELECT * FROM [" & strCSVFile & "]", _ cn, adOpenStatic, adLockOptimistic, adCmdText ' Check if search attribute exists blnSearchAttributeExists=false FOR EACH objField in rs.Fields IF UCASE(objField.Name) = UCASE(strSearchAttribute) THEN blnSearchAttributeExists=true END IF NEXT IF blnSearchAttributeExists=false THEN MSGBOX "'" & strSearchAttribute & "' attribute must be specified in the CSV header." & _ VbCrLf & "The attribute is used to map the data the csv file to users in Active Directory.",vbCritical wscript.quit END IF ' Read CSV File DO Until rs.EOF ' Get the ADsPath of the user by searching for a user in Active Directory on the search attribute ' specified, where the value is equal to the value in the csv file. ' e.g. LDAP://cn=user1,cn=users,dc=wisesoft,dc=co,dc=uk userPath = getUser(strSearchAttribute,rs(strSearchAttribute)) ' Check that an ADsPath was returned IF LEFT(userPath,6) = "Error:" THEN wscript.echo userPath ELSE wscript.echo userPath ' Get the user object SET objUser = GETOBJECT(userpath) userChanges = 0 ' Update each attribute in the CSV string FOR EACH objField in rs.Fields strAttribute = objField.Name oldval = "" newval = "" ' Ignore the search attribute (this is used only to search for the user account) IF UCASE(strAttribute) <> UCASE(strSearchAttribute) AND UCASE(strAttribute) <> "NULL" THEN newVal = rs(strAttribute) ' Get new attribute value from CSV file IF ISNULL(newval) THEN newval = "" END IF ' Special handling for common-name attribute. If the new value contains ' commas they must be escaped with a forward slash. IF strAttribute = "cn" THEN newVal = REPLACE(newVal,",","\,") END IF ' Read the current value before changing it readAttribute strAttribute ' Check if the new value is different from the update value IF oldval <> newval THEN wscript.echo "Change " & strAttribute & " from '" & oldVal & "' to '" & newVal & "'" ' Update attribute writeAttribute strAttribute,newVal ' Used later to check if any changes need to be committed to AD userChanges = userChanges + 1 END IF END IF NEXT ' Check if we need to commit any updates to AD IF userChanges > 0 THEN ' Allow script to continue if an update fails ON ERROR RESUME NEXT err.clear ' Save Changes to AD objUser.setinfo ' Check if update succeeded/failed IF err.number <> 0 THEN wscript.echo "Commit Changes: Failed. " & err.description err.clear ELSE wscript.echo "Commit Changes: Succeeded" END IF ON ERROR GOTO 0 ELSE wscript.echo "No Changes" END IF END IF userPath = "" rs.MoveNext LOOP ' Cleanup rs.close cn.close ' ************************************************* ' * End of script ' ************************************************* ' ************************************************* ' * Functions ' ************************************************* ' Reads specified attribute and sets the value for the oldVal variable SUB readAttribute(BYVAL strAttribute) SELECT CASE LCASE(strAttribute) CASE "manager_samaccountname" ' special handling to allow update of manager attribute using sAMAccountName (UserName) ' instead of using the distinguished name DIM objManager, managerDN ' Ignore error if manager is null ON ERROR RESUME NEXT managerDN = objUser.GET("manager") ON ERROR GOTO 0 IF managerDN = "" THEN oldVal="" ELSE SET objManager = GETOBJECT("LDAP://" & managerDN) oldVal = objManager.sAMAccountName SET objManager=Nothing END IF CASE "terminalservicesprofilepath" 'Special handling for "TerminalServicesProfilePath" attribute oldVal=objUser.TerminalServicesProfilePath CASE "terminalserviceshomedirectory" 'Special handling for "TerminalServicesHomeDirectory" attribute oldVal = objUser.TerminalServicesHomeDirectory CASE "terminalserviceshomedrive" 'Special handling for "TerminalServicesHomeDrive" attribute oldVal=objUser.TerminalServicesHomeDrive CASE "allowlogon" ' Special handling for "allowlogon" (Terminal Services) attribute ' e.g. 1=Allow, 0=Deny oldVal=objUser.AllowLogon CASE "password" ' Password can't be read, just return **** oldVal="****" CASE ELSE ON ERROR RESUME NEXT ' Ignore error if value is null ' Get old attribute value oldVal = objUser.GET(strAttribute) ON ERROR GOTO 0 END SELECT END SUB ' updates the specified attribute SUB writeAttribute(BYVAL strAttribute,newVal) SELECT CASE LCASE(strAttribute) CASE "cn" 'Special handling required for common-name attribute DIM objContainer SET objContainer = GETOBJECT(objUser.Parent) ON ERROR RESUME NEXT objContainer.MoveHere objUser.ADsPath,"cn=" & newVal ' The update might fail if a user with the same common-name exists within ' the same container (OU) IF err.number <> 0 THEN wscript.echo "Error changing common-name from '" & oldval & "' to '" & newval & _ "'. Check that the common-name is unique within the container (OU)" err.clear END IF ON ERROR GOTO 0 CASE "terminalservicesprofilepath" 'Special handling for "TerminalServicesProfilePath" attribute objUser.TerminalServicesProfilePath=newVal CASE "terminalserviceshomedirectory" 'Special handling for "TerminalServicesHomeDirectory" attribute objUser.TerminalServicesHomeDirectory=newVal CASE "terminalserviceshomedrive" 'Special handling for "TerminalServicesHomeDrive" attribute objUser.TerminalServicesHomeDrive=newVal CASE "allowlogon" ' Special handling for "allowlogon" (Terminal Services) attribute ' e.g. 1=Allow, 0=Deny objUser.AllowLogon=newVal CASE "password" ' Special handling for setting password objUser.SetPassword newVal CASE "manager_samaccountname" ' special handling to allow update of manager attribute using sAMAccountName (UserName) ' instead of using the distinguished name IF newVal = "" THEN objUser.PutEx ADS_PROPERTY_CLEAR, "manager", NULL ELSE DIM objManager, managerPath, managerDN managerPath = GetUser("sAMAccountName",newVal) IF LEFT(managerPath,6) = "Error:" THEN wscript.echo "Error resolving manager DN:" & managerPath ELSE SET objManager = GETOBJECT(managerPath) managerDN = objManager.GET("distinguishedName") SET objManager = NOTHING objUser.Put "manager",managerDN END IF END IF CASE ELSE ' Any other attribute ' code to update "normal" attribute IF newVal = "" THEN ' Special handling to clear an attribute objUser.PutEx ADS_PROPERTY_CLEAR, strAttribute, NULL ELSE objUser.put strAttribute,newVal END IF END SELECT END SUB ' Function to return the ADsPath of a user account by searching ' for a particular attribute value ' e.g. LDAP://cn=user1,cn=users,dc=wisesoft,dc=co,dc=uk FUNCTION getUser(BYVAL strSearchAttribute,strSearchValue) DIM objRoot DIM getUserCn,getUserCmd,getUserRS ON ERROR RESUME NEXT SET objRoot = GETOBJECT("LDAP://RootDSE") SET getUserCn = CREATEOBJECT("ADODB.Connection") SET getUserCmd = CREATEOBJECT("ADODB.Command") SET getUserRS = CREATEOBJECT("ADODB.Recordset") getUserCn.open "Provider=ADsDSOObject;" getUserCmd.activeconnection=getUserCn getUserCmd.commandtext="<LDAP://" & objRoot.GET("defaultNamingContext") & ">;" & _ "(&(objectCategory=person)(objectClass=user)(" & strSearchAttribute & "=" & strSearchValue & "));" & _ "adsPath;subtree" SET getUserRs = getUserCmd.EXECUTE IF getUserRS.recordcount = 0 THEN getUser = "Error: User account not found" ELSEIF getUserRS.recordcount = 1 THEN getUser = getUserRs(0) ELSE getUser = "Error: Multiple user accounts found. Expected one user account." END IF getUserCn.close END FUNCTION |
