This spreadsheet is for the bulk amending of Active Directory group membership. The groups will not be amended 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 group in column D and validate whether the object in column E is a member. |
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 groups specified in columns D and adds the objects specified in column E to their members attribute. 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 groups specified in columns D and removes the objects specified in column E to their members attribute. Rollback.ps1 will contain a complete reversal script that reverts the objects back to their original state. |
Clear |
Clears the results from column F |
When reading group 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
For fastest results reading groups and their members use DN
Updating objects
Populate the names of the group objects in column D and the name of the corresponding member object to be added or removed in column E.
Validate the objects by selecting the "Read" button.
Result |
|
True |
The member object is a member of the group object |
False |
The member object is not a member of the group object |
Object Not found |
Either the group or member object cannot be found in the AD |
Error |
There is an error accessing the group or member |
Once the spread sheet is populated with groups and members to be added or removed then select the "Add" button to create an addition script or select the "Remove" button to create a removal script.
Generating and executing powershell scripts
Once the spreadsheet is populated and Add or remove selected then "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.
Selecting read can validate if the addition or removal has been successful.
- Details
- Category: Active Directory
- Published: 04 November 2013