· 10 years ago · Sep 13, 2016, 09:02 AM
1# ERROR REPORTING ALL
2Set-StrictMode -Version latest
3
4#----------------------------------------------------------
5# LOAD ASSEMBLIES AND MODULES
6#----------------------------------------------------------
7Try
8{
9 Import-Module ActiveDirectory -ErrorAction Stop
10}
11Catch
12{
13 Write-Host "[ERROR]`t ActiveDirectory Module couldn't be loaded. Script will stop!"
14 Exit 1
15}
16
17#----------------------------------------------------------
18#STATIC VARIABLES
19#----------------------------------------------------------
20$path = Split-Path -parent $MyInvocation.MyCommand.Definition
21$newpath = $path + "\create_user.xlsx"
22$FileName = $newpath
23$log = $path + "\create_ad_users.log"
24$date = Get-Date
25$addn = (Get-ADDomain).DistinguishedName
26$dnsroot = (Get-ADDomain).DNSRoot
27$i = 1
28
29
30#----------------------------------------------------------
31#Import-Excel
32#----------------------------------------------------------
33Function Import-FromExcel
34{
35Param(
36[Parameter(Mandatory=$true)]
37[String]$WorkbookPath
38)
39
40# Constant values
41[int]$xlToRight = -4161; [int]$xlToLeft = -4159; [int]$xlUp = -4162; [int]$xlDown = -4121;
42
43If (($objExcel = New-Object -ComObject Excel.Application)) { Write-Verbose "Created Excel application object" }
44Else { Write-Host "Unable to create Excel object on this computer. Check if you have Excel installed." -ForegroundColor Red; Return $Error[0].CategoryInfo.Category; }
45
46If(Test-Path -Path $WorkbookPath)
47{
48If(($objWorkbook = $objExcel.Workbooks.Open($WorkbookPath))) { Write-Verbose "Opened $WorkbookPath successfully" }
49Else { Write-Host "Unable to open $WorkbookPath." -ForegroundColor Red; Return $Error[0].Exception.Message }
50}
51Else { Return "Workbook not found $WorkbookPath!" }
52
53$objSheet = $objWorkbook.Sheets.Item(1)
54$TotalColumnsAddress = ($objSheet.Cells(1,$objSheet.Columns.Count).EntireColumn.Address($false, $false)).ToString().Split(":")[0]
55$ColumnCount = $objSheet.Range("$($TotalColumnsAddress)1").End($xlToLeft).Column
56$RowCount = $objSheet.Range("A$($objSheet.Rows.Count)").End($xlUp).Row
57$LastColumnAddress = ($objSheet.Range("$($TotalColumnsAddress)1").End($xlToLeft).EntireColumn.Address($false,$false)).ToString().Split(":")[0]
58Write-Verbose "Total column address limit is $TotalColumnsAddress`n Whereas Last column in data is $LastColumnAddress"
59
60If ($ColumnCount -ge 1 -and $RowCount -ge 2) { Write-Verbose "Found $ColumnCount column and $RowCount rows of data in workbook."}
61Else
62{
63Write-Host "No records found for processing. There should be at least one record/row apart from the header row in Workbook" -ForegroundColor Red
64Return "No records found in input workbook file"
65}
66
67Write-Verbose "Converting Excel data into an object."
68$ColHeaders = @()
69$ColHeaders = $objSheet.Range("A1:$($objSheet.Range("$($TotalColumnsAddress)1").End($xlToLeft).Address($false,$false))").value2
70# Replace blank column headers with Column'n'
71Write-Verbose "Generating object headers from workbook"
72For($i=1; $i -le $ColHeaders.Count; $i++)
73{
74if($ColHeaders[1,$i] -eq "" -or $ColHeaders[1,$i] -eq $null)
75{
76Write-Verbose "Column $i was found empty hence assigning column name as Column$i"
77$ColHeaders[1,$i] = "Column$i"
78}
79}
80
81$objOutData = @()
82
83$DataRange = $objSheet.Range("A2:$($LastColumnAddress)$RowCount").Rows
84Write-Verbose "Started processing rows/records from workbook"
85For($curRow=2; $curRow -le $RowCount; $curRow++)
86{
87Write-Verbose "Working on row number $curRow."
88$rowData = $DataRange.Rows | Where-Object { $_.Row -eq $curRow } | %{$_.Value2}
89# Blank object for properties
90$objRecord = New-Object -TypeName PSObject
91For($curColumn = 1; $curColumn -le $ColumnCount; $curColumn++)
92{
93# Adjusting the number format for column
94If($objSheet.Cells($curRow, $curColumn).NumberFormat -eq "General")
95{
96$ValueData = $rowData[$curColumn-1]
97}
98Else
99{
100If(($objSheet.Cells($curRow, $curColumn).NumberFormat).ToString() -cmatch "d" -or
101($objSheet.Cells($curRow, $curColumn).NumberFormat).ToString() -cmatch "M" -or
102($objSheet.Cells($curRow, $curColumn).NumberFormat).ToString() -cmatch "yy")
103{
104If(($objSheet.Cells($curRow, $curColumn).NumberFormat) -cmatch "h:" -and
105($objSheet.Cells($curRow, $curColumn).NumberFormat) -cmatch "mm")
106{
107Write-Verbose "Number format found: $($objSheet.Cells($curRow, $curColumn).NumberFormat)"
108$ValueData = [System.DateTime]::FromOADate($rowData[$curColumn-1]).ToString('ddd, dd-MMM-yyyy hh:mm:ss tt')
109}
110Else
111{
112$ValueData = [System.DateTime]::FromOADate($rowData[$curColumn-1]).ToString('dd-MMM-yyyy')
113}
114}
115ElseIf(($objSheet.Cells($curRow, $curColumn).NumberFormat) -cmatch "h:" -or
116($objSheet.Cells($curRow, $curColumn).NumberFormat) -cmatch "mm" -or
117($objSheet.Cells($curRow, $curColumn).NumberFormat) -cmatch "ss")
118{
119$ValueData = [System.DateTime]::FromOADate($rowData[$curColumn-1]).ToString('hh:mm:ss')
120}
121ElseIf(($objSheet.Cells($curRow, $curColumn).NumberFormat) -cmatch "0" -or
122($objSheet.Cells($curRow, $curColumn).NumberFormat) -cmatch "0.0")
123{
124$value = 0
125If([double]::TryParse($rowData[$curColumn-1], [ref]$value)) { $ValueData = $value }
126Else { $ValueData = $rowData[$curColumn-1] }
127}
128Else
129{ $ValueData = $rowData[$curColumn-1] }
130}
131$objRecord | Add-Member -MemberType NoteProperty -Name $ColHeaders[1,$curColumn] -Value $ValueData -Force
132}
133$objOutData += $objRecord
134}
135
136# Quit Excel and release all resources.
137$objWorkbook.Close($false)
138$objExcel.Quit()
139Write-Verbose "Successfully processed and closed Excel application"
140
141# Supress errors temporarily
142$oldErrorActionPreference = $ErrorActionPreference
143$ErrorActionPreference = 'SilentlyContinue'
144
145Try { do { $comReleaser = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($RowCount) } while($comReleaser -gt -1) } Catch {}
146Try { do { $comReleaser = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($ColumnCount) } while($comReleaser -gt -1) } Catch {}
147Try { do { $comReleaser = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($DataRange) } while($comReleaser -gt -1) } Catch {}
148Try { do { $comReleaser = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($objSheet) } while($comReleaser -gt -1) } Catch {}
149Try { do { $comReleaser = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($objWorkbook) } while($comReleaser -gt -1) } Catch {}
150Try { do { $comReleaser = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($objExcel) } while($comReleaser -gt -1) } Catch {}
151Try { do { $comReleaser = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($objRecord) } while($comReleaser -gt -1) } Catch {}
152Write-Verbose "Released all the Com objects."
153$ErrorActionPreference = $oldErrorActionPreference
154
155Return $objOutData
156}
157
158
159#----------------------------------------------------------
160
161
162#----------------------------------------------------------
163#START FUNCTIONS
164#----------------------------------------------------------
165Function Start-Commands
166{
167 Create-Users
168}
169
170Function Create-Users
171{
172 "Processing started (on " + $date + "): " | Out-File $log -append
173 "--------------------------------------------" | Out-File $log -append
174 Import-FromExcel | ForEach-Object
175 If (($_.Implement.ToLower()) -eq "yes")
176 {
177 If (($_.GivenName -eq "") -Or ($_.LastName -eq ""))
178 {
179 Write-Host "[ERROR]`t Please provide valid GivenName, LastName and Initials. Processing skipped for line $($i)`r`n"
180 "[ERROR]`t Please provide valid GivenName, LastName and Initials. Processing skipped for line $($i)`r`n" | Out-File $log -append
181 }
182 Else
183 {
184 # Set the target OU
185 $location = $_.TargetOU + ",$($addn)"
186
187 # Set the Enabled and PasswordNeverExpires properties
188 If (($_.Enabled.ToLower()) -eq "true") { $enabled = $True } Else { $enabled = $False }
189 If (($_.PasswordNeverExpires.ToLower()) -eq "true") { $expires = $True } Else { $expires = $False }
190
191 # A check for the country, because those were full names and need
192 # to be land codes in order for AD to accept them. I used Netherlands
193 # as example
194 If($_.Country -eq "Deutschland")
195 {
196 $_.Country = "DE"
197 }
198 Else
199 {
200 $_.Country = "EN"
201 }
202 # Replace dots / points (.) in names, because AD will error when a
203 # name ends with a dot (and it looks cleaner as well)
204 $replace = $_.Lastname.Replace(".","")
205 If($replace.length -lt 4)
206 {
207 $lastname = $replace
208 }
209 Else
210 {
211 $lastname = $replace.substring(0,4)
212 }
213 # Create sAMAccountName according to this 'naming convention':
214 # <FirstLetterInitials><FirstFourLettersLastName> for example
215 # htehp
216 $sam = $_.GivenName.substring(0,1).ToLower() + $lastname.ToLower()
217 Try { $exists = Get-ADUser -LDAPFilter "(sAMAccountName=$sam)" }
218 Catch { }
219 If(!$exists)
220 {
221 # Set all variables according to the table names in the Excel
222 # sheet / import CSV. The names can differ in every project, but
223 # if the names change, make sure to change it below as well.
224 $setpass = ConvertTo-SecureString -AsPlainText $_.Password -force
225
226 Try
227 {
228 Write-Host "[INFO]`t Creating user : $($sam)"
229 "[INFO]`t Creating user : $($sam)" | Out-File $log -append
230 New-ADUser $sam -GivenName $_.GivenName -Initials $_.Initials `
231 -Surname $_.LastName -DisplayName ($_.LastName + "," + $_.Initials + " " + $_.GivenName) `
232 -Office $_.OfficeName -Description $_.Description -EmailAddress $_.Mail `
233 -StreetAddress $_.StreetAddress -City $_.City -State $_.State `
234 -PostalCode $_.PostalCode -Country $_.Country -UserPrincipalName ($sam + "@" + $dnsroot) `
235 -Company $_.Company -Department $_.Department -EmployeeID $_.EmployeeID `
236 -Title $_.Title -OfficePhone $_.Phone -AccountPassword $setpass -Manager $_.Manager `
237 -profilePath $_.ProfilePath -scriptPath $_.ScriptPath -homeDirectory $_.HomeDirectory `
238 -homeDrive $_.homeDrive -Enabled $enabled -PasswordNeverExpires $expires
239 Write-Host "[INFO]`t Created new user : $($sam)"
240 "[INFO]`t Created new user : $($sam)" | Out-File $log -append
241
242 $dn = (Get-ADUser $sam).DistinguishedName
243 # Set an ExtensionAttribute
244 #If ($_.ExtensionAttribute1 -ne "" -And $_.ExtensionAttribute1 -ne $Null)
245 #{
246 # $ext = [ADSI]"LDAP://$dn"
247 # $ext.Put("extensionAttribute1", $_.ExtensionAttribute1)
248 # Try { $ext.SetInfo() }
249 # Catch { Write-Host "[ERROR]`t Couldn't set the Extension Attribute : $($_.Exception.Message)" }
250 #}
251
252 # Set ProxyAdresses
253 Try { $dn | Set-ADUser -Add @{proxyAddresses = ($_.ProxyAddresses -split ";")} -ErrorAction Stop }
254 Catch { Write-Host "[ERROR]`t Couldn't set the ProxyAddresses Attributes : $($_.Exception.Message)" }
255
256 # Move the user to the OU ($location) you set above. If you don't
257 # want to move the user(s) and just create them in the global Users
258 # OU, comment the string below
259 If ([adsi]::Exists("LDAP://$($location)"))
260 {
261 Move-ADObject -Identity $dn -TargetPath $location
262 Write-Host "[INFO]`t User $sam moved to target OU : $($location)"
263 "[INFO]`t User $sam moved to target OU : $($location)" | Out-File $log -append
264 }
265 Else
266 {
267 Write-Host "[ERROR]`t Targeted OU couldn't be found. Newly created user wasn't moved!"
268 "[ERROR]`t Targeted OU couldn't be found. Newly created user wasn't moved!" | Out-File $log -append
269 }
270
271 # Rename the object to a good looking name (otherwise you see
272 # the 'ugly' shortened sAMAccountNames as a name in AD. This
273 # can't be set right away (as sAMAccountName) due to the 20
274 # character restriction
275 $newdn = (Get-ADUser $sam).DistinguishedName
276 Rename-ADObject -Identity $newdn -NewName ($_.GivenName + " " + $_.LastName)
277 Write-Host "[INFO]`t Renamed $($sam) to $($_.GivenName) $($_.LastName)`r`n"
278 "[INFO]`t Renamed $($sam) to $($_.GivenName) $($_.LastName)`r`n" | Out-File $log -append
279 }
280 Catch
281 {
282 Write-Host "[ERROR]`t Oops, something went wrong: $($_.Exception.Message)`r`n"
283 }
284 }
285 Else
286 {
287 Write-Host "[SKIP]`t User $($sam) ($($_.GivenName) $($_.LastName)) already exists or returned an error!`r`n"
288 "[SKIP]`t User $($sam) ($($_.GivenName) $($_.LastName)) already exists or returned an error!" | Out-File $log -append
289 }
290 }
291 }
292 Else
293 {
294 Write-Host "[SKIP]`t User ($($_.GivenName) $($_.LastName)) will be skipped for processing!`r`n"
295 "[SKIP]`t User ($($_.GivenName) $($_.LastName)) will be skipped for processing!" | Out-File $log -append
296 }
297 $i++
298 }
299 "--------------------------------------------" + "`r`n" | Out-File $log -append
300}
301
302Write-Host "STARTED SCRIPT`r`n"
303Write-Host "STOPPED SCRIPT"