This spreadsheet is for the bulk changing or creation of Active Directory objects. 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.
Update	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 object with the attribute values specified in columns F and higher. 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 E objects will be read sequential starting in cell E2 and continuing in column E until an empty cell is found.

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

• Common Name
• Samaccountname
• Distinguished name

The object category is specified in "B16", this can either be;

• User
• Group
• Computer
• Contact

There are 2 update modes either Create object "Yes" or "No" specified in "B17".

 

Updating objects

Populate the names of the objects in column E and the names of the attributes in row 1. If the objects need to be validated select read.

Complete the attribute column values to those that are to be updated. Empty cells will be ignored, if an attribute needs to be cleared then type NULL in the cells corresponding to the object that needs the attribute cleared.

Once the spreadsheet is populated set Create object B17 to no and 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.

Creating objects 

Set B15 to the naming format for creating objects either Common Name or Distinguished Name can be used. Samaccountname will just be as Common Name.

Set B16 to the object category to be created

Set B17 Create object to "Yes"

Set B18 to the OU where the new objects are to be created, this will be ignored if Distinguished Names are specified in B15

Populate the names of the objects in column E and the names of the attributes in row 1. If the objects need to be validated as not existing select read

Complete the attribute column values to those that are to be created

Creating Users 

User Accounts have 3 main names within AD, Common Name, Universal Principal Name and Samaccountname. These names must be unique and it is best practice for them to match. In large corporations these often align to HR number or Payroll which is unique within the organisation.

Name	LDAP	Uniqueness	Description	Format
Common Name	CN	OU	The object name within the AD	String e.g. 123456
Universal Principal Name	userPrincipalName	AD Forest	The users logon name	String e.g. 123456
Samaccountname	Samaccountname	Domain	Used for old applications and servers NTLM	String @ FQDN e.g. This email address is being protected from spambots. You need JavaScript enabled to view it.

 

In order to change a user object to a logon account it must be enabled accounts cannot be enabled unless they have a password that meets the domain complexity requirements policy. In most Active Directories this is a minimum of 8 characters and in secure environment this will need to include at least 1 number 1 lowercase character and 1 uppercase character.

The minimum attribute requirement for creating a disabled user object is samaccountname. Place this attribute in the first column. And all other attributes after. In most organisations the following attributes will typically be used to create user accounts.

LDAP	Description
CN	Common Name of the user object (Mandatory)
samaccountname	Pre windows 2000 name of the object (Mandatory)
password	Must meet domain complexity requirements
accountdisabled	Set to FALSE to enable a user account, must have password set
givenname	The first name of the user
sn	The surname of the user
displayname	The full name of the users as it appears in the GAL e,g. last, first
userPrincipalName	The logon name of the users user @ domain
pwdLastSet	New user accounts should have pre expired password set to 0
profilepath	The UNC path to the users roaming profile
homedrive	The letter followed by a colon of their homedrive e.g. H:
homedirectory	The UNC path to the users home drive

If the users require access to terminal services then terminal services attributes can be added.

If the users require an exchange mailbox a separate process is performed for mailbox enabling.

Creating Contacts

Creating contacts is more straightforward than creating users, there are no mandatory attributes other than CN, typically the following attributes would be populated:

• givenname
• sn 
• displayname

 

If the contacts require mail enabling mailbox a separate process is performed.

 

Creating Groups 

When creating groups there are 3 mandatory attributes Common Name, Samaccountname and GroupeType. It is best practice to make the samaccountname and the common name match. If the name accurately describes what the group is for then it is easier for administrators to decide whether or not other objects should be added to the group.

 e.g.

Sales_Share_modify – Modify access to the sales share

Managers_Mailing_List – A mail distribution list for managers

The LDAP attribute grouptype is stored on the attribute as an integer however the spreadsheet when reading and updating will convert that to/from the name.

Name	Integer
Domain local Security Group	-2147483644
Global Security Group	-2147483646
Universal Security Group	-2147483640
Domain local Distribution Group	4
Global Distribution Group	2
Universal Distribution Group	8

Security groups are for access control lists to resources such as servers shares and printers. Distribution groups are contained purely within the Active directory and are primarily used for messaging purposes

Security Groups:

When choosing a grouptype it is best practice add users to global groups and nest the the global groups into local groups which are in the access control list for the resource.

E.g. Users-> Global Group -> Local Group -> Resource

Universal Security Groups are forest wide and should only be used in special purpose

Type	When to use
Local Security Group (not created by this spreadsheet)	Within a server not within the AD, use in access control lists when an application is built on a specific set of servers such as IIS and SQL
Domain local Security Group	Within a domain, use in access control lists for domain infrastructure resources such as file shares and printers, where the resources can be migrated between servers
Global Security Group	Within a domain, nest into local groups and populate with users that require access to the resources
Universal Security Group	Within an AD Use for specialist purpose

Distribution Groups:

Distribution groups are predominantly for messaging purposes as messaging systems are forest wide, some exchange administrators make all distribution groups universal.

A typical set of attributes to specify when creating groups

LDAP	Description
CN	Common Name of the group object (Mandatory)
samaccountname	Pre windows 2000 name of the object (Mandatory)
Grouptype	Must be specified (Mandatory)
Description	A description of the groups purpose

Please note: - it is possible to mail enable security groups giving them both security and messaging purpose.

 

Creating Computers 

Computer objects are created by administrators so an engineer can at a later date join a workstation or server to the domain with a less privileged account. When creating a computer object there are 3 mandatory attributes Common Name, Samaccountname and Useraccountcontrol.

It is normal for the samaccountname to be the same as the common name with a $ appended.

e.g. if the CN is Comp1 then Samaccountname should be Comp1$

User account control must be set for computer objects to differentiate the samaccountname from user and group objects, this will be typically 4096

A Typical set of attributes when creating a Computer object

LDAP	Description
CN	Common Name of the object (Mandatory)
samaccountname	This should be the same as CN appended with a "$" (Mandatory)
Useraccountcontrol	Set to 4096 for standard member workstations or servers(Mandatory)
Description	A description of the computers purpose

  

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.

• Next >