Import Constraints

Question

So I will explain the end goal and then my thoughts and question. I open for suggestions.

We have a SQL DB with our office names and addresses. I would like to import that data so that when a new user is created or current user is modified the admin can select the office name from a drop down and the address field will auto populate. The problem is keeping the info up to date in Adaxes. I am the Sys Admin and we do not have a lot of programming time right now to setup a web service for this so for a stop gap I was thinking:

Export the data from the SQL server with a PS script and import it into Adaxes.

The question I have is this the next best way?
How would I update the constraints in the User Property patterns?
- I found the ADSI commands would I use PowerShell to update the constraints through ADSI?

Comments

Hello,

You can achieve the functionality with the help of PowerShell scripts.

First of all, you can use a Scheduled Task. The Task will poll your SQL database at certain periods of time and will update the User Property Pattern with the list of offices contained in the database.

Then, you can create a Business Rule that will be triggered After Creating a User. The Business Rule will read the office that was selected for the user during creation and poll the database for what address should be set for this office, and then will set the appropriate address. Take a look at the Automatically Set Users' Address Based on Their Office Tutorial. It contains instructions on how to create such a Business Rule. In the Tutorial, the offices and addresses for them are hard-coded, but it is also possible to poll a database to get the addresses.

Both the Scheduled Task and the Business Rule will require scripts to implement. We will write the scripts for you. For this purpose we need a couple of clarifications:

1. What database engine do you use for your SQL database? Is it MS SQL or something else?
2. Can you describe in more detail how the offices are mapped to their addresses in your database? Are they stored in the same table or different tables? Please provide as much detail as you can on this.

---

First off thank you for your quick reply and I should have know you had a tutorial for this :D I am gathering the information and will post back with the info as soon as I get it.

---

OK I am back we are using MS SQL and this is the info about the tables:

The "Offices table" is the table that holds each office.
For the address you will want to select from the "OfficeLanguages table" by both "OfficeID" and "LanguageID" (to ensure you get 1 address and it’s the default)
For the country you will want to grab from the "Countries table" based on the "CountryID" in the "Offices table".

Table Name: Offices                             
OfficeId    ADOfficeName    CountryId   PhoneNumber  FaxNumber                          

Table Name: OfficeLanguages                             
OfficeId    DisplayName StreetNumber    StreetName  Address2    City    StateProvince   PostalCode  DisplayAddress

Table Name: Countries                               
CountryId   Name                                                    

Let me know if you need anything else and thank you. I think with this sample script I will be able to model my other auto import task from it and it seems like this would be helpful example for other users as well.

---

Hello,

We've transferred the info to our scripting guy and assigned the task to write the script for you.

The whole of our team is currently overloaded with tasks for the new Adaxes 2013.1 release to make it available on Monday, including all scripting guys, and they'll be able to proceed to your script only after the new release is available. We'll update this forum topic as soon as he completes the job.

---

Hello,

We have a small question regarding the required scripts. In your previous message you wrote:

For the address you will want to select from the "OfficeLanguages table" by both "OfficeID" and "LanguageID" (to ensure you get 1 address and it’s the default)

However, the OfficeLanguages Table does not contain a field called LanguageID. Where do we get the LanguageID field and how is it related to the office address and the OfficeID field?

---

Sorry about that, I took extra columns out to make the list clearer and I removed the ones you needed.

The Table: OfficeLanguages does contain a column for "LanguageId" and the Table: Offices does have a column named "DefaultLanguageId"

So I should have posted this, sorry for the confusion:

Table Name: Offices                        
OfficeId   ADOfficeName   CountryId   PhoneNumber    FaxNumber    DefaultLanguageId       

Table Name: OfficeLanguages                        
OfficeId   LanguageId  DisplayName   StreetNumber   StreetName   Address2   City   StateProvince   PostalCode   DisplayAddress

Table Name: Countries                        
CountryId   Name                                       

This probably makes a lot more sense now.

---

Hello,

We still have a couple of questions to you.

1. The OfficeLanguages table contains a field called DisplayAddress. What is the meaning of that field and where should we put it?

2. As to the country, AD has three properties specifying a country:

   • Country Name (co) - the name of the country,
   • Country (c) - a two-letter Country Code (like US, for example),
   • Country Code (countryCode) - a numerical code for the country.

   When updating a country for a user, you need to update all the three properties. So, what we are interested in, which of the three properties do you use in the Countries table? Please keep in mind that we will need to list the country name mappings (countries with their numerical and two-letter country codes) directly in the script. So, it would be nice to know, how many countries you have and preferably have a list of those countries.

---

DisplayAddress is the full address concatenated and should go into the AD Attribute Address

I attached the Countries Table in a csv file.

Thanks again for doing this. I know its difficult not having access to the actual DB.

CountriesTable.zip

Answer 1

Hello,

Here you go:

STEP 1: Scheduled Task to update User Pattern

1. Launch Adaxes Administration Console.

2. Launch the Create Scheduled Task wizard.

3. On the 3rd step of the wizard, select the Show all object types checkbox and select the Domain-DNS object type.
   

4. On the 4th step, click Add Action, select Run a program of PowerShell script, and paste the following script:

    $databaseHost = "host.company.com" #TODO: modify me
    $databaseName = "MyDatabase" # TODO: modify me
    $isOfficePropertyRequired = $True
   
    # Use the credentials of the default Adaxes administrator
    # to connect to the database.
    $databaseUsername = $NULL
    $databasePassword = $NULL
   
    # Get the office name
    $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 ADOfficeName FROM Offices"
   
    $reader = $command.ExecuteReader()
   
    $officesName = @()
    while ($reader.Read())
    {
        $officesName += $reader["ADOfficeName"]
    }
   
    $reader.Close()
    $command.Dispose()
    $connection.Close()
   
    if($officesName.Length -eq 0)
    {
        return
    }
   
    # Modify 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)
   
    foreach ($item in $userPattern.Items)
    {
        if($item.PropertyName -eq "physicalDeliveryOfficeName")
        {
            $userPattern.Items.Remove($item)
            break
        }
    }
   
    $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)

5. Customize the script for your needs.

6. On the Activity Scope step of the wizard, add a domain to the activity scope of the task. It is important to add a single domain!

STEP 2: Script to update address
Add Run a program or PowerShell script action to a Business Rule executed after user creation. Use the following script:

$databaseHost = "host.company.com" #TODO: modify me
$databaseName = "MyDatabase" # TODO: modify me

# TODO: Specify country info @{"Country name" = @("two-letter country code", "Country code");
#                             "Country name" = @("two-letter country code", "Country code")}
$countryInfo = @{"United States" = @("US", "840");
                 "Albania" = @("AL", "8");
                 "Argentina" = @("AR", "32");
                 "Australia" = @("AU", "36");
                 "Austria" = @("AT", "40");
                 "Belarus" = @("BY", "112");
                 "Belgium" = @("BE", "56");
                 "Brazil" = @("BR", "76");
                 "Bulgaria" = @("BG", "100");
                 "Canada" = @("CA", "124");
                 "Chile" = @("CL", "152");
                 "China" = @("CN", "156");
                 "Colombia" = @("CO", "170");
                 "Costa Rica" = @("CR", "188");
                 "Croatia" = @("HR", "191");
                 "Czech Republic" = @("CZ", "203");
                 "Denmark" = @("DK", "208");
                 "Estonia" = @("EE", "233");
                 "Finland" = @("FI", "246");
                 "France" = @("FR", "250");
                 "Germany" = @("DE", "276");
                 "Greece" = @("GR", "300");
                 "Hong Kong" = @("HK", "344");
                 "Hungary" = @("HU", "348");
                 "India" = @("IN", "356");
                 "Indonesia" = @("ID", "360");
                 "Ireland" = @("IE", "372");
                 "Italy" = @("IT", "380");
                 "Japan" = @("JP", "392");
                 "Kazakhstan" = @("KZ", "398");
                 "Democratic People's Republic of Korea" = @("KP", "408");
                 "Republic of Korea" = @("KR", "410");
                 "Latvia" = @("LV", "428");
                 "Lithuania" = @("LT", "440");
                 "Malaysia" = @("MY", "458");
                 "Mexico" = @("MX", "484");
                 "Montenegro" = @("ME", "499");
                 "Netherlands" = @("NL", "528");
                 "New Zealand" = @("NZ", "554");
                 "Norway" = @("NO", "578");
                 "Pakistan" = @("PK", "586");
                 "Panama" = @("PA", "591");
                 "Peru" = @("PE", "604");
                 "Philippines" = @("PH", "608");
                 "Poland" = @("PL", "616");
                 "Portugal" = @("PT", "620");
                 "Romania" = @("RO", "642");
                 "Russian Federation" = @("RU", "643");
                 "Saudi Arabia" = @("SA", "682");
                 "Serbia" = @("RS", "688");
                 "Singapore" = @("SG", "702");
                 "Slovakia" = @("SK", "703");
                 "South Africa" = @("ZA", "710");
                 "Spain" = @("ES", "724");
                 "Sweden" = @("SE", "752");
                 "Switzerland" = @("CH", "756");
                 "Taiwan, Province of China" = @("TW", "158");
                 "Thailand" = @("TH", "764");
                 "Turkey" = @("TR", "792");
                 "Ukraine" = @("UA", "804");
                 "United Arab Emirates" = @("AE", "784");
                 "United Kingdom" = @("GB", "826");
                 "VietNam" = @("VN", "704");
                 "Israel" = @("IL", "376")} # TODO: modify me

# Use the credentials of the default Adaxes administrator to connect to the database.
$databaseUsername = $NULL
$databasePassword = $NULL

$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()

# Get the office address
$command = $connection.CreateCommand()
$command.CommandText = "SELECT OfficeID, DefaultLanguageId, CountryId FROM Offices WHERE ADOfficeName = @ADOfficeName"
$command.Parameters.Add("@ADOfficeName", "%physicalDeliveryOfficeName%") | Out-Null;

$reader = $command.ExecuteReader()

if ($reader.Read())
{
    $officeID = $reader["OfficeID"]
    $languageId = $reader["DefaultLanguageId"]
    $countryId = $reader["CountryId"]
}

$reader.Close()
$command.Dispose()

$command = $connection.CreateCommand()
$command.CommandText = "SELECT DisplayAddress, City, StateProvince, PostalCode FROM OfficeLanguages WHERE OfficeId = @OfficeId AND LanguageId = @LanguageId"
$command.Parameters.Add("@OfficeId", $officeID) | Out-Null;
$command.Parameters.Add("@LanguageId", $languageId) | Out-Null;

$reader = $command.ExecuteReader()

if ($reader.Read())
{
    $streetAddress = $reader["DisplayAddress"]
    $city = $reader["City"]
    $stateProvince = $reader["StateProvince"]
    $postalCode = $reader["PostalCode"]
}

$reader.Close()
$command.Dispose()

$command = $connection.CreateCommand()
$command.CommandText = "SELECT Name FROM Countries WHERE CountryId = @CountryId"
$command.Parameters.Add("@CountryId", $countryId) | Out-Null;

$reader = $command.ExecuteReader()

if ($reader.Read())
{
    $countryName = $reader["Name"]
}

$reader.Close()
$command.Dispose()
$connection.Close()

# Update user
$Context.TargetObject.Put("streetAddress", $streetAddress)
$Context.TargetObject.Put("l", $city)
$Context.TargetObject.Put("st", $stateProvince)
$Context.TargetObject.Put("postalCode", $postalCode)

if($countryInfo[$countryName] -ne $NULL)
{
    $country = ($countryInfo[$countryName])[0]
    $countryCode = ($countryInfo[$countryName])[1]
    $Context.TargetObject.Put("co", $countryName)
    $Context.TargetObject.Put("c", $country)
    $Context.TargetObject.Put("countryCode", $countryCode)
}

$Context.TargetObject.SetInfo()

Don't forget to modify the database host and name at the beginning of the script.

Comments

First off thank you, this is great, the scripts are working and it gives me a better understanding of how scripts work in Adaxes.

I do have 2 questions:

1. On the office name import the list is not sorted alphabetically. If I go into the constraint and select edit the software sorts the list when saved. But on the script import the list is not sorted. I tried to add "$officesName = $officesName | Sort-Object" but this is not working. I think it has to do with type casting.

2. When the address gets updated and if a field is blank the web interface says there are errors. Is there a way to display the error as to what attribute was not updated or if the value is NULL then not show an error?

Thanks again

---

Hello,

Here's an updated version of the script that sorts office names alphabetically before updating the Property Pattern:

$databaseHost = "host.company.com" #TODO: modify me
$databaseName = "MyDatabase" # TODO: modify me
$isOfficePropertyRequired = $True

# Use the credentials of the default Adaxes administrator
# to connect to the database.
$databaseUsername = $NULL
$databasePassword = $NULL

# Get the office name
$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 ADOfficeName FROM Offices"

$reader = $command.ExecuteReader()

$officesName = @()
while ($reader.Read())
{
    $officesName += $reader["ADOfficeName"]
}

$reader.Close()
$command.Dispose()
$connection.Close()

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

# Sort office names in the array
[System.Array]::Sort($officesName)

# Modify 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)

foreach ($item in $userPattern.Items)
{
    if($item.PropertyName -eq "physicalDeliveryOfficeName")
    {
        $userPattern.Items.Remove($item)
        break
    }
}

$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)

Here's also an updated version of the script that updates user's address after user creation. If a certain portion of the address is not specified in the database, the script just skips it, and no errors will be shown on the Web Interface:

$databaseHost = "host.company.com" #TODO: modify me
$databaseName = "MyDatabase" # TODO: modify me

# TODO: Specify country info @{"Country name" = @("two-letter country code", "Country code");
#                             "Country name" = @("two-letter country code", "Country code")}
$countryInfo = @{
"United States" = @("US", "840");
 "Albania" = @("AL", "8");
 "Argentina" = @("AR", "32");
 "Australia" = @("AU", "36");
 "Austria" = @("AT", "40");
 "Belarus" = @("BY", "112");
 "Belgium" = @("BE", "56");
 "Brazil" = @("BR", "76");
 "Bulgaria" = @("BG", "100");
 "Canada" = @("CA", "124");
 "Chile" = @("CL", "152");
 "China" = @("CN", "156");
 "Colombia" = @("CO", "170");
 "Costa Rica" = @("CR", "188");
 "Croatia" = @("HR", "191");
 "Czech Republic" = @("CZ", "203");
 "Denmark" = @("DK", "208");
 "Estonia" = @("EE", "233");
 "Finland" = @("FI", "246");
 "France" = @("FR", "250");
 "Germany" = @("DE", "276");
 "Greece" = @("GR", "300");
 "Hong Kong" = @("HK", "344");
 "Hungary" = @("HU", "348");
 "India" = @("IN", "356");
 "Indonesia" = @("ID", "360");
 "Ireland" = @("IE", "372");
 "Italy" = @("IT", "380");
 "Japan" = @("JP", "392");
 "Kazakhstan" = @("KZ", "398");
 "Democratic People's Republic of Korea" = @("KP", "408");
 "Republic of Korea" = @("KR", "410");
 "Latvia" = @("LV", "428");
 "Lithuania" = @("LT", "440");
 "Malaysia" = @("MY", "458");
 "Mexico" = @("MX", "484");
 "Montenegro" = @("ME", "499");
 "Netherlands" = @("NL", "528");
 "New Zealand" = @("NZ", "554");
 "Norway" = @("NO", "578");
 "Pakistan" = @("PK", "586");
 "Panama" = @("PA", "591");
 "Peru" = @("PE", "604");
 "Philippines" = @("PH", "608");
 "Poland" = @("PL", "616");
 "Portugal" = @("PT", "620");
 "Romania" = @("RO", "642");
 "Russian Federation" = @("RU", "643");
 "Saudi Arabia" = @("SA", "682");
 "Serbia" = @("RS", "688");
 "Singapore" = @("SG", "702");
 "Slovakia" = @("SK", "703");
 "South Africa" = @("ZA", "710");
 "Spain" = @("ES", "724");
 "Sweden" = @("SE", "752");
 "Switzerland" = @("CH", "756");
 "Taiwan, Province of China" = @("TW", "158");
 "Thailand" = @("TH", "764");
 "Turkey" = @("TR", "792");
 "Ukraine" = @("UA", "804");
 "United Arab Emirates" = @("AE", "784");
 "United Kingdom" = @("GB", "826");
 "VietNam" = @("VN", "704");
 "Israel" = @("IL", "376")} # TODO: modify me

# Use the credentials of the default Adaxes administrator to connect to the database.
$databaseUsername = $NULL
$databasePassword = $NULL

$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()

# Get the office address
$command = $connection.CreateCommand()
$command.CommandText = "SELECT OfficeID, DefaultLanguageId, CountryId FROM Offices WHERE ADOfficeName = @ADOfficeName"
$command.Parameters.Add("@ADOfficeName", "%physicalDeliveryOfficeName%") | Out-Null;

$reader = $command.ExecuteReader()

if ($reader.Read())
{
    $officeID = $reader["OfficeID"]
    $languageId = $reader["DefaultLanguageId"]
    $countryId = $reader["CountryId"]
}

$reader.Close()
$command.Dispose()

$command = $connection.CreateCommand()
$command.CommandText = "SELECT DisplayAddress, City, StateProvince, PostalCode FROM OfficeLanguages WHERE OfficeId = @OfficeId AND LanguageId = @LanguageId"
$command.Parameters.Add("@OfficeId", $officeID) | Out-Null;
$command.Parameters.Add("@LanguageId", $languageId) | Out-Null;

$reader = $command.ExecuteReader()

if ($reader.Read())
{
    $streetAddress = $reader["DisplayAddress"]
    $city = $reader["City"]
    $stateProvince = $reader["StateProvince"]
    $postalCode = $reader["PostalCode"]
}

$reader.Close()
$command.Dispose()

$command = $connection.CreateCommand()
$command.CommandText = "SELECT Name FROM Countries WHERE CountryId = @CountryId"
$command.Parameters.Add("@CountryId", $countryId) | Out-Null;

$reader = $command.ExecuteReader()

if ($reader.Read())
{
    $countryName = $reader["Name"]
}

$reader.Close()
$command.Dispose()
$connection.Close()

# Update user
if(-not([System.String]::IsNullOrEmpty($streetAddress)) -and -not(([System.Convert]::IsDBNull($streetAddress))))
{
    $Context.TargetObject.Put("streetAddress", $streetAddress)
}

if(-not([System.String]::IsNullOrEmpty($city)) -and -not(([System.Convert]::IsDBNull($city))))
{
    $Context.TargetObject.Put("l", $city)
}

if(-not([System.String]::IsNullOrEmpty($stateProvince)) -and -not(([System.Convert]::IsDBNull($stateProvince))))
{
    $Context.TargetObject.Put("st", $stateProvince)
}

if(-not([System.String]::IsNullOrEmpty($postalCode)) -and -not(([System.Convert]::IsDBNull($postalCode))))
{
    $Context.TargetObject.Put("postalCode", $postalCode)
}

if($countryInfo[$countryName] -ne $NULL)
{
    $country = ($countryInfo[$countryName])[0]
    $countryCode = ($countryInfo[$countryName])[1]
    $Context.TargetObject.Put("co", $countryName)
    $Context.TargetObject.Put("c", $country)
    $Context.TargetObject.Put("countryCode", $countryCode)
}

$Context.TargetObject.SetInfo()

---

Works great, thank you for all your help. This really jump starts my customization with your product.