Schedule import of users from CSV

With Adaxes, you can automate user account provisioning by importing user data from a CSV file. For example, such a CSV file can be created by your HR system or other third-party software. Adaxes can regularly process this file and create new users in your directory. On top of that, Adaxes will execute all the provisioning workflows you have configured.

The CSV file has to be imported using a PowerShell script. The script will read the CSV file via the Import-Csv cmdlet and pass the data to the New-AdmUser cmdlet to create new users.

The New-AdmUser cmdlet is included in Adaxes PowerShell module. To use the cmdlet in a scheduled task, you need to install Adaxes PowerShell module on the computer where your Adaxes service is running.

Create CSV file

Create a CSV file with the following structure.

Adaxes will expect the columns in the CSV file to have specific names. If the columns in your CSV file are named differently or are not represented in the default set, you will need to use column mapping in the import script.

 Default column set {id=default-column-names}
Column mame Description Example Type
AccountExpirationDate The expiration date for the account. When set to 0, the account never expires. 4/17/2006
Monday, April 17, 2006
Monday, April 17, 2006 2:22 PM
Mon, 17 Apr 2006 21:22:48 GMT
05/01/2012 5:00:00 PM
Date
AccountNotDelegated Specifies whether the security context of the user is delegated to a service. true
false
Boolean
AccountPassword The user password. secret Secure String
AllowReversiblePassword
Encryption
Specifies whether reversible password encryption is allowed for the account. true
false
Boolean
CannotChangePassword Specifies whether the account password can be changed. true
false
Boolean
ChangePasswordAtLogon Specifies whether the password must be changed during the first logon. true
false
Boolean
City The user's town or city. London String
Company The user's company. Acme String
Country The country or region code for the user's language of choice. US
FR
String
Department The user's department. Sales String
Description The description of the user. External subcontractor String
DisplayName The display name of the user. John Smith String
Division The user's division. Software String
EmailAddress The user's e-mail address. johndoe@example.com String
EmployeeID The user's employee ID. A123321 String
EmployeeNumber The user's employee number. 112233 String
Enabled Specifies if the account is enabled. true
false
Boolean
Fax The user's fax phone number. +1 (999) 555 1122 String
GivenName The user's first name. John String
HomeDirectory The user's home directory. \\SERVER\johnsmith String
HomeDrive The drive that is associated with the UNC path defined by the HomeDirectory property. D: String
HomePage The URL of the home page of the user. http://example.com/jsmith String
HomePhone The user's home telephone number. +1 (999) 555 2222 String
Initials The initials that represent part of the user's name. L String
LogonWorkstations The computers that the user can access. COMP1,COMP2.example.com String
Manager The user's manager. john.doe
CN=Doe,CN=Users,DC=acme,DC=com
7D1D1508-2A07-47D8-8933-C9E557ED86D0
S-1-5-21-1233211223-291919
ADUser
MobilePhone The user's mobile phone number. +1 (999) 555 3333 String
Name The user's full name. John Smith String
Office The location of the user's office or place of business. B1021 String
OfficePhone The user's office telephone number. +1 (999) 555 4444 String
Organization The user's organization. Accounting String
OtherAttributes Values for user properties that cannot be specified in the CSV file columns. 'extensionAttribute1'=value
'customAttribute'=value1,value2
'attr1'=val; 'attr2'=val1,val2
TTT
OtherName The name in addition to a user's given name and surname, such as the user's middlename. Peter String
PasswordNeverExpires Specifies whether the password of the account can expire. true
false
Boolean
PasswordNotRequired Specifies whether the account requires a password. true
false
Boolean
Path The DN of the Organizational Unit (OU) or container where the new user will be created. CN=Users,DC=acme,DC=com String
POBox The user's post office box number. 25656 String
PostalCode The user's postal code or zip code. 18711 String
ProfilePath The path to the user's profile. \\SERVER\profiles\johndoe String
ProtectedFromAccidental
Deletion
Specifies whether an object is protected from accidental deletion. true
false
Boolean
SamAccountName The user's logon name (pre-Windows 2000). johnsmith String
ScriptPath The path to the user's log on script. \\SCRIPTS\johnsmithLogin String
SmartcardLogonRequired Specifies whether a smart card is required to logon. true
false
Boolean
State The user's state or province. Nevada String
StreetAddress The user's street address. 100 Main Street String
Surname The user's last name or surname. Smith String
Title The user's title. Sales Manager String
TrustedForDelegation Specifies whether an account is trusted for Kerberos delegation. true
false
Boolean
UserPrincipalName The user's logon name. johnsmith@example.com String

Schedule user import

To schedule the import process, you need to create a scheduled task that will execute a PowerShell script.

  1. Launch Adaxes Administration console.

     How
    • On the computer where Adaxes Administration console is installed, open Windows Start menu.

    • Click Adaxes Administration Console.

  2. Expand your Adaxes service, right-click Scheduled Tasks, point to New and click Scheduled Task.

  3. Enter a name for the new scheduled task, and click Next.

    It is recommended to use nouns to name scheduled tasks (e.g. CSV Importer), because tasks will appear as operation initiators in the acitivity log and approval email notifications.

  4. Specify how often the task should run and click Next.

  5. To import user accounts into an Organizational Unit, select the Organizational Unit object type.

    Click Next.

  6. Click Add an action.

  7. Select Run a program or PowerShell script.

  8. Click Edit.

    You can click the button to provide a custom description for the action.

  9. Paste the following script into the script editor.

    $file = "\\SERVER\Share\users.csv"
    $targetDN = "%distinguishedName%"
    
    $domain = $Context.GetObjectDomain($targetDN)
    $importedUsers = Import-Csv $file
    foreach ($user in $importedUsers)
    {
        # Process special columns (see below)
    
        # Process mapped columns (see below)
    
        $user | New-AdmUser -Path $targetDN -Server $domain -AdaxesService localhost
    }
    

    The $targetDN variable specifies the distinguished name (DN) of the Organizational Unit or container where to create user accounts. The %distinguishedName% value reference will be replaced with the DN of the Organizational Unit included in the activity scope of the task.

    Processing special columns

    If your CSV file contains columns with the following data types, they need to be processed in the script:

    • Password (e.g. AccountPassword)
    • Boolean (e.g. ChangePasswordAtLogon, Enabled)
    • DN (e.g. Manager)
     Details

    The columns have to be processed because Adaxes expects data of a specific type from each column.

    For example, to specify a manager, you need to pass the manager's distinguished name (DN) or username to the New-AdmUser cmdlet. This means you need to obtain the correct property value in the script if the column in your CSV contains some other unique identifier, like Employee ID.

    $file = "\\SERVER\Share\users.csv"
    $targetDN = "%distinguishedName%"
    
    $domain = $Context.GetObjectDomain($targetDN)
    $importedUsers = Import-Csv $file
    foreach ($user in $importedUsers)
    {
        # Password column
        $user.AccountPassword = `
            ConvertTo-SecureString -AsPlainText $user.AccountPassword -Force
    
        # Boolean columns
        $user.Enabled = [System.Boolean]::Parse($user.Enabled)
        $user.ChangePasswordAtLogon = [System.Boolean]::Parse($user.ChangePasswordAtLogon)
    
        # DN columns
        $managerID = $user.Manager
        $manager = Get-AdmObject -Filter {employeeID -eq $managerID} `
            -Server $domain -AdaxesService localhost -ErrorAction SilentlyContinue
        $user.Manager = $manager.DistinguishedName
    
        $user | New-AdmUser -Path $targetDN -Server $domain -AdaxesService localhost
    }
    

    Column mapping

    If columns in your CSV file have custom names or the file contains columns that are not included in the default property set, you need to map these columns to the properties of the new user.

     Details

    The $columnMap variable specifies a hashtable that maps column names to property names from your directory schema.

    Import-Module Adaxes
    
    $file = "\\SERVER\Share\users.csv"
    $targetDN = "%distinguishedName%"
    $columnMap = @{
        "First Name" = "givenName";
        "Last Name" = "sn";
        "EmpID" = "employeeID";
        "EmpRole" = "adm-CustomAttributeText1";
    }
    
    $domain = $Context.GetObjectDomain($targetDN)
    $importedUsers = Import-Csv $file
    foreach ($user in $importedUsers)
    {
        $otherAttributes = @{}
        foreach ($property in $user.PSObject.Properties)
        {
            # Map property name if mapping specified
            if ($columnMap[$property.Name])
            {
                $propertyName = $columnMap[$property.Name]
                $user.PSObject.Properties.Remove($property.Name)            
            }
            else
            {
                continue
            }
    
            # Get property value
            $value = $property.Value
            if ($null -eq $value)
            {
                continue
            }
    
            $otherAttributes.Add($propertyName, $value)
        }
        $user | New-AdmUser -Path $targetDN -Server $domain `
            -AdaxesService localhost -OtherAttributes $otherAttributes
    }
    

    For quick reference, here is a list of most commonly used properties, their names, and their availability.

     Property names
    • Display name

    • Property name in schema

    • Availability

    • Full Name

    • cn, name

    • On-prem/Azure

    • First Name

    • givenName

    • On-prem/Azure

    • Last Name

    • sn

    • On-prem/Azure

    • Initials

    • initials

    • On-prem only

    • Description

    • description

    • On-prem/Azure

    • Office

    • physicalDeliveryOfficeName

    • On-prem/Azure

    • Telephone Number

    • telephoneNumber

    • On-prem/Azure

    • Telephone Number (Other)

    • otherTelephone

    • On-prem only

    • Email

    • mail

    • On-prem/Azure

    • Web Page

    • wwwHomePage

    • On-prem/Azure

    • Web Page (Other)

    • url

    • On-prem only

    • Username

    • userPrincipalName

    • On-prem/Azure

    • SAM Account Name

    • sAMAccountname

    • On-prem only

    • Password

    • unicodePwd

    • On-prem/Azure

    • Account Expires

    • accountExpires

    • On-prem/Azure

    • Protect from Accidental Deletion

    • ProtectedFromAccidentalDeletion

    • On-prem/Azure

    • Street Address

    • streetAddress

    • On-prem/Azure

    • P.O.Box

    • postOfficeBox

    • On-prem/Azure

    • City

    • l

    • On-prem/Azure

    • State/Province

    • st

    • On-prem/Azure

    • Zip/Postal Code

    • postalCode

    • On-prem/Azure

    • Country/Region

    • c, co, and countryCode

    • On-prem/Azure

    • Title

    • title

    • On-prem/Azure

    • Department

    • department

    • On-prem/Azure

    • Company

    • company

    • On-prem/Azure

    • Division

    • division

    • On-prem/Azure

    • Manager

    • manager

    • On-prem/Azure

    • Profile Path

    • profilePath

    • On-prem only

    • Logon Script

    • scriptPath

    • On-prem only

    • Home Directory

    • homeDirectory

    • On-prem only

    • Home Directory Drive

    • homeDrive

    • On-prem only

    • Home Phone, Home Phone (Other)

    • homePhone, otherHomePhone

    • On-prem only

    • Mobile Phone

    • mobile

    • On-prem/Azure

    • Mobile Phone (Other)

    • otherMobile

    • On-prem only

    • Fax

    • facsimileTelephoneNumber

    • On-prem/Azure

    • Fax (Other)

    • otherFacsimileTelephoneNumber

    • On-prem only

    • IP Phone

    • ipPhone, otherIpPhone

    • On-prem only

    • Notes

    • info

    • On-prem only

    • Employee Type

    • employeeType

    • On-prem/Azure

    • Employee ID

    • employeeID

    • On-prem/Azure

    • Age Group

    • ageGroup

    • Azure only

    • Hire Date

    • employeeHireDate

    • Azure only

    • Leave Date

    • employeeLeaveDateTime

    • Azure only

    • Cost Center

    • costCenter

    • Azure only

    • Responsibilities

    • responsibilities

    • Azure only

    • Schools

    • schools

    • Azure only

    Error email notifications

    You might want the script to send an email notification if an error occurs during user account creation. In this case, use the following code:

    ...
    
    try
    {
        $user | New-AdmUser -Path $targetDN -Server $domain `
            -AdaxesService localhost -ErrorAction Stop
    }
    catch [System.Exception]
    {
        $to = "admin@company.com"
        $subj = "Failed to Import User from CSV"
        $bodyText = "Adaxes failed to import user " + $user.Name + " from $file."`
            + "`nError: " + $_.Exception.Message
        $bodyHtml = $null
        $Context.SendMail($to, $subj, $bodyText, $bodyHtml)
    
        $Context.LogMessage($bodyText, "Error")
    }
    

    For information on how to create scripts for business rules, custom commands, and scheduled tasks, see Server-side scripting.

    When done, click OK two times.

  10. Click Next.

  11. On the Activity Scope step, specify the Organizational Unit where imported user accounts will be created:

    • Click Add.

    • Select the target Organizational Unit.

    • In the Assignment Options dialog, select the The Organizational Unit object checkbox and clear the Objects located in the Organizational Unit checkbox.

    • Click OK two times.

    Using business rules you can automatically move newly created users to appropriate Organizational Units based on certain rules. For more details, see Automatically move users between Organizational Units.

  12. The activity scope of the scheduled task must include a single Organizational Unit! Otherwise the task will import user accounts to each OU included into the scope.

    Click Finish.

Default Property Values

With the help of property patterns you can assign a default value to any property of directory objects. If a user account is created during data import, and the value of a property is empty, the default value will be set for that property. For details, see:

See also