Project: PhoneBook_ADsync_Main
The main project of this UMRA application collects the data from both the phonebook database and Active Directory. This results in two tables. Next, the join table action combines the two tables into a single new result table. This table holds in each row, all data to evaluate a single account. With a for-each loop, the project accesses the evaluation project for each individual account.
Figure 3: Overview of the script for the main UMRA application project.
Script action: Execute script – initialize variables
The project first executes script action Execute script to initialize the variables. The specification of this action is simple and straightforward: Only the name of the initialization project needs to be executed.
Figure 4: Script action Execute script executing script PhoneBook_ADsync_Init
Note that not all variables that are initialized in the initialization project are actually used in the main project. The main project initiates the execution of other projects that use these variables.
Script action: Generate generic table – phonebook
Next, the data of the phonebook application are collected. Script action Generate generic table generates a table with the phonebook data.
Figure 5: Script action Generate generic table to collect the phonebook data
The type of table is the result of a database query to an MS-Access (Jet) type database. For this type of database, only the name of the database file and the query must be specified.
The query returns the following 7 columns for each employee:
| Column
|
Description
|
ID
|
The unique identification of the employee in the database table. This column is the primary key of the table. The column is not used in the UMRA application.
|
FirstName
|
The first name of the employee. The FirstName column corresponds with the LDAP attribute givenName of the corresponding Active Directory user account. The column is used when the employee is found in the database table, but no user account for the employee already exists.
|
LastName
|
The last name of the employee. The LastName column corresponds with the LDAP attribute sn (surname) of the corresponding Active Directory user account. The column is used when the employee is found in the database table, but no user account for the employee already exists.
|
AccountID
|
The identification of the employee in the organization. This number uniquely identifies the employee and corresponds with the LDAP attribute employeeID of the corresponding user account in Active Directory. If no Active Directory user account with LDAP attribute employeeID equal to AccountID is found, no corresponding user account in Active Directory exists. In such a case, the user account must be created. If vice versa, a user account in Active Directory exists with an employeeID and no employee in the database table with the same AccountID is found, the employee is apparently no longer part of the organization and the user account must be removed.
|
Sex
|
Either M(ale) of F(emale). This column is not used in the UMRA application.
|
Department
|
The department of the employee. This column is not used in the UMRA application.
|
PhoneNumber
|
The telephone number of the employee. The column must correspond with the LDAP attribute telephoneNumber for each user account of Active Directory. If these values do not match, the attribute value of the Active Directory attribute must be updated.
|
Table 6: The columns returned for each employee
The name of the database file is set to variable %DatabaseFileName% as specified in the initialization project. The query simply reads the entire table from the database:
select * from Accounts
The result of the action is a table that is stored in variable %PhoneBookAccounts%. In a different environment, this action collects the data from an HR-system. The system can be any database. The result of the query must contain identification information for each user account and all relevant information to update. In most cases, the query is complex and includes data from multiple tables.
Script action: Generate generic table – Active Directory
The next script action collects the relevant data from Active Directory. This action performs an LDAP search and stores the result in a generic table.
Figure 6: Script action Generate generic table to collect Active Directory data
The LDAP search queries Active Directory using the binding string specified with variable %LdapBindingString%. The initialization project sets the value of this variable. In the example shown, the value is:
LDAP://ou=CompanyUsers,dc=tools4ever,dc=local2
The binding string must contain the word LDAP and the full path. Optionally, the name can contain binding information:
LDAP://name_of_dc/ ou=CompanyUsers,dc=tools4ever,dc=local2
The search returns the following 4 attributes for matching items:
| Attribute (LDAP name)
|
Description
|
employeeID
|
The unique identification of the user account in Active Directory. This value corresponds with a similar field of the phonebook database data (column AccountID). When these values correspond, the entries from both systems match.
In Active Directory, the employeeID attribute is often used for these purposes. The attribute SAM-Account-Name is also unique, but it might server different purposes.
|
cn
|
The common name attribute of the user account. This attribute is collected only for displaying purposes.
|
distinguishedName
|
The distinguished name of the user account in Active Directory. The distinguished name of a user account uniquely identifies the account in the directory service. UMRA uses this attribute to get access the user account, for instance when a user account attribute must be updated.
|
telephoneNumber
|
The telephone number of the user account as stored in Active Directory. If this number is not the same as the number stored in the phonebook database, it needs to be updated.
|
Table 7: LDAP attributes returned by Active Directory search
The UMRA project stores the results of the Active Directory search in variable %ActiveDirectoryAccounts%. Note that the variable holds the entire table.
Script action: Export variables - start
The UMRA application writes important progress and update information to a single file. The different projects execute the script action Export variables to write a single line to this file. Note that the different projects write to the same export file. The name of the file is set using variable %ExportFileName%.
The main project writes a single line to the export file to indicate the start of the synchronization process. After this line, the CREATE, UPDATE and DELETE synchronization action projects write additional lines to the export file. Finally, the main project marks the end of the synchronization process with another line.
Figure 8: Example of the export file produced by the UMRA application.
To write a line of text to the export file, the script action Export variables is used. The action contains multiple fields to compose the entire line of text. In this UMRA application, each Export variables action uses 4 fields.
Figure 9: Script action Export variables used to mark the start of the synchronization process
The first field contains the variables %NowHour%, %NowMinute%, etc. to compose a text field describing the current time and date. The %Now…% variables are automatically set by UMRA at runtime. The next field shows the text SYNC_START. The third field shows '000000' and the last fourth field shows the text Synchronization started.
Script action: Manage table data – log table data
For debugging purposes, the next two script actions log the phonebook table and the Active Directory user account table. In a real-life situation, these actions can be removed. When the UMRA application is designed and tested, the content of the tables is needed for table columns names and analyzing purposes.
Figure 10: Script action Manage table data … to log the table data contents
The script action specifies the type of operation, Log table data, and the name of the variable that contains the data: %PhoneBookAccounts% (and %ActiveDirectoryAccounts%).
Script action: Join tables
The join table action is the main action of the UMRA project. The action takes two input tables and copies the contents into a single output table. The output table is then used for further processing.
Figure 11: Script action Join tables
The properties of the Join table action specify how the rows from the two input tables must be joined. The two table variables %PhoneBookAccounts% and %ActiveDirectoryAccounts% identify the input tables. These variables contain the data from the phonebook database and the Active Directory user accounts respectively. The script action creates the ouput variable %JoinAccountsTable%.
Figure 12: Properties of script action Join Tables
The join condition specifies how to match the rows of the first and second input table. In this example scenario, the column AccountID contained in table %PhoneBookAccounts% must match the column employeeID of table %ActiveDirectoryAccounts%. If these values match, a new row is inserted in resulting table of the input table %JoinAccountsTable%. The new row contains all columns from both input tables and one extra column to describe the join operation: JoinResult.
For each row, the value of JoinResult can take one of three values:
0: a matching row was found for both input tables;
1: only the first input table contains the data of the row. No matching row was found for the second table. In this case, all values of the columns in the resulting output table that correspond with the second input table are all empty.
2: only the second input table contains the data of the row. No matching row was found for the first table. In this case, all values of the columns in the resulting output table that correspond with the first input table are empty.
If duplicate rows are found, e.g. rows with equal values for column AccountID of the first input table or equal values for employeeID of the second input table, then only the first row is used in the output table. All other rows with the same identification values are ignored.
The following table shows all of the columns and the origin of the column:
| Column
|
Source
|
Comment
|
ID
|
Phonebook database
|
|
FirstName
|
Phonebook database
|
|
LastName
|
Phonebook database
|
|
AccountID
|
Phonebook database
|
Equal to employeeID if match found
|
Sex
|
Phonebook database
|
|
Department
|
Phonebook database
|
|
PhoneNumber
|
Phonebook database
|
|
employeeID
|
Active Directory
|
Equal to AccountID if match found
|
cn
|
Active Directory
|
|
distinguishedName
|
Active Directory
|
|
telephoneNumber
|
Active Directory
|
|
JoinResult
|
Script action join table
|
Possible values:
0: Columns for phonebook data and Active Directory contain data, match found;
1: Columns for phonebook data contain data, Active Directory columns are empty, no match found;
2: Columns for phonebook data are empty, Active Directory columns contain data, no match found;
|
Table 7: All columns of the join table action result table %JoinAccountsTable%
The join table action roughly corresponds with a full outer join operation according to database terminology but has some differences:
- duplicate entries are removed
- only a single column of each table is used to join individual rows.
Script action: Manage table data – log table data
The next action logs the contents of the resulting table to the log file. This action is for debugging purposes only. The table contains all columns of both input tables and the extra JoinResult column.
Figure 13: Script action to log the contents of join result table %JoinAccountsTable%
Script action: For-Each loop
The result of the join table operation is a table that contains all data of both input tables. For matching rows, the result is a single row with the data of all columns of both input rows. For non-matching rows, the missing input table row contains empty column values. A For-Each construction now forms a loop to process all of the rows of the resulting table, one by one.
Figure 14: For-Each script action processing all data of the join result table
As can be seen in figure 14, all of the columns 0,…,12 of the input table %JoinAccountsTable% are passed to the call project PhoneBook_ADsyn_Evaluate. This evaluation project analyzes each row of the join result table and performs the appropriate action. The evaluation project is described in a separate section.
Note that not all variables are passed to the called project as shown in figure 15.
Figure 15: Input variables specification of For-Each script action
The input and output tables contained in variables %ActiveDirectoryAccount%, %PhoneBookAccounts% and %JoinAccountsTable% are not passed to the executed project. This is only for performance reasons: When UMRA passes a variable to the called project the variable is actually copied internally. For simple variables this is not very time and resource consuming. For large tables, the copy operation takes considerable time and memory. The copy operation is executed for every iteration cycle of the for-each loop. Therefore, the table data variables are not passed to the called project. Note that the individual values of the current row are passed to the calling project as separate variables.
When the for-each script action is completed, all rows are processed and the user accounts are updated in Active Directory. Finally, a simple script action writes an end-marker to the UMRA application export file with script action Export variables. For a description of this action, see section .
| 
