Script Repository


Import new and updated users from CSV file

November 13, 2020
4119

Using the script, you can schedule users import from CSV files. For details, see Schedule Import of Users from a CSV File.

CSV File Sample:

sn,givenName,sAMAccountName,description,physicalDeliveryOfficeName,AccountPassword,manager
Robertson,William,wrobertson,Sales Manager at New York Office,New York,secret,Stephen Jones
John,Susan,sjohn,Senior Controller at LA Central,LA Central,,James Borwn
Smith,Elizabeth,esmith,---TERMINATED---,TERM,Z0eArjoQe

Script 1: Create new and update existing users

The script imports user accounts from a CSV file. Existing accounts are updated based on the data in the CSV file and non-existing ones are created in Active Directory. Existing users are identified by the property specified in the $userIdProperty variable.

Parameters:

  • $csvFilePath - Specifies a path to the CSV file that will be imported.
  • $userIdColumn - Cpecifies the name of the column that contains the property for identifying existing users.
  • $userIdProperty - Specifies the LDAP name of the property for identifying existing users (e.g. sAMAccountName, employeeID).
  • $accountPasswordColumn - Specifies the name of the column that contains account passwords.
  • $customColumnNames - Maps column headers with LDAP names of the corresponding user account properties. If mapping is not specified for a column header, the header specified in the file will be used as the property LDAP name.
  • $aDObjectProperties - Specifies the list of headers of the columns that contain references to other AD objects, such as, Manager, Secretary or Assistant. Objects can be referenced in the columns by their:
    • Distinguished Name
    • Full name (cn attribute)
    • Display name
Edit Remove
PowerShell
Import-Module Adaxes

$csvFilePath = "\\SERVER\share\ImportUsers.csv" # TODO: modify me
$userIdColumn = "EmployeeName" # TODO: modify me
$userIdProperty = "sAMAccountName" # TODO: modify me
$accountPasswordColumn = "AccountPassword" # TODO: modify me
$customColumnNames = @{
    "JobTitle" = "title";
    "FirstName" = "givenName";
} # TODO: modify me
$aDObjectProperties = @("Manager", "Secretary") # TODO: modify me

$domainName = $Context.GetObjectDomain("%distinguishedName%")
$importedUsers  = Import-Csv -Path $csvFilePath

foreach ($userFromCSV in $importedUsers)
{
    $userObject = @{}
    $accountPassword = $NULL
    $propertiesToClear = @()
    foreach ($property in $userFromCSV.PSObject.Properties)
    {
        $columnName = $property.Name
        $value = $property.Value
        
        if ($columnName -ieq $accountPasswordColumn -and !([System.String]::IsNullOrEmpty($value)))
        {
            $accountPassword = ConvertTo-SecureString -AsPlainText $value -Force
            continue
        }
        elseif ($columnName -ieq $accountPasswordColumn -and [System.String]::IsNullOrEmpty($value))
        {
            continue
        }
        
        if ($customColumnNames.ContainsKey($columnName))
        {
            $propertyName = $customColumnNames[$columnName]
        }
        else
        {
            $propertyName = $columnName
        }
        
        if ([System.String]::IsNullOrEmpty($value))
        {
            $propertiesToClear += $propertyName
            continue
        }

        # Parse special columns
        if ($columnName -ieq $userIdColumn)
        {
            $propertyName = $userIdProperty
        }
        elseif ($aDObjectProperties -icontains $columnName)
        {
            $aDObject = Get-AdmObject -Filter {(Name -eq $value) -or (DisplayName -eq $value) -or (distinguishedName -eq $value)} `
                -AdaxesService localhost -ErrorAction SilentlyContinue -Server $domainName
            
            if ($aDObject -is [System.Array])
            {
                $Context.LogMessage("Found more than one object with identity '$value'.", "Warning")
                continue
            }

            if ($aDObject -eq $NULL)
            {
                $Context.LogMessage("Could not locate object with identity '$value'.", "Warning")
                continue
            }
            
            $value = $aDObject.DistinguishedName
        }

        if ($value -ieq "True" -or $value -ieq "False")
        {
            $value = [System.Boolean]::Parse($value)
        }

        $userObject.Add($propertyName, $value)
    }
    
    # Check whether the user exists
    $valueForSearch = $userObject.$userIdProperty
    $userExists = Get-AdmUser -LdapFilter "($userIdProperty=$valueForSearch)" `
        -AdaxesService localhost -ErrorAction SilentlyContinue -Server $domainName
    
    if ($userExists -eq $NULL)
    {
        # Build user name
        $displayName = $userObject.GivenName + " " + $userObject.SN
        $parameters = @{
            "Path" = "%distinguishedName%"
            "Name" = $displayName;
            "Server" = $domainName;
            "AdaxesService" = "localhost"
            "Enabled" = $True
            "OtherAttributes" = $userObject
            "ErrorAction" = "Stop"
        }
        
        if (!([System.String]::IsNullOrEmpty($accountPassword)))
        {
            $parameters.Add("AccountPassword", $accountPassword)
        }
        
        # Create a new user account
        try
        {
            New-AdmUser @parameters
        }
        catch
        {
            $Context.LogMessage("An error occurred when creating user '$displayName'. Error: " + $_.Exception.Message, "Warning")
        }
        continue
    }

    if ($userExists -is [System.Array])
    {
        $Context.LogMessage("Found more than one user with value '$valueForSearch' in property '$userIdProperty'", "Warning")
        continue
    }

    # If user exists, update account
    try
    {
        Set-AdmUser -Identity $userExists.DistinguishedName -Replace $userObject `
            -AdaxesService localhost -Server $domainName -ErrorAction Stop
    }
    catch
    {
        $Context.LogMessage("An error occurred when updating user '$displayName'. Error: " + $_.Exception.Message, "Warning")
    }
    
    if ($propertiesToClear.Length -ne 0)
    {
        try
        {
            Set-AdmUser -Identity $userExists.DistinguishedName -Clear $propertiesToClear `
                -AdaxesService localhost -Server $domainName -ErrorAction Stop
        }
        catch
        {
            $Context.LogMessage("An error occurred when updating user '$displayName'. Error: " + $_.Exception.Message, "Warning")
        }
    }
    
    if ([System.String]::IsNullOrEmpty($accountPassword))
    {
        continue
    }
    
    try
    {
        Set-AdmAccountPassword -Identity $userExists.DistinguishedName -NewPassword $accountPassword `
            -Reset -Server $domainName -AdaxesService localhost -ErrorAction Stop
    }
    catch
    {
        $Context.LogMessage("An error occurred when updating the password for user '$displayName'. Error: " + $_.Exception.Message, "Warning")
    }
}

Script 2: Only update existing users

The script imports user accounts from a CSV file. Existing accounts are updated based on the data in the CSV file and are identified by the property specified in the $userIdProperty variable. The script also sends an email notification containing users that were not found or if there were more than one user found with a specific identity.

Parameters:

  • $csvFilePath - Specifies a path to the CSV file that will be imported.
  • $userIdColumn - Specifies the name of the column that contains the property for identifying existing users.
  • $userIdProperty - Specifies the LDAP name of the property for identifying existing users (e.g. sAMAccountName, employeeID).
  • $accountPasswordColumn - Specifies the name of the column that contains account passwords.
  • $customColumnNames - Maps column headers with LDAP names of the corresponding user account properties. If mapping is not specified for a column header, the header specified in the file will be used as the property LDAP name;
  • $aDObjectProperties - Specifies the list of headers of the columns that contain references to other AD objects, such as, Manager, Secretary or Assistant. Objects can be referenced in the columns by their:
    • Distinguished Name
    • Full name (cn attribute)
    • Display name
  • $to - Specifies the email notification recipient.
  • $subject - Specifies the email notification subject.
  • $reportHeader - Specifies the report header.
  • $reportFooter - Specifies the report header.
Edit Remove
PowerShell
Import-Module Adaxes

$csvFilePath = "\\SERVER\share\ImportUsers.csv" # TODO: modify me
$userIdColumn = "EmployeeName" # TODO: modify me
$userIdProperty = "employeeId" # TODO: modify me
$accountPasswordColumn = "AccountPassword" # TODO: modify me
$customColumnNames = @{
    "JobTitle" = "title";
    "FirstName" = "givenName";
} # TODO: modify me
$aDObjectProperties = @("Manager", "Secretary") # TODO: modify me

# E-mail settings
$to = "recipient@domain.com" # TODO: modify me
$subject = "Import report" # TODO: modify me
$reportHeader = "<h2>Import report</h2>"
$reportFooter = "<hr /><p><i>Please do not reply to this e-mail, it has been sent to you for notification purposes only.</i></p>" # TODO: modify me

$domainName = $Context.GetObjectDomain("%distinguishedName%")
$importedUsers  = Import-Csv -Path $csvFilePath

$moreThanOneUserFound = New-Object "System.Text.StringBuilder"
$userNotFound = New-Object "System.Text.StringBuilder"
foreach ($userFromCSV in $importedUsers)
{
    $userObject = @{}
    $accountPassword = $NULL
    $propertiesToClear = @()
    foreach ($property in $userFromCSV.PSObject.Properties)
    {
        $columnName = $property.Name
        $value = $property.Value
        
        if ($columnName -ieq $accountPasswordColumn -and !([System.String]::IsNullOrEmpty($value)))
        {
            $accountPassword = ConvertTo-SecureString -AsPlainText $value -Force
            continue
        }
        elseif ($columnName -ieq $accountPasswordColumn -and [System.String]::IsNullOrEmpty($value))
        {
            continue
        }
        
        if ($customColumnNames.ContainsKey($columnName))
        {
            $propertyName = $customColumnNames[$columnName]
        }
        else
        {
            $propertyName = $columnName
        }
        
        if ([System.String]::IsNullOrEmpty($value))
        {
            $propertiesToClear += $propertyName
            continue
        }

        # Parse special columns
        if ($columnName -ieq $userIdColumn)
        {
            $propertyName = $userIdProperty
        }
        elseif ($aDObjectProperties -icontains $columnName)
        {
            $aDObject = Get-AdmObject -Filter {(Name -eq $value) -or (DisplayName -eq $value) -or (distinguishedName -eq $value)} `
                -AdaxesService localhost -ErrorAction SilentlyContinue -Server $domainName
            
            if ($aDObject -is [System.Array])
            {
                $Context.LogMessage("Found more than one object with identity '$value'.", "Warning")
                continue
            }

            if ($aDObject -eq $NULL)
            {
                $Context.LogMessage("Could not locate object with identity '$value'.", "Warning")
                continue
            }
            
            $value = $aDObject.DistinguishedName
        }

        if ($value -ieq "True" -or $value -ieq "False")
        {
            $value = [System.Boolean]::Parse($value)
        }

        $userObject.Add($propertyName, $value)
    }
    
    # Check whether the user exists
    $valueForSearch = $userObject.$userIdProperty
    $userExists = Get-AdmUser -LdapFilter "($userIdProperty=$valueForSearch)" `
        -AdaxesService localhost -ErrorAction SilentlyContinue -Server $domainName
    
    if ($NULL -eq $userExists)
    {
        $userNotFound.Append("<li>$valueForSearch</li>")
        continue
    }

    if ($userExists -is [System.Array])
    {
        $moreThanOneUserFound.Append("<li>$valueForSearch</li>")
        continue
    }

    # If user exists, update account
    try
    {
        Set-AdmUser -Identity $userExists.DistinguishedName -Replace $userObject `
            -AdaxesService localhost -Server $domainName -ErrorAction Stop
    }
    catch
    {
        $Context.LogMessage("An error occurred when updating user '$displayName'. Error: " + $_.Exception.Message, "Warning")
    }
    
    if ($propertiesToClear.Length -ne 0)
    {
        try
        {
            Set-AdmUser -Identity $userExists.DistinguishedName -Clear $propertiesToClear `
                -AdaxesService localhost -Server $domainName -ErrorAction Stop
        }
        catch
        {
            $Context.LogMessage("An error occurred when updating user '$displayName'. Error: " + $_.Exception.Message, "Warning")
        }
    }
    
    if ([System.String]::IsNullOrEmpty($accountPassword))
    {
        continue
    }
    
    try
    {
        Set-AdmAccountPassword -Identity $userExists.DistinguishedName -NewPassword $accountPassword `
            -Reset -Server $domainName -AdaxesService localhost -ErrorAction Stop
    }
    catch
    {
        $Context.LogMessage("An error occurred when updating the password for user '$displayName'. Error: " + $_.Exception.Message, "Warning")
    }
}

if ($moreThanOneUserFound.Length -eq 0 -and $userNotFound.Length -eq 0)
{
    return
}

# Build report
$html = New-Object "System.Text.StringBuilder"
$html.Append($reportHeader)
if ($userNotFound.Length -ne 0)
{
    $html.Append("<b>The following users were not found in Active Directory:</b>")
    $html.Append("<ol>")
    $html.Append($userNotFound.ToString())
    $html.Append("</ol>")
}

if ($moreThanOneUserFound.Length -ne 0)
{
    $html.Append("<b>Found more than one user with the following value of the $userIdProperty property:</b>")
    $html.Append("<ol>")
    $html.Append($moreThanOneUserFound.ToString())
    $html.Append("</ol>")
}

# Send report
$Context.SendMail($to, $subject, $NULL, $html.ToString())

Script 3: Only create new users

The script imports new user accounts from a CSV file. Each user will get a randomly generated password. The script also sends an email notification containing users with a specific identity that already exist.

Parameters:

  • $csvFilePath - Specifies a path to the CSV file that will be imported.
  • $userIdColumn - Specifies the name of the column that contains the property for identifying existing users.
  • $userIdProperty - Specifies the LDAP name of the property for identifying existing users (e.g. sAMAccountName, employeeID).
  • $customColumnNames - Maps column headers with LDAP names of the corresponding user account properties. If mapping is not specified for a column header, the header specified in the file will be used as the property LDAP name;
  • $aDObjectProperties - Specifies the list of headers of the columns that contain references to other AD objects, such as, Manager, Secretary or Assistant. Objects can be referenced in the columns by their:
    • Distinguished Name
    • Full name (cn attribute)
    • Display name
  • $ouDN - Specifies the distinguished name (DN) of the OU where new users will be created. For information on how to get the DN of a directory object, see Get the DN of an Active Directory object.
  • $to - Specifies the email notification recipient.
  • $subject - Specifies the email notification subject.
  • $reportHeader - Specifies the report header.
  • $reportFooter - Specifies the report header.
Edit Remove
PowerShell
Import-Module Adaxes

$csvFilePath = "\\SERVER\share\ImportUsers.csv" # TODO: modify me
$userIdColumn = "EmployeeName" # TODO: modify me
$userIdProperty = "sAMAccountName" # TODO: modify me
$customColumnNames = @{
    "JobTitle" = "title";
    "FirstName" = "givenName";
} # TODO: modify me
$aDObjectProperties = @("Manager", "Secretary") # TODO: modify me
$ouDN = "CN=Users,DC=domain,DC=com" # TODO: modify me

# E-mail settings
$to = "recipient@domain.com" # TODO: modify me
$subject = "Import report" # TODO: modify me
$reportHeader = "<h2>Import report</h2>"
$reportFooter = "<hr /><p><i>Please do not reply to this e-mail, it has been sent to you for notification purposes only.</i></p>" # TODO: modify me

$domainName = $Context.GetObjectDomain($ouDN)
$importedUsers  = Import-Csv -Path $csvFilePath

$rootDSE = $Context.BindToObject("Adaxes://RootDSE")
$userFound = New-Object "System.Text.StringBuilder"
foreach ($userFromCSV in $importedUsers)
{
    $userObject = @{}
    $accountPassword = $NULL
    $propertiesToClear = @()
    foreach ($property in $userFromCSV.PSObject.Properties)
    {
        $columnName = $property.Name
        $value = $property.Value
        
        if ($customColumnNames.ContainsKey($columnName))
        {
            $propertyName = $customColumnNames[$columnName]
        }
        else
        {
            $propertyName = $columnName
        }
        
        if ([System.String]::IsNullOrEmpty($value))
        {
            $propertiesToClear += $propertyName
            continue
        }

        # Parse special columns
        if ($columnName -ieq $userIdColumn)
        {
            $propertyName = $userIdProperty
        }
        elseif ($aDObjectProperties -icontains $columnName)
        {
            $aDObject = Get-AdmObject -Filter {(Name -eq $value) -or (DisplayName -eq $value) -or (distinguishedName -eq $value)} `
                -AdaxesService localhost -ErrorAction SilentlyContinue -Server $domainName
            
            if ($aDObject -is [System.Array])
            {
                $Context.LogMessage("Found more than one object with identity '$value'.", "Warning")
                continue
            }

            if ($aDObject -eq $NULL)
            {
                $Context.LogMessage("Could not locate object with identity '$value'.", "Warning")
                continue
            }
            
            $value = $aDObject.DistinguishedName
        }

        if ($value -ieq "True" -or $value -ieq "False")
        {
            $value = [System.Boolean]::Parse($value)
        }

        $userObject.Add($propertyName, $value)
    }
    
    # Check whether the user exists
    $valueForSearch = $userObject.$userIdProperty
    $userExists = Get-AdmUser -LdapFilter "($userIdProperty=$valueForSearch)" `
        -AdaxesService localhost -ErrorAction SilentlyContinue -Server $domainName
    
    if ($NULL -eq $userExists)
    {
        # Build user name
        $displayName = $userObject.GivenName + " " + $userObject.SN
        $parameters = @{
            "Path" = $ouDN
            "Name" = $displayName;
            "Server" = $domainName;
            "AdaxesService" = "localhost"
            "Enabled" = $True
            "OtherAttributes" = $userObject
            "ErrorAction" = "Stop"
        }
        
        # Generate password
        $userAdsPathObj = New-Object Softerra.Adaxes.Adsi.AdsPath "Adaxes://$ouDN"
        $rdnValue = [Softerra.Adaxes.Ldap.Rdn]::EscapeAttributeValue($displayName)
        $userAdsPathObj.CreateChildPath("CN=$rdnValue")
        $passwordString = $rootDSE.GeneratePasswordForNewUser($userAdsPathObj)
        $passwordSecureString = ConvertTo-SecureString -AsPlainText $passwordString -Force
        $parameters.Add("AccountPassword", $passwordSecureString)

        # Create a new user account
        try
        {
            New-AdmUser @parameters
        }
        catch
        {
            $Context.LogMessage("An error occurred when creating user '$displayName'. Error: " + $_.Exception.Message, "Warning")
        }
    }
    else
    {
        $userFound.Append("<li>$valueForSearch</li>")
    }
}

if ($userFound.Length -eq 0)
{
    return
}

# Build report
$html = New-Object "System.Text.StringBuilder"
$html.Append($reportHeader)
$html.Append("<b>The following users were found in Active Directory:</b>")
$html.Append("<ol>")
$html.Append($userFound.ToString())
$html.Append("</ol>")
$html.Append($reportFooter)

$Context.SendMail($to, $subject, $NULL, $html.ToString())

Comments ( 23 )
avatar
Christian Ehrenberg
Jan 20, 2020
Can you please help me with the script.
I built the task as described in (https://www.adaxes.com/tutorials_ActiveDirectoryManagement_ScheduleImportOfUsersFromCSV.htm).
And as an action the script is deposited.
Unfortunately no user will import
CSV file as described (with my data).
sn,givenName,sAMAccountName,description,physicalDeliveryOfficeName,AccountPassword,manager
Robertson,William,wrobertson,Sales Manager at New York Office,New York,secret,Stephen Jones
avatar
Support
Jan 20, 2020

Hello Christian,

Could you, please, provide a screenshot of the Scheduled Task you created including the Activity Scope? You can send it to support@adaxes.com.


Does the Scheduled Task produce any error messages when executing the script? If so, please, provide us with screenshots. You can check it by viewing the Activity History of the task:https://www.adaxes.com/help/?ManageScheduledTasks.ViewActivityHistory.html.

If you made any modifications to the script, please, provide us with the updated script in TXT format.

avatar
Dario Inversi
Sep 29, 2020
Hi, I'm trying to get the script work in our environment but I get and error on line 88: "The search filter cannot be recognized. (Server: *****) Stack trace: at <ScriptBlock>, <No file>: line 88".
Could you please support me on this error.

Thank you.

Dario
avatar
Support
Sep 29, 2020
Hello Dario.

Could you, please, post here or send us (support[at]adaxes.com) the script you are using in TXT format including all your modifications?

Also, provide us with a screenshot of the Scheduled Task that executes the script. Please, make sure that the screenshot includes the Activity Scope section.
avatar
Leo Gomez
Nov 19, 2020
I have the same problem - what was the solution?
avatar
Support
Nov 19, 2020
Hello Leo,

Unfortunately, we did not receive any information from Dario. For troubleshooting purposes, please, provide the same data as was requested above.
avatar
Leo Gomez
Nov 19, 2020
<p>Import-Module Adaxes<br /><br />$csvFilePath = "\\Server\file\OriginalImport.csv" # TODO: modify me<br /><br />$Domain="@***.***"<br />$EmpID="Employee_ID"<br />$FirstName="First_Name"<br />$LastName="Last_Name.ToLower()"<br />$userIdColumn=$($FirstName + " " + $LastName)<br />$sAMAccountName= ($LastName + $EmpID)<br />$MgrID="Supervisor"<br />$Manager=Get-ADUser -LDAPFilter "(EmployeeID=$MgrID)" -Properties distinguishedName<br />$userPrincipalName=$sAMAccountName+$Domain<br /><br />$customColumnNames = @{<br />"Company"= "company"<br />"Employee_ID" = "employeeID";<br />"Location" = "Office";<br />"First_Name" = "GivenName";<br />"Middle_Name" = "MI";<br />"Last_Name" = "Surname"<br />"Job_Title" = "title";<br />"Role" = "Department";<br />"userPrincipalName" = "userPrincipalName"<br /><br />} # TODO: modify me<br />$aDObjectProperties = @("Manager = $Manager") # TODO: modify me<br />$ouDN = "OU=Test Adaxes,DC=***,DC=***" # TODO: modify me<br /><br /># E-mail settings<br />$to = "EMAIL Address" # TODO: modify me<br />$subject = "Import report" # TODO: modify me<br />$reportHeader = "&lt;h2&gt;Import report&lt;/h2&gt;"<br />$reportFooter = "&lt;hr /&gt;&lt;p&gt;&lt;i&gt;Please do not reply to this e-mail, it has been sent to you for notification purposes only.&lt;/i&gt;&lt;/p&gt;" # TODO: modify me<br /><br />$domainName = $Context.GetObjectDomain($ouDN)<br />$importedUsers = Import-Csv -Path $csvFilePath<br /><br />$rootDSE = $Context.BindToObject("Adaxes://RootDSE")<br />$userFound = New-Object "System.Text.StringBuilder"<br />foreach ($userFromCSV in $importedUsers)<br />{<br />$userObject = @{}<br />$accountPassword = $NULL<br />$propertiesToClear = @()<br />foreach ($property in $userFromCSV.PSObject.Properties)<br />{<br />$columnName = $property.Name<br />$value = $property.Value<br /><br />if ($customColumnNames.ContainsKey($columnName))<br />{<br />$propertyName = $customColumnNames[$columnName]<br />}<br />else<br />{<br />$propertyName = $columnName<br />}<br /><br />if ([System.String]::IsNullOrEmpty($value))<br />{<br />$propertiesToClear += $propertyName<br />continue<br />}<br /><br /># Parse special columns<br />if ($columnName -ieq $userIdColumn)<br />{<br />$propertyName = $sAMAccountName<br />}<br />elseif ($aDObjectProperties -icontains $columnName)<br />{<br />$aDObject = Get-AdmObject -Filter {(Name -eq $value) -or (DisplayName -eq $value) -or (distinguishedName -eq $value)} `<br />-AdaxesService localhost -ErrorAction SilentlyContinue -Server $domainName<br /><br />if ($aDObject -is [System.Array])<br />{<br />$Context.LogMessage("Found more than one object with identity '$value'.", "Warning")<br />continue<br />}<br /><br />if ($aDObject -eq $NULL)<br />{<br />$Context.LogMessage("Could not locate object with identity '$value'.", "Warning")<br />continue<br />}<br /><br />$value = $aDObject.DistinguishedName<br />}<br /><br />if ($value -ieq "True" -or $value -ieq "False")<br />{<br />$value = [System.Boolean]::Parse($value)<br />}<br /><br />$userObject.Add($propertyName, $value)<br />}<br /><br /># Check whether the user exists<br />$valueForSearch = $userObject.$sAMAccountName<br />$userExists = Get-AdmUser -LdapFilter "($sAMAccountName=$valueForSearch)" `<br />-AdaxesService localhost -ErrorAction SilentlyContinue -Server $domainName<br /><br />if ($NULL -eq $userExists)<br />{<br /># Build user name<br />$displayName = $userObject.GivenName + " " + $userObject.SN<br />$parameters = @{<br />"Path" = $ouDN<br />"Name" = $displayName;<br />"Server" = $domainName;<br />"AdaxesService" = "localhost"<br />"Enabled" = $True<br />"OtherAttributes" = $userObject<br />"ErrorAction" = "Stop"<br />}<br /><br /># Generate password<br />$userAdsPathObj = New-Object Softerra.Adaxes.Adsi.AdsPath "Adaxes://$ouDN"<br />$rdnValue = [Softerra.Adaxes.Ldap.Rdn]::EscapeAttributeValue($displayName)<br />$userAdsPathObj.CreateChildPath("CN=$rdnValue")<br />$passwordString = $rootDSE.GeneratePasswordForNewUser($userAdsPathObj)<br />$passwordSecureString = ConvertTo-SecureString -AsPlainText $passwordString -Force<br />$parameters.Add("AccountPassword", $passwordSecureString)<br /><br /># Create a new user account<br />try<br />{<br />New-AdmUser @parameters<br />}<br />catch<br />{<br />$Context.LogMessage("An error occurred when creating user '$displayName'. Error: " + $_.Exception.Message, "Warning")<br />}<br />}<br />else<br />{<br />$userFound.Append("&lt;li&gt;$valueForSearch&lt;/li&gt;")<br />}<br />}<br /><br />if ($userFound.Length -eq 0)<br />{<br />return<br />}<br /><br /># Build report<br />$html = New-Object "System.Text.StringBuilder"<br />$html.Append($reportHeader)<br />$html.Append("&lt;b&gt;The following users were found in Active Directory:&lt;/b&gt;")<br />$html.Append("&lt;ol&gt;")<br />$html.Append($userFound.ToString())<br />$html.Append("&lt;/ol&gt;")<br />$html.Append($reportFooter)<br /><br />$Context.SendMail($to, $subject, $NULL, $html.ToString())</p>
avatar
Leo Gomez
Nov 19, 2020
The search filter cannot be recognized. (Server: ***.***) Stack trace: at <ScriptBlock>, <No file>: line 101
avatar
Support
Nov 20, 2020
Hello Leo,

Thank you for the provided details. The issue occurs because of the modifications you made in the script. For us to correct it, please, specify what exactly all your modifications are intended for and what the desired behavior is. Live examples will be very helpful.
avatar
Leo Gomez
Nov 20, 2020
I'm attempting to import a CSV using the script above to create new users.

CSV Contains:
Company, Employee_ID, Location, First_Name, Middle_Name, Last_Name, Job_title, Role,Supervisor
2000, 12345, 20394, Lexie, M, Smith, Care Associate, RG-20000-Campus-Matrix, 137250

Based on the csv:
The sAMAAccountName should be the last name and employee ID
$sAMAccountName= ($LastName + $EmpID)

Manager: Lookup based on the Supervisor field which is the Employee ID for the Manager
$MgrID="Supervisor"
$Manager=Get-ADUser -LDAPFilter "(EmployeeID=$MgrID)" -Properties distinguishedName

Users Full Name should be the concatenated first and last name:
$userIdColumn=$($FirstName + " " + $LastName)

All of the other fields are to go in as they are:
"Company"= "company"
"Employee_ID" = "employeeID";
"Location" = "Office";
"First_Name" = "GivenName";
"Middle_Name" = "MI";
"Last_Name" = "Surname"
"Job_Title" = "title";
"Role" = "Department";

Email Settings:
$to = Should Lookup the Managers email address for each imported user based on the result of the manager lookup.
avatar
Support
Nov 20, 2020
Hello Leo,

Thank you for specifying. For us to update the script according to your needs, please, specify what should be done if a manager with the specified employee ID is not found. Should the user be created with an empty Manager property? Should notifications about existing users be sent to a different email? Any additional details and live examples of the desired behavior will be much appreciated.
avatar
Leo Gomez
Nov 20, 2020
If manager does not exist - email should go to ServcieDesk@domain.com "user created no manager"
avatar
Support
Nov 20, 2020
Hello Leo,

What if not only the manager does not exist, but also the sAMAccountName is already taken? Should a corresponding email be sent to ServcieDesk@domain.com?
avatar
Leo Gomez
Nov 20, 2020
Yes, If the user "sAMAccountName" already exists it should error out and send email to "ServiceDesk@domain.com.
avatar
Support
Nov 23, 2020
Hello Leo,

Thank you for the confirmation. Please, find the updated script below.

Edit Remove
PowerShell
Import-Module Adaxes

$csvFilePath = "\\SERVER\share\ImportUsers.csv" # TODO: modify me
$customColumnNames = @{
    "JobTitle" = "title";
    "FirstName" = "givenName";
} # TODO: modify me
$aDObjectProperties = @("Manager", "Secretary") # TODO: modify me
$ouDN = "CN=Users,DC=domain,DC=com" # TODO: modify me

# E-mail settings
$to = "recipient@domain.com" # TODO: modify me
$subject = "Import report" # TODO: modify me
$reportHeader = "<h2>Import report</h2>"
$reportFooter = "<hr /><p><i>Please do not reply to this e-mail, it has been sent to you for notification purposes only.</i></p>" # TODO: modify me

$domainName = $Context.GetObjectDomain($ouDN)
$importedUsers  = Import-Csv -Path $csvFilePath

$rootDSE = $Context.BindToObject("Adaxes://RootDSE")
$managerEmailsToUsersFound = @{}
foreach ($userFromCSV in $importedUsers)
{
    $userObject = @{}
    $accountPassword = $NULL
    $propertiesToClear = @()
    $managerMail = $NULL
    foreach ($property in $userFromCSV.PSObject.Properties)
    {
        $columnName = $property.Name
        $value = $property.Value
        
        if ($customColumnNames.ContainsKey($columnName))
        {
            $propertyName = $customColumnNames[$columnName]
        }
        else
        {
            $propertyName = $columnName
        }
        
        if ([System.String]::IsNullOrEmpty($value))
        {
            $propertiesToClear += $propertyName
            continue
        }

        # Parse special columns
        if ($aDObjectProperties -icontains $columnName)
        {
            if ($propertyName -eq "manager")
            {
                $aDObject = Get-AdmObject -Filter {EmployeeID -eq $value} `
                    -AdaxesService localhost -ErrorAction SilentlyContinue -Server $domainName -Properties mail
            }
            else
            {
                $aDObject = Get-AdmObject -Filter {(Name -eq $value) -or (DisplayName -eq $value) -or (distinguishedName -eq $value)} `
                    -AdaxesService localhost -ErrorAction SilentlyContinue -Server $domainName
            }
            
            if ($NULL -eq $aDObject)
            {
                $Context.LogMessage("Could not locate object with identity '$value'.", "Warning")
                continue
            }
             
            if ($aDObject -is [System.Array])
            {
                $Context.LogMessage("Found more than one object with identity '$value'.", "Warning")
                continue
            }
            
            $value = $aDObject.DistinguishedName
            if ($propertyName -eq "manager")
            {
                $managerMail = $aDObject.Mail
            }
        }

        if ($value -ieq "True" -or $value -ieq "False")
        {
            $value = [System.Boolean]::Parse($value)
        }

        $userObject.Add($propertyName, $value)
    }
    
    if ($NULL -eq $managerMail)
    {
        $managerMail = $to
    }
    
    if (!$managerEmailsToUsersFound.ContainsKey($managerMail))
    {
        $managerEmailsToUsersFound.Add($managerMail, (New-Object "System.Text.StringBuilder"))
    }
    
    
    # Build sAMAccountName
    $sAMAccountName = $userObject.sn + $userObject.employeeID
    
    # Check whether the user exists
    $userExists = Get-AdmUser -LdapFilter "(sAMAccountName=$sAMAccountName)" `
        -AdaxesService localhost -ErrorAction SilentlyContinue -Server $domainName
    
    if ($NULL -eq $userExists)
    {
        # Build user name
        $displayName = $userObject.GivenName + " " + $userObject.SN
        $parameters = @{
            "Path" = $ouDN
            "Name" = $displayName;
            "SamAccountName" = $sAMAccountName
            "Server" = $domainName;
            "AdaxesService" = "localhost"
            "Enabled" = $True
            "OtherAttributes" = $userObject
            "ErrorAction" = "Stop"
        }
        
        # Generate password
        $userAdsPathObj = New-Object Softerra.Adaxes.Adsi.AdsPath "Adaxes://$ouDN"
        $rdnValue = [Softerra.Adaxes.Ldap.Rdn]::EscapeAttributeValue($displayName)
        $userAdsPathObj.CreateChildPath("CN=$rdnValue")
        $passwordString = $rootDSE.GeneratePasswordForNewUser($userAdsPathObj)
        $passwordSecureString = ConvertTo-SecureString -AsPlainText $passwordString -Force
        $parameters.Add("AccountPassword", $passwordSecureString)

        # Create a new user account
        try
        {
            New-AdmUser @parameters 
        }
        catch
        {
            $Context.LogMessage("An error occurred when creating user '$displayName'. Error: " + $_.Exception.Message, "Warning")
        }
    }
    else
    {
        $managerEmailsToUsersFound[$managerMail].Append("<li>$sAMAccountName</li>")
    }
}

foreach ($mail in $managerEmailsToUsersFound.Keys)
{
    if ($managerEmailsToUsersFound[$mail].Length -eq 0)
    {
        continue
    }
    
    # Build report
    $html = New-Object "System.Text.StringBuilder"
    $html.Append($reportHeader)
    $html.Append("<b>The following users were found in Active Directory:</b>")
    $html.Append("<ol>")
    $html.Append($managerEmailsToUsersFound[$mail].ToString())
    $html.Append("</ol>")
    $html.Append($reportFooter)
    
    $Context.SendMail($mail, $subject, $NULL, $html.ToString())
}


avatar
Leo Gomez
Nov 25, 2020
Thanks for the updated script!

I modified what you sent as follows:
Changed the Path to the CSV, Changed the $to and added the following:

$customColumnNames = @{
"Company"= "company"
"Employee_ID" = "employeeID";
"Location" = "Office";
"First_Name" = "GivenName";
"Middle_Name" = "middleName";
"Last_Name" = "sn"
"Job_Title" = "title";
"Role" = "Department";
"Supervisor" = "$mgrid";

$aDObjectProperties = @("Manager = Supervisor", "Secretary")
$ouDN = info was changed.

CSV Content
Company,Employee_ID,Location,First_Name,Middle_Name,Last_Name,Job_Title,Role,Supervisor
20000,128,20127,Lexie,r,Test,Test Associate,rg-20000-Campus-CaretrckrMatrix,135743
20000,133,20423,Madeline,C,Test,Resident Test Associate,rg-20000-Campus-RCACaretrackerAL,135743
20000,136,20415,Irene,M,Test,Test Services Asst,rg-20000-Campus-SmartlinxOnly,135743

When the script is run the log says completed. The users are NOT created and the execution log says:
"An error occurred when creating user 'Irene Test'. Error: Parameter must be a non empty string. Parameter name: name"
avatar
Support
Nov 26, 2020
Hello Leo,

We opened a support ticket to help you with the script. An email was sent to the address of your account.
avatar
Jay
Oct 14, 2020
Will this script only update the fields that are needed? Ideally we want to move to importing the Master Employee Record in to Adaxes and simply update only the attributes that changed. We have over 15k employees and in the past the scripts have run too long and not finished.
avatar
Support
Oct 15, 2020

Hello Jay,

The current version of the script not only updates the users, but also creates new ones. Also, it attempts to update all the properties present in the CSV file. However, the script can be updated to work exactly as you need. Still on 15k users it can take quite long.
avatar
Jay
Oct 19, 2020
Is this the best solution to keep the users updated? If not, what do you suggest?
avatar
Support
Oct 20, 2020
Hello Jay,

It totally depends on your requirements. If you have a CSV file with user data that is frequently updated, using such a script should work just fine to keep your user data up to date in Active Directory.
avatar
Jay
Oct 20, 2020
So this script as is just needs the headings of the columns to be the same as the attribute name or map them in the script. Correct? Also we will need a check added to only update the accounts where information changed.
avatar
Support
Oct 21, 2020
Hello Jay,

>So this script as is just needs the headings of the columns to be the same as the attribute name or map them in the script. Correct?

Yes, that is correct.

>Also we will need a check added to only update the accounts where information changed.

There is no need to add the check. Adaxes does not update properties to the same values. If all the properties are the same (nothing has changed), no updates will be performed.
Leave a comment