Location: Articles

Syndicate

Friends

Friends

Adventnet Sky
Articles

Articles

Bulk Updates to Active Directory User Attributes

By David Wiseman on Thursday, June 12, 2008 3:52 PM

Describes how to make bulk updates to Active Directory User Attributes using freeware tools (from WiseSoft.co.uk).

Overview

Although you can make certain types of bulk updates using Active Directory Users and Computers, there are many situations where this tool is not suitable for making mass changes to Active Directory User attributes. Often system administrators will resort to writing scripts or purchasing expensive 3rd party tools. There is nothing wrong with using scripts to perform updates - it's a very flexible solution and I encourage all system administrators to learn some basic scripting skills. Also, for some companies the 3rd party tools will easily pay for themselves in the time they save. This article will discuss a freeware alternative from WiseSoft.co.uk.

The tool is called Password Control and it was originally designed as a simple tool to allow helpdesk staff to change passwords. A Bulk Password Control dialog was added to make it easy to reset passwords for large numbers of user accounts. The application also acquired a Bulk Modify component that makes it easy to perform bulk updates to user account attributes, a CSV update tool and a feature that allows you to perform updates using Microsoft Excel. An application that started life as a simple password control tool for helpdesk staff has now become a powerful bulk update tool for system administrators.

Note: If you just need a password tool for helpdesk staff, the other features can be removed

Bulk Modify

Bulk Modify Dialog

The Bulk Modify dialog (above) has been designed to look similar to the user dialog in Active Directory Users and Computers. This makes it very easy to identify the attributes you want to update. Simply click the checkbox next to the attribute you want to update and type the new value. If you want to clear an attribute, click the checkbox a second time so that the checkbox has a shaded tick. Attribute modifications can be based on existing attributes using the powerful XML Placeholders feature. The image above demonstrates how the XML placeholders feature can be used to set the display name to "LastName, FirstName". When you are ready to update, click the OK button.

Identifying the user accounts you want to update is done through the Bulk Password Control dialog. It's really easy to select all the users in a particular OU or Group. There is also a query dialog with some built-in queries to choose from; and if you are feeling adventurous, you can also write your own queries. If you already have a list of user accounts you want to update, you can load them from a text file. Once you have loaded the user accounts you want to update, click the "Modify Attributes" button to launch the "Bulk Modify" dialog above.

Bulk Modify Dialog

The Bulk Modify tool is very flexible and will cover a large number of bulk user update scenarios. Here are just a few examples:

  • Update the username to "FirstName.Surname"
  • Add/Remove/Replace Groups from user group membership
  • Modify Logon Hours.
  • Add/Remove a computer to the "userWorkstations" attribute. (Log On To)
  • Convert the description attribute to ProperCase.
  • Set the initials attribute to the first letter of the first name
  • Bulk load user photos into Active Directory.

Bulk Modify is not suitable for performing updates based on data from an external source. For example, you can set the display name to "FirstName Surname" but it's not possible to populate the FirstName (givenName) and Surname (sn) attributes unless this data already exists in Active Directory. For this type of update, the CSV Update tool is required.

CSV Update

CSV Update dialog

CSV files are text files with columns separated by commas and rows separated by new line feeds. If you are unfamiliar with CSV files, I recommend reading this Wikipedia link. CSV files are a convenient format to use for bulk updates due to it's popularity and the ease at which data can be extracted from various types of data sources to CSV format.

The CSV file simply needs to have a header row that specifies the names of the Active Directory attributes to be updated. One of the columns is used to match the rows in the CSV file to user accounts in Active Directory - normally this would be the username (sAMAccountName) or userPrincipalName attribute. The Active Directory Schema guide is useful for identifying the correct names to use in your CSV file.

Once you've created your CSV file, open the Bulk Password Control application. Select the "CSV Update" option from the "Bulk Modify" menu. The dialog pictured above is displayed. There are four simple steps to complete to perform a CSV Update:

  1. Click the button with the folder icon and select your CSV file.
  2. Use the drop down to select the binding attribute. This is the attribute that is used to match rows in the CSV file to user accounts in Active Directory. Often you will want to use the username (sAMAccountName) or userPrincipalName attributes - these are a good choice because of their uniqueness. A unique attribute is not required though, and there are some situations where you might want to use a single row in a CSV file to update several user accounts.
  3. By default all columns in the CSV file apart from the binding column are used in the update. Review the list of attributes that will be updated and make any required modifications.
  4. The multi-valued separator character can be specified. For example, you could use "www.wisesoft.co.uk;www.activedir.org" to load two values into the url (Other Web Page) attribute. The other option allows you to have a single row in the CSV file update multiple user accounts. By default an error will be logged if two user accounts match the specified binding attribute value.

Once you are ready to update, click the "Update" button.

Edit in Excel

Bulk Password Control dialog - Edit in Exel

The "Edit in Excel" feature allows you to update user attributes using an Excel spreadsheet. This allows you to leverage the powerful data manipulation features of Excel in your bulk user updates. For example you can write a simple formula to set the userPrincipalName attribute to "username@domainname.com". This is also possible using XML Placeholders with Bulk Modify, but the formulas you can write in Excel can be much more complex.

Edit user data in Excel

Once you are finished making modifications, simply save the Excel file and you will be prompted to update.

Rollback

There is an element of risk involved in making bulk updates to Active Directory whatever tool you decide to use. I recommend that you test updates in a QA environment before running on your production domain. I also advise that you ensure that you have a recent backup of your directory as a recovery method of last resort. All the tools mentioned previously have one major advantage over using scripts to update Active Directory - extensive logging and rollback built in as standard.

See it in Action!

This YouTube video gives a quick demonstration of some of the features mentioned in this article.

Other Alternatives

I'm sure that some comparisons will be made to ADModify.NET, as described in a recent article on the website Both apps are free so you can choose the app that best suites the type of update you want to make. ADModify has better support for Exchange attributes. You will find that Bulk Modify supports a number of features/attributes that are not available in ADModify.

Many system administrators resort to scripting to perform mass updates of Active Directory attributes. Writing scripts to perform updates gives you a great deal of flexibility. One of the downsides is that you don't get the extensive logging and rollback features provided by Password Control. This script shows you how you can use a VBScript to update user attributes from a CSV file.

If you are a fan of command line tools, you might like ADMod from joeware.net.

Final Word

WiseSoft.co.uk is my personal website and the application mentioned in this article is freeware. I hope that you like this article and find the application useful for making bulk updates to user account attributes.

Many Thanks,

David Wiseman

 

 

Copyright 2014 ActiveDir.org
Terms Of Use