New Employees from access to AD With Powershell

This topic contains 5 replies, has 2 voices, and was last updated by Profile photo of Don Jones Don Jones 1 month ago.

  • Author
    Posts
  • #64561
    Profile photo of benwexler
    benwexler
    Participant

    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}
    	
  • #64570
    Profile photo of Don Jones
    Don Jones
    Keymaster

    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!

    • #64582
      Profile photo of benwexler
      benwexler
      Participant

      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?

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

  • #64594
    Profile photo of Don Jones
    Don Jones
    Keymaster

    1) Time to upgrade 😉

    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!

  • #64818
    Profile photo of benwexler
    benwexler
    Participant

    Does this involve Select "Field Names" Command and Alter Table Command?

  • #64828
    Profile photo of Don Jones
    Don Jones
    Keymaster

    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.

You must be logged in to reply to this topic.