📘
Note
This script is provided as an example of how XML can be generated from different sources for use with Interacts profile sources. This is not supported by Interact, therefore, you should feel free to use this as a starting point and tweak as necessary.

Using PowerShell, it's possible to convert an Excel spreadsheet containing user details into an XML file that Interact can process for user synchronization.

The script

Let's start by looking at the script before breaking it down into more detail...

# Excel details
$excelPath = "{FULL PATH TO EXCEL WORKBOOK}"
$sheetName = "{NAME OF SHEET IN EXCEL}"

$usernameCell   = "{COLUMN HOLDING USERNAME}" # E.g. 1 or "A" depending on the sheet's reference style
$firstNameCell  = "{COLUMN HOLDING FIRSTNAME}"
$surnamecell    = "{COLUMN HOLDING SURNAME}"
$emailCell      = "{COLUMN HOLDING EMAIL}"
$managerCell    = "{COLUMN HOLDING MANAGER}"
$jobTitleCell   = "{COLUMN HOLDING JOB TITLE}"
$departmentCell = "{COLUMN HOLDING DEPARTMENT}"
$locationCell   = "{COLUMN HOLDING LOCATION}"
$companyCell    = "{COLUMN HOLDING COMPANY}"

# Hash table for additional fields, key is the field name, value is the number of the cell holding the data
$additionalFields = @{"first_aider" = 13; "employee_id" = 14}

#Interact details
$uri             = "{URL FOR GENERAL PROFILE SOURCES API}" # E.g. "https://{{intranet_url}}/api/umi/{{source_id}}/upload"
$authtoken       = "{AUTHENTICATION KEY AS DEFINED IN PROFILE SOURCES}" # E.g. "12345" or "78fSfjsQhmBM" or any other value
$domain          = "{NAME OF GENERAL PROFILE SOURCE}" 
$ldapId          = "{SOURCE ID OF GENERAL PROFILE SOURCE}" 
$xmlPath         = "{FULL PATH TO XML FILE TO CREATE}"
$groupName       = "{GROUP NAME WITHIN INTERACT}" # E.g. "Intranet Users"
$newUserPassword = "{PASSWORD TO ASSIGN TO NEW USERS}" # Can be excluded, but only if the sync option and password elements are adjusted accordingly

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

function Write-SyncOption([System.Xml.XmlTextWriter] $writer, [string] $optionName, [string] $optionValue){
    # Write a syncoption element using the passed values
    $writer.WriteStartElement('option')
    $writer.WriteAttributeString('name', $optionName)
    $writer.WriteString($optionValue)
    $writer.WriteEndElement()
}


function Write-DocumentBase([System.Xml.XmlTextWriter] $writer){
    # Write the document root element
    # Write the syncoptions element   
    $writer.WriteStartElement('syncdata')
    $writer.WriteAttributeString('version', '1')

    $writer.WriteStartElement('syncoptions')
    $writer.WriteAttributeString('domain', $domain)
    $writer.WriteAttributeString('ldapid', $ldapId)

    Write-SyncOption $writer 'syncCompanies' 'true'
    Write-SyncOption $writer 'syncLocations' 'true'
    Write-SyncOption $writer 'syncDepartments' 'true'
    Write-SyncOption $writer 'syncManagers' 'true'
    Write-SyncOption $writer 'actionDisabledUsers' 'd'
    Write-SyncOption $writer 'actionMissingDeletedUsers' 'd'
    Write-SyncOption $writer 'loginType' '1'
    Write-SyncOption $writer 'defaultCulture' '1'
    Write-SyncOption $writer 'newUserPasswordBehaviour' 'strict'

    $writer.WriteEndElement()
}


function Write-AdditionalField([System.Xml.XmlTextWriter] $writer, [string] $fieldName, [string] $fieldValue){
    # Write an additional field element using the passed values
    $writer.WriteStartElement('field')
    $writer.WriteAttributeString('name', $fieldName)
    $writer.WriteString($fieldValue)
    $writer.WriteEndElement()
}


function Write-ManagerElement([System.Xml.XmlTextWriter] $writer, [string] $manager){
    # Write the manager element for a user
    $writer.WriteStartElement('manager')
    if (![string]::IsNullOrWhiteSpace($manager) -and ($manager.ToLower() -ne 'undefined')) {
        $writer.WriteAttributeString('uid', $manager)
        $writer.WriteAttributeString('dn', '')
        $writer.WriteAttributeString('username', $manager)
        $writer.WriteAttributeString('email', $manager)
    } else {
        $writer.WriteAttributeString('uid', '')
        $writer.WriteAttributeString('dn', '')
        $writer.WriteAttributeString('username', '')
        $writer.WriteAttributeString('email', '')
    }
    $writer.WriteEndElement()        
}


function Write-PrimaryOrganisationElement([System.Xml.XmlTextWriter] $writer, [string] $organisationType, [string] $organisationValue){    
    # Write an organisation element using the passed values 
    $writer.WriteStartElement('organisation')
    $writer.WriteAttributeString('type', $organisationType)
    $writer.WriteAttributeString('primary', 'true')
    $writer.WriteString($organisationValue)
    $writer.WriteEndElement()
}


function Write-OrganisationsElement([System.Xml.XmlTextWriter] $writer, [string] $department, [string] $company, [string] $location){
    # Write the organisations element for a user
    $writer.WriteStartElement('organisations')

    if (![string]::IsNullOrWhiteSpace($department)) {
        Write-PrimaryOrganisationElement $writer 'department' $department
    }

    if (![string]::IsNullOrWhiteSpace($company)) {
        Write-PrimaryOrganisationElement $writer 'company' $company
    }

    if (![string]::IsNullOrWhiteSpace($location)) {
        Write-PrimaryOrganisationElement $writer 'location' $location
    }

    $writer.WriteEndElement()
}


function Write-UsersElement([System.Xml.XmlTextWriter] $writer, [System.Object] $sh){
    # Write the users element of the document
    $rows = ($sh.UsedRange.Rows).count

    $writer.WriteStartElement('users')
    $writer.WriteAttributeString('TotalUsers', $rows-1)
    for($row = 2 ; $row -le $rows ; $row++)
    {
        $username = $sh.Cells.Item($row,$usernameCell).value2
        $firstname = $sh.Cells.Item($row,$firstNameCell).value2
        $surname = $sh.Cells.Item($row,$surnamecell).value2
        $email = $sh.Cells.Item($row,$emailCell).value2
        $manager = $sh.Cells.Item($row,$managerCell).value2
        $jobTitle = $sh.Cells.Item($row,$jobTitleCell).value2
        $department = $sh.Cells.Item($row,$departmentCell).value2
        $location = $sh.Cells.Item($row,$locationCell).value2
        $company = $sh.Cells.Item($row,$companyCell).value2
                
        Write-Host "Processing user " $userName $email
        # Create user element with attributes
        $writer.WriteStartElement('user')
        $writer.WriteAttributeString('uid', $username)
        $writer.WriteAttributeString('dn', '')
        $writer.WriteAttributeString('username', $username)
        $writer.WriteAttributeString('email', $email)

        # Call the function to write person specific elements
        $writer.WriteStartElement('person')
        $writer.WriteElementString('firstname', $firstname)
        $writer.WriteElementString('surname', $surname)
        $writer.WriteElementString('title', '')
        $writer.WriteElementString('initials', '')
        $writer.WriteElementString('jobtitle', $jobTitle)
        $writer.WriteElementString('phone', '')
        $writer.WriteElementString('mobile', '')
        $writer.WriteElementString('fax', '')
        $writer.WriteElementString('extension', '')
        $writer.WriteElementString('address', '')
        $writer.WriteEndElement()

        # Create user specific elements
        $writer.WriteElementString('statusenabled', 'true')
        $writer.WriteElementString('password', $newUserPassword)
        $writer.WriteElementString('culture', '1')
        $writer.WriteStartElement('language')
        $writer.WriteAttributeString('id', '-1')
        $writer.WriteEndElement()
        
        # Create additional fields examples
        $writer.WriteStartElement('additionalfields')

        foreach ($af in $additionalFields.GetEnumerator()) {
            $afValue = $sh.Cells.Item($row,$af.Value).value2
            Write-AdditionalField $writer $af.Name $afValue
        }

        $writer.WriteEndElement()

        # Call the function to write the manager element
        Write-ManagerElement $writer $manager

        # Call the function to write the organisations element
        Write-OrganisationsElement $writer $department $company $location

        $writer.WriteEndElement()
    }
    $writer.WriteEndElement()
}


function Write-GroupsElement([System.Xml.XmlTextWriter] $writer, [System.Object] $sh){
    # Write the groups element of the document
    $writer.WriteStartElement('groups')
    Write-Host "Building group"
    
    $rows = ($sh.UsedRange.Rows).count

    $writer.WriteAttributeString('TotalUsers', $rows-1)
    $writer.WriteAttributeString('TotalGroups', 1)
    
    Write-Host ""
    Write-Host "Processing group "
    # Create group element with attributes
    $writer.WriteStartElement('group')
    $writer.WriteAttributeString('uid', "group1")
    $writer.WriteAttributeString('dn', "")
    $writer.WriteAttributeString('name', $groupName)
    $writer.WriteAttributeString('UserCount', $rows)

    $writer.WriteStartElement('users')

    for($row = 2 ; $row -le $rows ; $row++)
    {
        # Create a user element for each member
        $username = $sh.Cells.Item($row,$usernameCell).value2
        $email = $sh.Cells.Item($row,$emailCell).value2
        
        $writer.WriteStartElement('user')
        $writer.WriteAttributeString('uid', $username)
        $writer.WriteAttributeString('dn', '')
        $writer.WriteAttributeString('username', $username)
        $writer.WriteAttributeString('email', $email) # doesn't include this on member
        $writer.WriteEndElement()

    }
    $writer.WriteEndElement()

    $writer.WriteEndElement()
}

###########################################
#
# The main execution sequence of the script
#

Write-Host "Starting..."

Write-Host "Setting up XML document..."

# Set up the XML document
$xmlWriter = New-Object System.Xml.XmlTextWriter($xmlPath, $null)
$xmlWriter.Formatting = 'Indented'
$xmlWriter.Indentation = 1
$XmlWriter.IndentChar = "`t"
$xmlWriter.WriteStartDocument()

# Call the major work functions to build the document
Write-DocumentBase $xmlWriter

$excel=new-object -com excel.application
$wb=$excel.workbooks.open($excelPath)

$sh=$wb.Sheets.Item($sheetName)

Write-Host "Processing users..."
Write-UsersElement $xmlWriter $sh

Write-Host "Processing groups..."
Write-GroupsElement $xmlWriter $sh

$wb.Close()
$excel.Workbooks.Close()


# Final tidy up actions
$xmlWriter.WriteEndElement()
$xmlWriter.Flush()
$xmlWriter.Close()

Write-Host "Complete"

# Deliver file to API endpoint
Invoke-RestMethod -Uri $uri -Method Post -InFile $xmlPath -ContentType "multipart/form-data" -Headers @{'X-ApiKey'=$authtoken; }

Script walkthrough

Let's look at the script in a little more detail...

Set up

First, we set up some variables that relate to your particular instance on Interact and your particular spreadsheet that contains the user information

# Excel details
$excelPath = "{FULL PATH TO EXCEL WORKBOOK}"
$sheetName = "{NAME OF SHEET IN EXCEL}"

$usernameCell   = "{COLUMN HOLDING USERNAME}" # E.g. 1 or "A" depending on the sheet's reference style
$firstNameCell  = "{COLUMN HOLDING FIRSTNAME}"
$surnamecell    = "{COLUMN HOLDING SURNAME}"
$emailCell      = "{COLUMN HOLDING EMAIL}"
$managerCell    = "{COLUMN HOLDING MANAGER}"
$jobTitleCell   = "{COLUMN HOLDING JOB TITLE}"
$departmentCell = "{COLUMN HOLDING DEPARTMENT}"
$locationCell   = "{COLUMN HOLDING LOCATION}"
$companyCell    = "{COLUMN HOLDING COMPANY}"

# Hash table for additional fields, key is the field name, value is the number of the cell holding the data
$additionalFields = @{"first_aider" = 13; "employee_id" = 14}

#Interact details
$uri             = "{URL FOR GENERAL PROFILE SOURCES API}" # E.g. "https://{{intranet_url}}/api/umi/{{source_id}}/upload"
$authtoken       = "{AUTHENTICATION KEY AS DEFINED IN PROFILE SOURCES}" # E.g. "12345" or "78fSfjsQhmBM" or any other value
$domain          = "{NAME OF GENERAL PROFILE SOURCE}" 
$ldapId          = "{SOURCE ID OF GENERAL PROFILE SOURCE}" 
$xmlPath         = "{FULL PATH TO XML FILE TO CREATE}"
$groupName       = "{GROUP NAME WITHIN INTERACT}" # E.g. "Intranet Users"
$newUserPassword = "{PASSWORD TO ASSIGN TO NEW USERS}" # Can be excluded, but only if the sync option and password elements are adjusted accordingly

Then, a series of functions are defined that generate the XML for a user within the spreadsheet and the various synchronization options from within profile sources.

Synchronization options

The functions for generating the XML for users and groups should be fairly self-explanatory so let's look at the various options

function Write-DocumentBase([System.Xml.XmlTextWriter] $writer){
    # Write the document root element
    # Write the syncoptions element 
    $xmlWriter.WriteStartElement('syncdata')
    $xmlWriter.WriteAttributeString('version', '1')

    $xmlWriter.WriteStartElement('syncoptions')
    $xmlWriter.WriteAttributeString('domain', $domain)
    $xmlWriter.WriteAttributeString('ldapid', $ldapId)

    Write-SyncOption $xmlWriter 'syncCompanies' 'true'
    Write-SyncOption $xmlWriter 'syncLocations' 'true'
    Write-SyncOption $xmlWriter 'syncDepartments' 'true'
    Write-SyncOption $xmlWriter 'syncManagers' 'true'
    Write-SyncOption $xmlWriter 'actionDisabledUsers' 'x'
    Write-SyncOption $xmlWriter 'actionMissingDeletedUsers' 'x'
    Write-SyncOption $xmlWriter 'loginType' '0'
    Write-SyncOption $xmlWriter 'defaultCulture' '1'
    Write-SyncOption $xmlWriter 'newUserPasswordBehaviour' 'strict'

    $xmlWriter.WriteEndElement()
}

When using profile sources, Interact takes the options from the XML source itself rather than from the screens within Interact. Each option above is described a little below

syncCompanies - this tells Interact whether or not to synchronize user companies. If a company does not exist in Interact it will be automatically created and the user assigned to that company.
syncLocations - this tells Interact whether or not to synchronize user Locations. Again, if a location does not already exist in Interact it will be automatically created and the user assigned to that location.
syncDepartments - this tells Interact whether or not to synchronize user departments. As above, if a department does not already exist in Interact it will be automatically created and the user assigned to that location.
actionDisabledUsers - this tells Interact what to do with users that are marked in the XML as disabled. Options here are:
- x - do nothing - do not update the interact status of those users
- d - deactive those users within Interact
- a - deactivate and archive users within Interact
actionMissingDeletedUsers - this tells Interact what to do with users that have either been previously created in Interact with this profile source, or are not assigned to any groups within the XML. Options for this are the same as above.
loginType - this needs to be present and set to the appropriate authentication method - please see the schema documentation for more information
defaultCulture - this needs to be present and set to the appropriate culture - please see the schema documentation for more information
newUserPasswordBehaviour - this tells Interact how to create passwords for new users. Options here are
- strict - Use the password that is specified in the user part of the XML
- random - Create a random password for each user

The main part of the script

The main part of the script is found at the bottom which actually handles the generation of the XML file and sending the data to Interact

#
# The main execution sequence of the script
#

Write-Host "Starting..."

Write-Host "Setting up XML document..."

# Set up the XML document
$xmlWriter = New-Object System.Xml.XmlTextWriter($xmlPath, $null)
$xmlWriter.Formatting = 'Indented'
$xmlWriter.Indentation = 1
$XmlWriter.IndentChar = "`t"
$xmlWriter.WriteStartDocument()

# Call the major work functions to build the document
Write-DocumentBase $xmlWriter

$excel=new-object -com excel.application
$wb=$excel.workbooks.open($excelPath)

$sh=$wb.Sheets.Item($sheetName)

Write-Host "Processing users..."
Write-UsersElement $xmlWriter $sh

Write-Host "Processing groups..."
Write-GroupsElement $xmlWriter $sh

$wb.Close()
$excel.Workbooks.Close()


# Final tidy up actions
$xmlWriter.WriteEndElement()
$xmlWriter.Flush()
$xmlWriter.Close()

Write-Host "Complete"

# Deliver file to API endpoint
Invoke-RestMethod -Uri $uri -Method Post -InFile $xmlPath -ContentType "multipart/form-data" -Headers @{'X-ApiKey'=$authtoken; }

This part of the script sets up the XML writer to generate the file, then opens up the excel workbook and relevant sheet, builds the and XML elements by calling the relevant functions, and finally sends the file to Interact as a POST request with the appropriate headers set.

Troubleshooting

Powershell is surprisingly good at reporting errors from the script itself, and Interact returns useful error messages if the XML is invalid so troubleshooting should be fairly straightforward - take a look at Troubleshooting XML data for more information.

Excel (via Powershell)

📘
Note

The script

Script walkthrough

Set up

Synchronization options

The main part of the script

Troubleshooting

📘Note

The script

Script walkthrough

Set up

Synchronization options

The main part of the script

Troubleshooting

📘
Note