Script Repository


Import possible values for property from SQL Database

September 25, 2018
1450

The below PowerShell script updates built-in User Pattern with a list of offices specified in an SQL human resources database.

To update the office list on a regular basis, you need to schedule execution of the script. For this purpose, create a Scheduled Task configured for the Domain-DNS object type.

See Also: Automatically Set Users' Address Based on Their Office.

Parameters:

  • $databaseHost - specifies the fully qualified domain name or IP address of the database host;
  • $databaseName - specifies the database name;
  • $tableName - specifies the name of the database table that contains information on available offices;
  • $fieldName - specifies the name of the database field to pull offices from;
  • $isOfficePropertyRequired - specifies whether to set the Office attribute as required, $True or $False;
  • $databaseUsername - specifies the username to be used to connect to the database;
  • $databasePassword - specifies the database password.
Edit Remove
PowerShell
$databaseHost = "host.company.com" #TODO: modify me
$databaseName = "MyDatabase" # TODO: modify me
$tableName = "Offices"
$fieldName = "OfficeName"
$isOfficePropertyRequired = $True

# Specify credentials used to connect to the database.
# When set to $NULL, the credentials of Adaxes default service administrator  will be used
$databaseUsername = $NULL
$databasePassword = $NULL

# Get office names
$connectionString = "Data Source=$databaseHost; Initial Catalog=$databaseName;"
if ($databaseUsername -eq $NULL)
{
    $connectionString = $connectionString +
        "Integrated Security=SSPI;"
}
else
{
    $connectionString = $connectionString +
        "User ID=$databaseUsername;Password=$databasePassword;"
}

$connection = New-Object "System.Data.SqlClient.SqlConnection"  $connectionString
$connection.Open()
$command = $connection.CreateCommand()
$command.CommandText = "SELECT $fieldName FROM $tableName"
$reader = $command.ExecuteReader()
$officesName = @()

while ($reader.Read())
{
    $officesName += $reader[$fieldName]
}

$reader.Close()
$command.Dispose()
$connection.Close()
if($officesName.Length -eq 0)
{
    return # Nothing to add
}

[System.Array]::Sort($officesName)

# Modify the User Pattern

# Bind to the User Pattern
$propertyPatternsPath = $Context.GetWellKnownContainerPath("PropertyPatterns")
$propertyPatternsPathObj = New-Object "Softerra.Adaxes.Adsi.AdsPath" $propertyPatternsPath
$builtinPathObj = $propertyPatternsPathObj.CreateChildPath("CN=Builtin")
$userPatternPath = $builtinPathObj.CreateChildPath("CN=User Pattern")
$userPattern = $Context.BindToObject($userPatternPath)

# Check whether an item exists for the Office property
# If it exists, remove it
foreach ($item in $userPattern.Items)
{
    if($item.PropertyName -eq "physicalDeliveryOfficeName")
    {
        $userPattern.Items.Remove($item)
        break
    }
}

# Create a new item with ne values for the Office property
$item = $userPattern.Items.Create()
$item.PropertyName = "physicalDeliveryOfficeName"
$item.IsPropertyRequired = $isOfficePropertyRequired
$constraints = $item.GetConstraints()
$constraint = $constraints.Create("ADM_PROPERTYCONSTRAINTTYPE_VALUERANGE")
$constraint.AreValuesDenied = $False
$constraint.Values = $officesName
$constraints.Add($constraint)
$item.SetConstraints($constraints)
$item.SetInfo()
$userPattern.Items.Add($item)

Comments ( 0 )
No results found.
Leave a comment