The script export user accounts located in a certain Organizational Unit to an SQL database table. To import user photos on demand, you can create a custom command for Organizational Unit objects that runs the script. To schedule import of user photos, create a scheduled task for Organizational Unit objects and include the OU or OUs that you want to export in the Activity Scope of the task.
Note: The table will be dropped each time the script runs, and a new table with the same name will be created instead.
To add the script to your command or task, use the Run a program or PowerShell script action.
Parameters:
- $databaseHost - Specifies the fully qualified domain name of the host where the database is hosted.
- $databaseName - Specifies the database name.
- $columnMap - Specifies names of columns in the database, corresponding attribute LDAP names, database data format and length.
- $tableName - Specifies the table name.
You can use value references in the table name. When the script is executed, the value references are replaced with property values of the Organizational Unit on which it is executed. For example, if you specify %name%, the table name is the same as the Organizational Unit name.
- $databaseUsername - Specifies the username to use when connecting to the database.
- $databasePassword - Specifies a password to the account specified in $databaseUsername.
If $databaseUsername and $databasePassword are set to $NULL, the credentials of the Adaxes service account will be used.
PowerShell
$databaseHost = "sql.example.com" # TODO: modify me
$databaseName = "My Database" # TODO: modify me
$tableName = "%name%" # TODO: modify me
$databaseUsername = $NULL # TODO: modify me
$databasePassword = $NULL # TODO: modify me
# If set to $NULL, the credentials of the Adaxes service
# account will be used to connect to the database
$columnMap = @{
"employeeID" = "employeeID", "varchar(MAX)";
"company" = "company", "varchar(MAX)";
"department" = "department", "varchar(MAX)";
"description" = "description", "varchar(MAX)";
"displayName" = "displayName", "varchar(MAX)";
"distinguishedName" = "distinguishedName", "varchar(MAX)";
"cn" = "cn", "varchar(MAX)";
"extensionAttribute1" = "extensionAttribute1", "varchar(MAX)";
"extensionAttribute2" = "extensionAttribute2", "varchar(MAX)";
"extensionAttribute3" = "extensionAttribute3", "varchar(MAX)";
"extensionAttribute4" = "extensionAttribute4", "varchar(MAX)";
"extensionAttribute5" = "extensionAttribute5", "varchar(MAX)";
"extensionAttribute6" = "extensionAttribute6", "varchar(MAX)";
"extensionAttribute7" = "extensionAttribute7", "varchar(MAX)";
"extensionAttribute8" = "extensionAttribute8", "varchar(MAX)";
"extensionAttribute9" = "extensionAttribute9", "varchar(MAX)";
"extensionAttribute10" = "extensionAttribute10", "varchar(MAX)";
"givenName" = "givenName", "varchar(MAX)";
"homeDirectory" = "homeDirectory", "varchar(MAX)";
"homePhone" = "homePhone", "varchar(MAX)";
"initials" = "initials", "varchar(MAX)";
"lastLogon" = "lastLogon", "datetime";
"mail" = "mail", "varchar(MAX)";
"physicalDeliveryOfficeName" = "physicalDeliveryOfficeName", "varchar(MAX)";
"postalCode" = "postalCode", "varchar(MAX)";
"profilePath" = "profilePath", "varchar(MAX)";
"sAMAccountName" = "sAMAccountName", "varchar(MAX)";
"scriptPath" = "scriptPath", "varchar(MAX)";
"sn" = "sn", "varchar(MAX)";
"streetAddress" = "streetAddress", "varchar(MAX)";
"st" = "st", "varchar(MAX)";
"telephoneNumber" = "telephoneNumber", "varchar(MAX)";
"title" = "title", "varchar(MAX)";
"userPrincipalName" = "userPrincipalName", "varchar(MAX)";
"whenCreated" = "whenCreated", "datetime";
"postOfficeBox" = "postOfficeBox", "varchar(MAX)";
"division" = "division", "varchar(MAX)";
"admCustomAttributeTextMultiValue1" = "adm-CustomAttributeTextMultiValue1", "varchar(MAX)";
} # TODO: modify me. Example @{"<column name>" = "<LDAP attribute name>", "<SQL data_type(size)>"}
# Connect to the database
$connectionString = "Data Source=$databaseHost; Initial Catalog=$databaseName;"
if ($databaseUsername -eq $NULL)
{
$connectionString = $connectionString + "Integrated Security=SSPI;"
}
else
{
$connectionString = $connectionString +
"User ID=$databaseUsername;Password=$databasePassword;"
}
try
{
$connection = New-Object "System.Data.SqlClient.SqlConnection" $connectionString
$connection.Open()
# Drop existing table
try
{
$dropTableCommand = $connection.CreateCommand()
$dropTableCommand.CommandText = "IF OBJECT_ID('$tableName', 'U') IS NOT NULL DROP TABLE ""$tableName"""
$dropTableCommand.ExecuteScalar()
}
catch
{
$Context.LogMessage("Could not drop table '$tableName'. Error: " + $_.Exception.Message, "Warning")
return
}
finally
{
# Release resources
if ($dropTableCommand) { $dropTableCommand.Dispose() }
}
# Create new table
# Build command to create a new table
$createTableCommandText = New-Object "System.Text.StringBuilder"
[void]$createTableCommandText.Append("CREATE TABLE ""$tableName"" (")
$columnNames = $columnMap.Keys
$propertyNames = @()
foreach ($columnName in $columnNames)
{
$columnInfo = $columnMap[$columnName]
$propertyName = $columnInfo[0]
$dataType = $columnInfo[1]
[void]$createTableCommandText.Append("$columnName $dataType,")
$propertyNames += $propertyName
}
[void]$createTableCommandText.Append(")")
# Execute command
try
{
$createTableCommand = $connection.CreateCommand()
$createTableCommand.CommandText = $createTableCommandText.ToString()
$createTableCommand.ExecuteScalar()
}
catch
{
$Context.LogMessage("Could not create table '$tableName'. Error: " + $_.Exception.Message, "Warning")
return
}
finally
{
# Release resources
if ($createTableCommand) { $createTableCommand.Dispose() }
}
# Search users in the OU
$searcher = $Context.TargetObject
$searcher.SearchFilter = "(sAMAccountType=805306368)"
$searcher.SearchScope = "ADS_SCOPE_SUBTREE"
$searcher.PageSize = 500
$searcher.ReferralChasing = "ADS_CHASE_REFERRALS_NEVER"
$searcher.SetPropertiesToLoad($propertyNames)
try
{
$searchResultIterator = $searcher.ExecuteSearch()
$searchResults = $searchResultIterator.FetchAll()
foreach ($searchResult in $searchResults)
{
# Add the user to table
# Build insert command
$updateTableCommandText = New-Object "System.Text.StringBuilder"
[void]$updateTableCommandText.Append("INSERT INTO ""$tableName"" (")
[void]$updateTableCommandText.Append($columnNames -join ",")
[void]$updateTableCommandText.Append(") VALUES(")
$parameters = $columnNames | %%{"@" + $_}
[void]$updateTableCommandText.Append($parameters -join ",")
[void]$updateTableCommandText.Append(")")
try
{
$updateTableCommand = $connection.CreateCommand()
$updateTableCommand.CommandText = $updateTableCommandText.ToString()
foreach ($columnName in $columnNames)
{
$propertyName = $columnMap[$columnName][0]
$valuesFromAD = $searchResult.Properties[$propertyName].Values
if ($valuesFromAD -eq $NULL)
{
$value = [DBNull]::Value
}
elseif ($propertyName -eq "lastLogon")
{
$valueFromAD = $valuesFromAD[0]
if (($valueFromAD -eq 0) -or ($valueFromAD -eq 9223372036854775807))
{
$value = [DBNull]::Value
}
else
{
# Convert ADS Large Integer to DateTime
$value = [DateTime]::FromFiletime([Int64]::Parse($valueFromAD))
}
}
elseif ($valuesFromAD.Count -gt 1)
{
$value = $valuesFromAD -join ","
}
else
{
$value = $valuesFromAD[0]
}
$parameterName = "@$columnName"
[void]$updateTableCommand.Parameters.Add($parameterName, $value)
}
# Execute command
$updateTableCommand.ExecuteScalar()
}
catch
{
$userIdentity = $Context.GetDisplayNameFromAdsPath($searchResult.AdsPath)
$Context.LogMessage("An error occurred when inserting user $userIdentity in table '$tableName'. Error: " + $_.Exception.Message, "Warning")
continue
}
finally
{
# Release resources
if ($updateTableCommand) { $updateTableCommand.Dispose() }
}
}
}
finally
{
$searchResultIterator.Dispose()
}
}
finally
{
$connection.Close()
}