This spreadsheet is for the bulk changing of multivalued Active Directory objects. Individual values in a multivalued attribute collection can be added or removed. The Objects will not be accessed directly, the spreadsheet will generate powershell scripts that can be executed by administrators.

The control buttons on the left hand side perform the following function:

Button	Functionality
Read	Will  read the objects specified in column E sequentially populating the LDAP attributes specified as row headers, column F and Higher. This provides the same functionality as the AD_Query spreadsheet.
Add	Will generate two Excel sheets called "Update.ps1" and "Rollback.ps1". Update.ps1 contains a powershell script that can be used by an administrator, The script will contain a series of commands that update the objects specified in column D and add the attribute values specified in columns E. Rollback.ps1 will contain a complete reversal script that reverts the objects back to their original state.
Remove	Will generate two Excel sheets called "Update.ps1" and "Rollback.ps1". Update.ps1 contains a powershell script that can be used by an administrator, The script will contain a series of commands that update the objects specified in column D and remove the attribute values specified in columns E. Rollback.ps1 will contain a complete reversal script that reverts the objects back to their original state.
Clear	Clears the contents of columns F and higher leaving the column headers intact

When reading Objects specified in column D objects will be read sequential starting in cell D2 and continuing in column D until an empty cell is found.

Objects can by specified in cell "B15" by Either;

• Common Name
• Samaccountname
• Distinguished name

The object attribute to be updated is specified in "B16", this should be a multivalued attribute

Typically the multi valued attributes that will be read in this way would be proxyaddresses and memberof. Proxyaddresses is a list of user email addresses in different mail formats. Memberof is the group membership of a user as a list of distinguished names.

Please note: The memberof attribute does not contain a users primary group  this is stored on the PrimaryGroupID attribute and is normally the integer 513 which is the RID of "Domain Users"

 

Updating objects

Populate the names of the objects in column D. Complete the multivalued attribute values that are to be added or removed in column E. If the objects need to be validated select read.

Once the spreadsheet is populated select the update button.

The update.ps1 and rollback.ps1 sheets will be deleted and recreated with powershell scripts that can be used to update objects.

 

Generating and executing poweshell scripts

Once the spreadsheet is populated with objects and attribute values select the Update button.

The tabs."Update.ps1" and "Rollback.ps1" will be created or if already there deleted and recreated.

Send the spreadsheet to your AD administrator to perform the following activities:

Copy the scripts to text files

Within the "update.ps1" sheet highlight column A, right click and select copy.

Create a new text file e.g. c:\script\update.ps1

Paste the contents of column A into the text file, either use notepad or a powershell editor.

Save the file and repeat for "rollback.ps1"

The powershell script update.ps1 can now be run at a command line or within a powershell editor

Once update.ps1 is run, there will be no output on the command line if the script executes without error. Errors will be outputted in red text. The script will output the csv file into its execution directory called Result.csv.

For each object processed the following results will be outputted:

Column	Result
Object	The name of the object that has been processed in the script
Error	An integer counting the number of errors processing the object in the script, If 0 Then No Error
Description	Description , The description of the last error processing the object in the script, If Empty then No Error

If a rollback is required then the rollback.ps1 script can be run.

• < Prev
• Next >