Messing up with AD PowerShell in a school environment – How to do it right.

In merchandising, back to school is the period in which students and their parents purchase school supplies and apparel for the upcoming school year. Yeah, cute stuff ha? Well, not exactly - If you are an engineer, the summer’s out period is a migraines beginning.


To relieve this pain, I would love to share with you my experiences in managing multiple Active Directory accounts that every year change group memberships as users (a.k.a. students) progress and move to new (promising;) classes.In order to demonstrate a similar situation I have created a lab in an Azure VM. My domain is siebenazurelab.gr and in this domain I have preloaded some users in OUs. Like most schools. I have created a tree structure that represents the Classes.

In this relatively small school I’ve just built, manager said we could not afford more than 3 classes with 3 subclasses in each class.

In a similar environment you hope you get an excel file with the new users that must be imported. The excel file should look  like this:

So now you have to import the users to AD…

The PowerShell Command to complete this task is called New-ADUser and gives us the ability to import to AD certain information like:
• Name
• City
• Home Phone

And many more other options. You can read more about these options on the MS official technet article here. The first task is to manipulate the excel file in a way to produce a .csv file that can be imported to AD.

Excel is your friend in this case, as you can easily create the following columns. In our simple school case we will only import information about first name, last name, username, user principal name, and e-mail.

WAIT! Did I say USERNAME? YES We have to fill a different username for every AD account that cannot contain spaces, special characters etc., so it’s kindatricky to choose a correct one. Usually most organizations have a pattern to assign usernames to their users and in this example, I have decided to choose a common one that it is like: take the first letter of the first name + “.” + Surname (Example m.haggar@sieben.gr). To achieve that I used the Excel button “Text to Columns” to separate the first name from the last name.Then in order to get the first letter of the first name, I used the function “LEFT()” and I added a dot (.) between words using (&). All of these must be included in the “TRIM()” which removes spaces between words. In your case if you have users with multiple first names or multiple last names you have to take decisions on how the usernames should be and you just hope that you get for input an excel file with first and last names already separated. I always recommend to my clients to keep it simple and do not have very long usernames. To meet the pre-windows 2000 requirement for user logon name in AD you must have usernames shorter than 20 characters. I forgot to say that I prefer all letters in username to be lowercase so I have added the “LOWER()” function.

Other columns that must be filled are A) User Principal Name which is the username with the domain name that the user logs-on and B)The e-mail address that is just a string for the SMTP address. All can be easily filled using Excel functions. See below:

Looking good…

So now the question is…

Am I ready to import my new users to AD?

The answer is


You always have to keep in mind and thorough check that Usernames are unique and you don’t have any duplicates in the same OU.

So let’s see how we can be sure of that… We need to export all the AD users and put them in an excel file. Then in the last rows of the Excel file we can add the users from the file that we have created before and then using Excel’s Conditional formatting we can highlight duplicates values. If there are conflicts indeed, we must manually correct the names/usernames to clean things up.

(Note that all the Cmdlets that the PowerShell version that I am using with the addition of the Active Directory Module is 4. You can find your PowerShell version with the Cmdlet: $PSVersionTable.PSVersion. I strongly recommend to get the latest RSAT tools from Microsoft in order to have access to the latest Cmdlets)
To export all the users from the AD you can use the following cmdlet in PowerShell - Active Directory Module:

Get-ADUser -SearchBase "DC=siebenazurelab,DC=gr" -filter * |Select-Object name,userprincipalname| Export-Csv -Path .\UserExport.csv -Encoding UTF8

I am sure that there are many other options to achieve this but my scenario works just fine. The csv created can be easily imported and translated into columns using (guess what…) Excel. Just change the domain name on the above PowerShell Cmdlet to match your domain name.

Just one more detail worth mentioning (before proceeding to the long anticipated user import) is that if you look carefully on the above Cmdlet you will find that I am using the Encoding options that the Cmdlet Export-Csv provides. If you have experience with environments where some administrators use non-English characters in AD you know that Greek or other types of characters can be a headache to the administrator. Hopefully the latest version of PowerShell provides the option to select the encoding and I am choosing UTF-8 to have a successful export or import. So throughout this demo when I am using Import-Csv or Export-Csv it is followed by the –encoding switch.

Assuming everything works and you’ve dealt with the duplicates once and for all, the next task is to save the excel file into .csv format. After that, for the same reasons I mentioned above, try using Notepad to just change the encoding to UTF-8.

Just for this demo lab, I have created a new folder in the server C: drive with the name data import and my goal is to add the new users to a specific temporary OU and then place them to the correct ones.

To import users from a csv file I use the following cmdlet in PowerShell - Active Directory Module:

Import-Csv -path c:\dataimport\newstudents.csv -Encoding UTF8 -Delimiter ";" | ForEach-Object {New-ADUser -Name $_.DisplayName -GivenName $_.Firstname –Surname $_.Lastname -UserPrincipalName $_.Userprincipalname -SamAccountName $_.UsernameEmailAddress $_.MailAddress –path "OU=NewStudents,OU=SchoolDemo,DC=siebenazurelab,DC=gr"}

Now,  see below the users created in the AD:

As you can see in the screenshot all users have been imported to the chosen temporary OU. All the users are disabled by default because there is no Password set for their account.

If you want to set a temporary password for the users in the above command you can add the -AccountPassword switch to the New-ADUser Cmdlet. When using the –AccountPassword option and you want to set a password as plain text in the Cmdlet you must use ConvertTo-SecureString and the –AsPlainText option. In addition you can add the –enabled switch set to $true to pre-enable the users. The Cmdlet will look like this:

Import-Csv -path c:\dataimport\newstudents.csv -Encoding UTF8 -Delimiter ";" | ForEach-Object {New-ADUser -Name $_.DisplayName -GivenName $_.Firstname -Surname $_.Lastname -UserPrincipalName $_.Userprincipalname –SamAccountName $_.Username -EmailAddress $_.MailAddress –path "OU=NewStudents,OU=SchoolDemo,DC=siebenazurelab,DC=gr" -AccountPassword (ConvertTo-SecureString 1stday@school!! -AsPlainText -Force) -Enabled $true}

 Users created in the AD should look like this:

Congrats! We have completed a successful bulk user creation in the AD.

The next tasks that your admin should do are:

• Move users to correct OU
• Add users to specific security group

At this point, I would like to share with you a couple of PowerShell Cmdlets that you may find useful.
I usually move all the students to temporary OUs before assigning them to the new ones, so the first Cmdlet moves all users from an OU to a different one:
Get-ADUser -Filter * -SearchBase "OU=Classes,OU=SchoolDemo,DC=siebenazurelab,DC=gr" | Move-ADObject -TargetPath "OU=Test,DC=siebenazurelab,DC=gr"

The second Cmdlet removes all users from the AD Groups specified in a .csv file. In the .csv you have to enter the path of the security in active directory. So it will look like this:

Now is time to proceed   to moving users to the correct OU and putting them in a specific security group. As you’ve seen before we were only given the name and the class for each student. Since we have created the username, now we have to use the class column to produce the Group and the OU values.

From the above excel file we can produce a .csv that can be used for both Cmdlets. The first command to move the users to the correct OU will look like this:

Import-csv -path c:\DataImport\GroupandOU.csv -encoding UTF8 -delimiter ";" | Foreach-Object {Get-ADUser $_.Username| Move-ADObject -TargetPath $_.OU}

The Cmdlet has successfully moved the users to the desired OUs:

Going on we willadd users to the desired security groups. The Cmdlet will be like this:

Import-csv -path c:\DataImport\GroupandOU.csv -encoding UTF8 -delimiter ";" | Foreach-Object {Add-ADGroupMember -identity $_.Group -members $_.Username}

After the successfully executed Cmdlet you can see that all the users have been added to the correct OU

That’s all!!!

Now all the new users have been added to the correct OU and they have the correct group membership. School is ready to begin!

*This was a simple scenario with easy names and simple OUs and Groups! In real life it is much more complicated… but always remember that PowerShell saves you by the bell!