Data Synchronization

UNDER CONSTRUCTION

Starting with version 1.1, iTop integrates a powerful data synchronization engine. This engines allows iTop consultants to federate several sources of information (like third party applications) into iTop.

This federation is defined in iTop using “Data Sources” objects. Each data source defines how iTop handles the synchronization of a given type of object from a given source.

For example it is possible to specify that iTop federates servers information coming from a network discovery tool and servers information coming from an asset management tool, with specific rules determining how the information is to be reconciled, which field is synchronized from which source, and with what effect for the end-users in iTop.

The picture below summarizes the information flow from an external data source into iTop.

The on-going process for synchronizing data with iTop is based on the following steps:

1. Extract data from the external source/application [not handled by iTop]
2. Transform the data to a content suitable for iTop [not handled by iTop]
3. Import the data into a temporary table in iTop [can be handled by iTop or an external application]
4. Search for matching objects in iTop [handled by iTop]
5. Create/Update or Delete the synchronized objects in iTop [handled by iTop]
6. Display and manage the synchronized object in iTop

To implement a Data Synchronization in iTop follow the steps below:

1. From the iTop admin menu, define a “Data Source” object, for the type of objects you want to import/synchronize. This creates a specific “replica” table in the iTop database, named “synchro_data_xxx”. (Once the data source is created, check the “Attributes” tab of the data source. The name of the temporary table is displayed at the top.)
2. Using either your favorite ETL or plain old scripts, populate the “synchro_data_xxxx” table corresponding to the “Data Source” with the data coming from the external source. You can use the special column “primary_key” for storing any identifier of the object in the external application. Each record in this table will correspond to one object in iTop. The column “id” is reserved for use by iTop and cannot be written. All other columns correspond to fields of the iTop object.
3. Trigger the execution of the data synchronization between the iTop objects and the “replicas” of the temporary table by executing the page “synchro/synchro_exec.php” either in command line mode, or by invocating the web page (using wget for example).

Of course you can schedule the steps 2 and 3 on a regular basis (hourly, daily or weekly) at will.

If you populate the table synchro_data_xxx using SQL commands, you must take care of duplicates. Remember that each record in the synchro_data_xxx table corresponds to one object in iTop.

You can either construct a value the uniquely identifies the “source” object and store this value in the “primary_key” field of the table, or use any combination of the record's field and MySQL's INDEX feature to ensure this uniqueness.

The “Data Sources” are defined using the menu “Admin Tools/Synchronization Data Sources”.

Each data source defines:

• The “target” class of the objects to be synchronized
• The list of attributes/fields of the objects to synchronize
• How to search/reconcile the objects with the objects already existing in iTop
• The rules of synchronizing/updating and possibly deleting objects in iTop
• How the synchronized objects behave for the iTop end-users (which fields are read-only, are users allowed to delete the objects…)
• An optional hyperlink and icon to refer iTop users to the corresponding object in the external application

When a configuration item is synchronized with a data source, its display is a bit different in iTop. The actions on this CI may also be limited depending, on the configuration of the data source. For example: the fields of the object that are synchronized may appear as read-only for the end users and iTop can prevent the users from deleting a synchronized object. The exact behaviour of the synchronized CI is determined by the properties of the data source.

The screenshot below depicts what a synchronized “Server” will look like in iTop. (Notice the “lock” icon in the object's title and the tooltip attached to it).

Some classes have attributes called Link Sets. Example: UserLocal.profile_list

You can synchronize those lists by writing all the information in the corresponding column:

Here is an example specifying that the login will have one profile: 'Portal User'

INSERT login, profile_list INTO data_synchro_userlocal_1 VALUES ('johndoe', 'profileid->name:Portal User;reason:Customer')...;

You can also specify several links (several profiles).

More information in Import a link set

As soon as a record has been left untouched for a period of time greater than the full load interval, then it will be considered as “disappeared” (see the status tab), and the object will be deleted along with the record in the sync data table.

By default, the deletion is disabled. To enable this feature, edit your data source:

• Full load interval: set the periodicity of your synchronization process. Leaving it to 0 disables the deletion feature.
• Delete policy: select “Delete”.

Note: the parameters Users allowed refers to the deletion of objects from within the GUI.

This mechanism has been designed having in mind the following constraints:

• be optimized for synchronizing huge volumes of data on a daily basis
• integrates easily with a third-party data management tools such as Talend.
• be secured and easy to operate/troubleshoot

As a consequence to these requirements, setting it up is not as intuitive as one could imagine.

Common pitfalls are:

• Delete records from the sync data table. This will not delete the objects. It will just “detach” the objects from the synchronization mechanism. Users will have the right to edit or delete the objects exactly as if they were created from within the UI. Reattaching the objects to the synchronization requires both to setup the data source so that the reconciliation can occur, then reintroduce the records into the sync data table. See how the deletion works hereabove. The synchronization will delete the records from the synch data table for you.
• Recreate the full sync data table periodically. With the relevant setup, this will work apparently, but…
  • When the table gets emptied, and until it gets populated again AND the synchro is run again, users will NOT see the corresponding objects as being synchronized
  • The synchronization will be long to execute and it will impact heavily the response time on the iTop web server. For every item synchronized, a reconciliation will have to occur, requiring the execution of queries to search for the best match.
• Use this mechanism for a realtime synchronization. It is far easier to implement that by the mean of web services like the REST/JSON services. The paradigm is then more intuitive: an object is being created (respectively updated/deleted) then you have to invoke the REST operation “object create” (respectively update/delete).

It is not possible to massively import documents in iTop using the CSV import feature, since obviously you cannot put a whole file into a CSV field. However it is possible to upload any kind of document using the data synchro feature, as long as you populate the document in the synchro_data_file_doc_xx table properly.

Note: in iTop the content of the FileDoc is stored in the database using 3 fields: content_data, content_filename and content_mimetype. All 3 fields MUST be populated for the data synchronization to create the document.

Assuming that you've created a Data Synchronization source as the following:

Then the following PHP example script 'do_one_upload.php' can be used to load the document /tmp/test.tgz into the table data_synchro_filedoc_1 in the database 'itop'.

do_one_upload.php

<?php
$hConn = mysql_connect('localhost', 'root', 'mypwd');
mysql_select_db('itop', $hConn); 
 
$sPath = "/tmp";
$sFile = "test.tgz";
 
$data = file_get_contents($sPath."/".$sFile);
$data = mysql_real_escape_string($data, $hConn);
$sFile = mysql_real_escape_string($sFile, $hConn);
$sOrgName = "Demo";
$sDocType = "contract";
$sPrimaryKey = $sFile;
 
$sQuery = "INSERT INTO synchro_data_filedoc_1 (primary_key,name,org_id,type,contents_data,contents_filename,contents_mimeType) ";
$sQuery .= "VALUES ('$sPrimaryKey','$sFile','$sOrgName','$sDocType','$data','$sFile', 'application/x-compress') ON DUPLICATE KEY UPDATE primary_key = '$sPrimaryKey'";
mysql_query($sQuery) OR die('Invalid query: ' . mysql_error());
?>

You can run the script from the command line by typing

php do_one_upload.php

Once this is done, just run the synchronization of the iTop Data Source 1, by calling synchro_exec.php from the command line:

php synchro_exec.php --auth_user=admin --auth_pwd=adminpwd --data_sources=1