23
Dec

PowerShell: Create Active Directory Users Based On Excel Input

Buffer

The last script / message just before I leave on a 2 week skiing holiday! Sooo, make good use of this, because you won’t hear from me earlier than the 8th or 9th of January :)

About the script now. This script will create users in Active Directory based on the settings in the input file (see the Excel / CSV file below this script for an example of the input file used). These settings can, of course, be changed or extended (check this Microsoft Technet Link to get an overview of all the settings that can be set with the PowerShell New-ADUser Cmdlet).

Not only can the file be extended (or decreased) it can also be altered. The column names can be changed (note that you also need to change it in the PowerShell script), the columns can be re-ordered, etc. The script will keep working, because it uses the column names!

Note #1: This script makes use of the Active Directory Module for PowerShell.
Note #2: Feel free to contact me if anything isn’t clear, doesn’t work or to see something changed.

create_ad_users.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93

#######################################################
# AUTHOR  : http://www.hican.net - @hicannet
# DATE    : 28-11-2011
# COMMENT : This script creates new Active Directory users
#           including different kind of properties based
#           on an input_create_ad_users.csv.
Import-Module ActiveDirectory
# Get current directory and set import file in variable
$path     = Split-Path -parent $MyInvocation.MyCommand.Definition
$newpath  = $path + "\import_create_ad_users.csv"
# Define variables
$log      = $path + "\create_ad_users.log"
$date     = Get-Date
$i        = 0
# Change this to the location you want the users to be created in your AD
$location = "OU=Test,OU=Users,DC=hican,DC=net"
# FUNCTIONS
Function createUsers
{
  "Created following users (on " + $date + "): " | Out-File $log -append
  "--------------------------------------------" | Out-File $log -append
  Import-CSV $newpath | ForEach-Object { 
    # A check for the country, because those were full names and need 
    # to be landcodes in order for AD to accept them. I used Netherlands 
    # as example
    If($_.CO -eq "Netherlands")
    {
      $_.CO = "NL"
    }
    # Replace dots / points (.) in names, because AD will error when a 
    # name ends with a dot (and it looks cleaner as well)
    $replace = $_.CN.Replace(".","")
    If($replace.length -lt 4)
    {
      $lastname = $replace
    }
    Else
    {
      $lastname = $replace.substring(0,4)
    }
    # Create sAMAccountName according to this 'naming convention':
    # <FirstLetterInitials><FirstFourLettersLastName> for example
    # hhica
    $sam = $_.Initials.substring(0,1).ToLower() + $lastname.ToLower()
    Try   { $exists = Get-ADUser -LDAPFilter "(sAMAccountName=$sam)" }
    Catch { }
    If(!$exists)
    {
      $i++
      # Set all variables according to the table names in the Excel 
      # sheet / import CSV. The names can differ in every project, but 
      # if the names change, make sure to change it below as well.
      $setpass = ConvertTo-SecureString -AsPlainText $_.Password -force
      New-ADUser $sam -GivenName $_.GivenName -Initials $_.Initials ` 
      -Surname $_.SN -DisplayName $_.DisplayName -Office $_.OfficeName `
      -Description $_.Description -EmailAddress $_.Mail ` 
      -StreetAddress $_.StreetAddress -City $_.L `
      -PostalCode $_.PostalCode -Country $_.CO -UserPrincipalName $_.UPN ` 
      -Company $_.Company -Department $_.Department -EmployeeID $_.ID ` 
      -Title $_.Title -OfficePhone $_.Phone -AccountPassword $setpass
 
      # Set an ExtensionAttribute
      $dn  = (Get-ADUser $sam).DistinguishedName
      $ext = [ADSI]"LDAP://$dn"
      $ext.Put("extensionAttribute1", $_.ExtensionAttribute1)
      $ext.SetInfo()
 
      # Move the user to the OU you set above. If you don't want to
      # move the user(s) and just create them in the global Users
      # OU, comment the string below
      Move-ADObject -Identity $dn -TargetPath $location
 
      # Rename the object to a good looking name (otherwise you see
      # the 'ugly' shortened sAMAccountNames as a name in AD. This 
      # can't be set right away (as sAMAccountName) due to the 20
      # character restriction
      $newdn = (Get-ADUser $sam).DistinguishedName
      Rename-ADObject -Identity $newdn -NewName $_.CN
 
      $output  = $i.ToString() + ") Name: " + $_.CN + "  sAMAccountName: " 
      $output += $sam + "  Pass: " + $_.Password
      $output | Out-File $log -append
    }
    Else
    {
      "SKIPPED - ALREADY EXISTS OR ERROR: " + $_.CN | Out-File $log -append
    }
  }
  "----------------------------------------" + "`n" | Out-File $log -append
}
# RUN SCRIPT
createUsers
#Finished

In the Excel file / Input CSV the following (general) structure was used (the values are example values).

import_create_ad_users.csv

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# LINE1 (the table headings in the Excel)
 
# Already_In_AD,CN,GivenName,Initials,SN,DisplayName,OfficeName,
# Description,Mail,StreetAddress,L,PostalCode,
# CO,UPN,Title-i,Company,Department,ID,ExtensionAttribute1,Title,
# Phone,Manager,Password
 
# LINE2 (first entry, all other entries look the same. As you can see
# there are also tables which aren't used, but are no problem for
# the script to work!
 
# NO,Net.Hican,Hican,H.,Net,"Net, H. - Hican -",Hican Building,
# Hican Net,info@hican.net,Hicanstreet 1,Hicancity,1337,
# Netherlands,info@hican,i-CEO,Hican.net,*,HIC1337,Staff,CEO,
# +0000000000,,IDDQD