Script Repository


Import new and updated users from CSV sent by e-mail

April 30, 2020
1951

The script imports user accounts from a CSV file sent by mail. For the script to be able to download the emails, they need to have a certain predefined subject, and the Adaxes service account must have full access to the mailbox where they are sent.

The script will update existing accounts based on the data in the CSV file, and non-existing ones will be created in AD. You can use any property of AD user accounts to locate users in AD, such as their username (LDAP attribute sAMAccountName or userPrincipalName), full name (sn), employee ID (employeeID) etc.

See Also: Import new user account from email message.

For details on how to schedule import of users with the help of the script, see Schedule Import of Users from a CSV File.

To be able to launch the script, download and install Microsoft Exchange Web Services Managed API on the computer where Adaxes service runs. Also, you need to install the Adaxes PowerShell Module for Active Directory.

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

Parameters:

  • $exchangeWebServiceDllPath - specifies the full path to the Microsoft Exchange Web Services dll module;
  • $exchangeServer - specifies the fully qualified domain name (FQDN) of your Exchange Server;
  • $searchSubject - specifies the subject of emails that contain CSV files with user data;
  • $mailboxMailAdress - specifies the email address of the mailbox where the emails will be sent;
  • $accountPasswordColumn - specifies the name of the column that contains account passwords;
  • $userIdColumn - specifies the name of the column that contains the property to identify existing users by;
  • $userIdProperty - specifies the LDAP display name of the property to identify existing users by;
  • $otherPropertiesMap - specifies a map of column headers and LDAP display names of the corresponding user account properties. If a column header is not specified in this list, it will be used as a LDAP display name of a property;
  • $aDObjectProperties - specifies a list of headers of the columns that contain references to other AD objects, such as, for example, Manager, Secretary or Assistant. Objects can by specified in these columns by their:
    • Distinguished Name
    • Full name (cn attribute)
    • Display name
  • $tempCSVFilePath - specifies a path to the temporary CSV file where the email content will be saved before import.
Edit Remove
PowerShell
Import-Module Adaxes

# Exchange settings
$exchangeWebServiceDllPath = "C:\Program Files\Microsoft\Exchange\Web Services\2.0\Microsoft.Exchange.WebServices.dll" # TODO: modify me
$exchangeServer = "exchangeserver.example.com" # TOOD: modify me. If $NULL connect to Exchange Online
$searchSubject = "User Export File" # TOOD: modify me
$mailboxMailAddress = "recipient@example.com" # TOOD: modify me

# CSV Settings
$accountPasswordColumn = "AccountPassword" # TODO: modify me
$userIdColumn = "EmployeeName" # TODO: modify me
$userIdProperty = "sAMAccountName" # TODO: modify me
$otherPropertiesMap = @{
    "Job Title" = "title";
    "First Name" = "givenName";
    "Last Name" = "sn";
} # TODO: modify me. Use this parameter if column name is not equal to LDAP display name of attribute. Example $otherPropertiesMap = @{"<Column Name>" = "ldapName";}
$aDObjectNameProperties = @("Manager") # TODO: modify me. Example $aDObjectNameProperties = @("<column 1>", "<column 2>")
$tempCSVFilePath = "C:\Scripts\temp.csv" # TODO: modify me

# Script block to get CSV file from email message
function GetCSVFile($exchangeServer, $searchSubject, $mailboxMailAddress, $tempCSVFilePath, $exchangeWebServiceDllPath)
{
    Import-Module $exchangeWebServiceDllPath
    
    if ($exchangeServer -eq $NULL)
    {
        # Connect to Exchange Online via the Exchange Web Services API
        $exchangeWebService = New-Object Microsoft.Exchange.WebServices.Data.ExchangeService
        $microsoft365Cred = $Context.GetOffice365Credential()
        if ($microsoft365Cred -eq $NULL)
        {
            $Context.LogMessage("Microsoft 365 credentials not set", "Warning")
            return
        }
        
        $exchangeWebService.Credentials = New-Object System.Net.NetworkCredential($microsoft365Cred.Username, $microsoft365Cred.GetNetworkCredential().Password)
        $exchangeWebService.Url = "https://outlook.office365.com/EWS/Exchange.asmx"
    }
    else
    {
        # Connect to Exchange on-premises via the Exchange Web Services API
        $exchangeWebService = New-Object Microsoft.Exchange.WebServices.Data.ExchangeService([Microsoft.Exchange.WebServices.Data.ExchangeVersion]::Exchange2010)
        $exchangeWebService.Url = "https://$exchangeServer/ews/exchange.asmx"
    }

    # Build filter
    $fistSearchFilter = New-Object Microsoft.Exchange.WebServices.Data.SearchFilter+IsEqualTo([Microsoft.Exchange.WebServices.Data.EmailMessageSchema]::HasAttachments, $true)
    $secondSearchFilter = New-Object Microsoft.Exchange.WebServices.Data.SearchFilter+IsEqualTo([Microsoft.Exchange.WebServices.Data.EmailMessageSchema]::Subject, $searchSubject)
    $searchFilterCollection = New-Object Microsoft.Exchange.WebServices.Data.SearchFilter+SearchFilterCollection([Microsoft.Exchange.WebServices.Data.LogicalOperator]::And)
    $searchFilterCollection.Add($fistSearchFilter)
    $searchFilterCollection.Add($secondSearchFilter)
    
    # Bind to the Inbox folder
    $folderID = New-Object Microsoft.Exchange.WebServices.Data.FolderId([Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::Inbox, $mailboxMailAddress)
    $inbox = [Microsoft.Exchange.WebServices.Data.Folder]::Bind($exchangeWebService, $folderID)
    
    # Set order
    $itemView = New-Object Microsoft.Exchange.WebServices.Data.ItemView(1)
    $itemView.OrderBy.Add([Microsoft.Exchange.WebServices.Data.ItemSchema]::DateTimeReceived, [Microsoft.Exchange.WebServices.Data.SortDirection]::Descending)
    
    # Find message
    $searchResults = $inbox.FindItems($searchFilterCollection, $itemView)
    if ($searchResults.Items.Count -eq 0)
    {
        return # No mail messages with the specified subject
    }
    
    # Get attachment
    $item = $searchResults.Items[0]
    $item.Load()
    $attachment = $item.Attachments[0]
    $attachment.Load()
    
    # Remove message
    $item.Delete("MoveToDeletedItems")
        
    # Write result to file
    try
    {
        $csvFile = New-Object System.IO.FileStream($tempCSVFilePath, [System.IO.FileMode]::Create)
        $csvFile.Write($attachment.Content, 0, $attachment.Content.Length)
        $csvFile.Close()
    }
    catch
    {
        $Context.LogMessage("An error occurred when creating a temporary CSV file. Error: " + $_.Exception.Message, "Warning")
        return
    }
}

# Download CSV file
GetCSVFile $exchangeServer $searchSubject $mailboxMailAddress $tempCSVFilePath $exchangeWebServiceDllPath

if (-not (Test-Path $tempCSVFilePath))
{
    return
}

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

foreach ($userFromCSV in $importedUsers)
{
    $userObject = @{}
    $accountPassword = $NULL
    $propertiesToClear = @()
    foreach ($property in $userFromCSV.PSObject.Properties)
    {
        $columnName = $property.Name
        $value = $property.Value
        
        # Convert password value to secure string
        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
        }
        
        # Replace column name with attribute name
        if ($otherPropertiesMap.ContainsKey($columnName))
        {
            $propertyName = $otherPropertiesMap[$columnName]
        }
        else
        {
            $propertyName = $columnName
        }
        
        # Decide whether the attribute needs to be cleared
        if ([System.String]::IsNullOrEmpty($value))
        {
            $propertiesToClear += $propertyName
            continue
        }

        # Parse columns that contain references to AD objects
        if ($columnName -ieq $userIdColumn)
        {
            $propertyName = $userIdProperty
        }

        elseif ($aDObjectNameProperties -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("Object with identity '$value' not found.", "Warning")
                continue
            }
            
            $value = $aDObject.DistinguishedName
        }

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

        # Add attribute to property list
        $userObject.Add($propertyName, $value)
    }
    
    # Check whether the user exists
    $valueForSearch = $userObject.$userIdProperty
    $userExists = Get-AdmUser -LdapFilter "($userIdProperty=$valueForSearch)" `
        -AdaxesService localhost -Server $domainName
    
    if ($userExists -eq $NULL)
    {
        # Build user name
        $displayName = $userObject.GivenName + " " + $userObject.SN # TODO: modify me
        $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")
    }
    
    # Clear properties
    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
    }
    
    # Update password
    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")
    }
}

# Remove the temporary CSV file
Remove-Item $tempCSVFilePath -Force


Comments ( 0 )
No results found.
Leave a comment