New Employees from access to AD With Powershell

PowerShell Help
1

I know there is a lot of code in here. The objective of this is to have employees created in AD with as little interaction as possible . I am bringing it here with requests for suggestions of how to make it cleaner.

  1. HR creates new employees in access database
  2. Macro runs the query to gather specific depts/users that require AD.
  3. The macro exports the query as an .xlsx
  4. PShell converts the .xlsx to a csv
  5. PShell runs import-csv
  6. PShell renames column headers as per LDAP naming convention
  7. Imported password module runs,provides pwd
  8. New csv with pwd is exported so we can have the pwd to give to users.
  9. Run a new import-csv to new-aduser
#Creates new OU
New-ADOrganizationalUnit #New AD Group - Not necessarily needed
Add-Type -AssemblyName System.Web

Import-Module ActiveDirectory

#Password Randomizer
Import-Module .\New-SWRandomPassword.psm1

# Create the Access application object
$accessApp = new-object -com access.application

#Open the database - use full path
$accessApp.Application.OpenCurrentDatabase(#Access Database Location#)

#Run Access query & run macro to export to .xlsx
$accessApp.Application.DoCmd.OpenQuery("#AccessQueryName")
$accessApp.Application.DoCmd.RunMacro("#MacroName")

#Close database
$accessApp.Application.CloseCurrentDatabase()

#Close Access
$accessApp.Quit()



[System.Runtime.InteropServices.Marshal]::ReleaseComObject($accessApp)
Remove-Variable accessApp

#Converts .xlsx to csv
 Function ExportWSToCSV ($excelFileName, $csvLoc)
{
    $excelFile = #ExcelFileLocation
    $E = New-Object -ComObject Excel.Application
    $E.Visible = $false
    $E.DisplayAlerts = $false
    $wb = $E.Workbooks.Open($excelFile)
    foreach ($ws in $wb.Worksheets)
    {
        $n = $ws.Name
        $ws.SaveAs($csvLoc + $n + ".csv", 6)
    }
    $E.Quit()
}
ExportWSToCSV -excelFileName "file" -csvLoc "#SaveLocation"

#Imports CSV, renames field name as per AD requirements and exports to a new CSV with passwords included
#Change Path to reflect the OU and DC for the company 
Import-CSV "#CSV-From-Above" | Select-Object Department,Title,EmployeeID, `
    @{name='Name';expression={($_.'First Name'.substring(0)+$_.'Last Name').substring(0).toLower()}}, `
    @{name='samAccountName';expression={($_.'First Name'.substring(0,1)+$_.'Last Name').substring(0).toLower()}}, `
    #Change to respective UPN addressing    
    @{name='userPrincipalName';expression={$_.'First Name'+'.'+$_.'Last Name'+'#domainname'}},` 
    @{name='displayName';expression={$_.'First Name'+' '+$_.'Last Name'}}, `
    @{name='givenName';expression={$_.'First Name'}}, `
    @{name='surName';expression={$_.'Last Name'}},`
    @{name= 'mail'; expression={$_.'First Name'+'.'+$_.'Last Name'+'#EmailDomain'}},`
    @{name='path';expression={#'OU=container,DC=container,DC=local'}},`
    @{name='accountpassword';expression= {New-SWRandomPassword -MinPasswordLength 8 -MaxPasswordLength 10 -Count 1}}| Export-Csv #NewFileWithPasswords -NoTypeInformation 
    
#Imports the csv from last command and creates separate new users from each row in the csv
    
         $Users = Import-Csv #Import CSV with passwords 
	ForEach ($User in $Users)
	{
		 
		$Name = $User.name
		$GivenName = $User.givenName
		$Surname = $User.surName
		$SAM = $User.SAMAccountname
		$userPrincipalName = $User.userPrincipalName
        $Password = $User.accountpassword
		$Title=$User.Title
		$Department=$User.Department
        $City=$User.City
        $State=$User.State
        $Office=$User.Office
        $EmployeeID=$User.EmployeeID
        $Email=$User.name+"DomainName" #change to appropriate email address

        New-ADUser -Name $Name -GivenName $givenName -UserPrincipalName $userPrincipalName -Surname $surname -SamAccountName $SamAccountName -Email $Email -Department $Department -EmployeeID $EmployeeID -Title $Title -Enabled $True -AccountPassword $(ConvertTo-SecureString -string $Password -AsPlainText -force) -PasswordNeverExpires $True -Path 'OU=container,DC=container,DC=local'-PassThru}
2

Thanks for posting ;).

I’m always curious about people using Excel and CSV; they’re poor data-storage formats, and can often involve more coding than using a proper database. Relying on files as a data medium in unattended processes also gets delicate, which means future troubleshooting and debugging.

It looks in this case like you’re trapped with Excel as a starting point, which sucks. Me, personally, I’d probably start with the Access source, and copy necessary data off to a SQL Server (including Express, if you prefer) instance. That eliminates Excel and CSV. I don’t need to rename any columns, because they can be named whatever I want. I can manipulate and use that data however I want, and if I need to create some kind of output for users, I can do that to. And, working with SQL Server involves a lot less fragility and code. I can easily extract SQL Server data in the form of objects, and pipe those to New-ADUser.

That’s why I asked on Twitter - I’m a past database guy, and so I tend to turn to databases when I’m dealing with data. I see all these multiple-CSV-file approaches (and Excel) break down over time, and they’re really difficult for someone - often, even their original creator - to re-understand months down the line. I tend to not regard them as highly stable for mission-critical operations. There’s just a lot of moving pieces, and getting it all working requires you to keep track of a lot in your head.

Also, a platform like SQL Server - even Express! - gives you a ton of functionality that you may be coding manually. For example, it’s trivial to create a SQL Server Integration Services (SSIS) package that automatically runs on a schedule and sucks data out of you Access database. That’s your first 6 steps, all being done by a tool that’s specifically designed (and supported) to do that.

Anyway - I definitely appreciate you talking the time to post. I really wanted to understand the use case!

3
  1. my boss set up all of our databases in access ( he’s also a Database guy) many years ago before i got here (only been here 3 yrs) so therefore we don’t have any SQl server instances running.

2)“I don’t need to rename any columns, because they can be named whatever I want.” Where are you referring to (in SQL ?) Doesn’t LDAP have specific naming conventions that need to be followed, otherwise the data doesn’t get passed through?

    • Dumb question (It’s only a dumb question if it is never asked)*
      Im assuming this is still all coded through Powershell
4

  1. Time to upgrade :wink:

  2. If you’re relying on the column names in CSVs to be specific things so they’ll hook up to LDAP properly, that’s not a concern in SQL Server. You can rename columns as part of the actual query, leaving the underlying physical column names whatever you want. A beauty of SQL.

  3. Sure!

5

Does this involve Select “Field Names” Command and Alter Table Command?

6

No, you wouldn’t use ALTER TABLE.

SELECT ColumnA AS Alias1, Column2 AS Alias2 FROM TableName WHERE criteria

You’d get two columns in the output, named Alias1 and Alias2.