iTop 1.x to 2.0 migration instructions

iTop 2.0 ships with two data-models:

• The 1.3 data model which is fully compatible with iTop 1.x. The only differences are a few bug fixes and the support of user-editable dashboards.
• The 2.0 data model which uses quite different concepts and was designed to be cleaner and more modular, but not necessarily aligned with the 1.x data model.

When upgrading the application using the setup process of iTop, the data-model of a previous (non-customized) 1.x instance is automatically migrated to the 1.3 data-model.

The “core” engine of iTop is upgraded to the 2.0 codebase and provides all the enhancements and bug fixes of the new version (user configurable lists and dashboards, shortcuts, etc.)

A “data migration”, on the other hand, consists in transforming the data stored in the database using the iTop 1.x data-model into the new 2.0 data model. Since the two models are significantly different, this process cannot be completely automated.

If you are happy with the data model of iTop 1.2 and don’t plan to document pieces of your IT infrastructure that are only covered by the 2.0 data-model, you don’t need to “migrate” your data to 2.0.

You need to migrate your data only if you want to use (some part of) the new data-model of iTop 2.0. In all other cases you can rely on the automatic upgrade.

When upgrading iTop, the setup compares the signature of all the data model files of the previous instance (located in the “modules” directory) with the signature of a corresponding 1.x genuine version. If any modification is detected in one of these files, the end-user is prompted whether or not to preserve the modifications. If the user answers to keep the modifications, then the whole “modules” folder will be used as the data-model definition. Otherwise a standard 1.3 model is used.

Since the User Editable dashboards are a special kind of Menu Node they are part of the data-model definition. You can benefit from the new feature by replacing the TemplateMenuNode instances by DashboardMenuNodes in your customized 1.x data-model.

• On a development machine, install your customized 1.x iTop instance
• Use the standard upgrade procedure to upgrade it with the 2.0 iTop package
• When prompted to keep or discard your modifications, tell the setup to keep them
• Install the 2.0 toolkit on this new instance of iTop
• Edit the data model to add a new “empty” DasbhoardMenuNode menu item. Run the toolkit to apply your modifications
• Use the interactive editor dashboard editor to design your dashboard, as any user would be when customizing her/his own dashboard
• Save the customized dashboard using the menu “Export to file…”
• Copy/paste the resulting XML in the definition of the DashboardMenuNode in your new data model.
• Use the toolkit to apply the modifications.
• Connect to iTop and select “Revert to Original Version…” to check that the customization is now part of your data model.
• Remove the un-needed TemplateMenuNode from your data model
• Upgrade your production instance with newly customized data model

If you want to migrate your 1.x iTop instance to the new 2.0 data model, here is an overview of the process:

• Upgrade your “old” iTop instance to 2.0. This will upgrade the data-model schema to 1.3 and bring to your iTop instance all the benefits of the code enhancements and bug fixes (especially in terms of data export capabilities).
• On the same database server, but in a different database, install a brand new instance of iTop that will receive your migrated data with the desired combination of modules from the 2.0 data-model. Don’t install the “Demo” data on this instance.
• Use the SQL script (see below) to transfer the Organizations, Documents, Contacts and User records from the old instance to the new instance. The SQL script will also create all the new Typology for you.
• For the remaining classes: Servers, PCs, Network Devices, Services, Contracts, etc… use the CSV export and CSV import features to transfer the data in the order explained below.

Why is there no simple mapping between iTop 1.2.1 and iTop 2.0?

There are several cases where the two data models (1.x and 2.0) are significantly different and where a simple automated mapping is not possible. The major cases where such a manual intervention is required are listed below:

Some concepts were absent from the 1.x data model. For example in iTop 1.2.1 there was no such thing as a “Virtual Machine”. In many cases the Virtual Machines were just documented using the “Server” object. Now that the “Virtual Machine” object exists in iTop 2.0, migrating from the old data-model to the new one requires distinguishing between servers and virtual machines. The way to perform this “split” depends on the data you entered in iTop. You may be fortunate enough to have used a strict naming convention that distinguishes servers from VMs, in which case a simple sort/filter in Excel can do the trick. In other cases you may need to review the data one by one (either in Excel or by creating two groups in iTop for instance).

Some of the fields that were either closed enumerated lists or plain-text fields have been turned into configurable enumeration, grouped together under the “Typology” umbrella.

Example: Brand and Model.

The transformation of such fields is handled by the SQL script provided below. You may still need to review and cleanup the data for near duplicates like “Hewlett Packard”, “Hewlett-Packard” or, even worse, “HP”.

A number of features were removed from the 2.0 data-model: either because there are other (better?) ways of documenting the same kind of information or because they were useless and too confusing in most cases. In such case, migrating to the standard 2.0 data-model will result in a loss of data.

Examples:

• A Team can no longer be a member of another Team.
• Locations are no longer in a hierarchy (there is no parent location anymore)

If you have several objects of the same class that have the same name, be aware that the re-import of other objects that point to such “duplicates” can be challenging.

The structure for documenting some information changed with the 2.0 data-model. In some cases there is no simple one to one mapping between the elements in 1.x and the elements in 2.x. for example, in 1.x the network connections were documented by connecting interfaces together. Since this was confusing (and hard to document) for most of the users, a simpler, direct relation between the devices was introduced in the 2.0 data-model. It is however possible to transfer the network topology between both models, but this requires some non-trivial processing of the data.

Once your two instances are ready for the migration, proceed as follows:

• Download the SQL script below and use find/replace in your favorite text editor to ajust the names of the source and destination databases. The source database is named itop1x in the SQL below and the destination database is named itop20.

1x_to-20_migration.sql

/**
 * Organization
*/
TRUNCATE TABLE itop20.organization;
INSERT INTO itop20.organization (id, name, code, STATUS, parent_id, parent_id_left, parent_id_right)
  SELECT id, name, code, STATUS, parent_id, parent_id_left, parent_id_right FROM itop1x.organization;
 
 
/**
 * Typology
*/
TRUNCATE TABLE itop20.typology;
 
/**
 * Typology - DocumentType
*/
INSERT INTO itop20.typology (name, finalclass)
 SELECT DISTINCT TYPE, 'DocumentType' FROM itop1x.document WHERE TYPE IS NOT NULL;
 
TRUNCATE TABLE itop20.documenttype;
INSERT INTO itop20.documenttype (id)
 SELECT id FROM itop20.typology WHERE finalclass = 'DocumentType';
 
/**
 * Document
*/
TRUNCATE TABLE itop20.document;
/**
 * Document - DocumentFile
*/
INSERT INTO itop20.document (id, name, org_id, documenttype_id, description, STATUS, finalclass)
SELECT 
    SOURCE.id,
    SOURCE.name,
    SOURCE.org_id,
    typo.id,
    SOURCE.description,
    SOURCE.status,
    'DocumentFile'
FROM
    itop1x.document AS SOURCE
        LEFT JOIN
    itop20.typology AS typo ON SOURCE.type = typo.name
WHERE SOURCE.finalclass = 'FileDoc';
 
TRUNCATE TABLE itop20.documentfile;
INSERT INTO itop20.documentfile (id, file_data, file_mimetype, file_filename)
SELECT id, contents_data, contents_mimetype, contents_filename FROM itop1x.filedoc;
 
/**
 * Document - DocumentNote
*/
INSERT INTO itop20.document (id, name, org_id, documenttype_id, description, STATUS, finalclass)
SELECT 
    SOURCE.id,
    SOURCE.name,
    SOURCE.org_id,
    typo.id,
    SOURCE.description,
    SOURCE.status,
    'DocumentNote'
FROM
    itop1x.document AS SOURCE
        LEFT JOIN
    itop20.typology AS typo ON SOURCE.type = typo.name
WHERE SOURCE.finalclass = 'Note';
 
TRUNCATE TABLE itop20.documentnote;
INSERT INTO itop20.documentnote (id, text)
SELECT id, note FROM itop1x.note;
 
/**
 * Document - DocumentWeb
*/
INSERT INTO itop20.document (id, name, org_id, documenttype_id, description, STATUS, finalclass)
SELECT 
    SOURCE.id,
    SOURCE.name,
    SOURCE.org_id,
    typo.id,
    SOURCE.description,
    SOURCE.status,
    'DocumentWeb'
FROM
    itop1x.document AS SOURCE
        LEFT JOIN
    itop20.typology AS typo ON SOURCE.type = typo.name
WHERE SOURCE.finalclass = 'WebDoc';
 
TRUNCATE TABLE itop20.documentweb;
INSERT INTO itop20.documentweb (id, url)
SELECT id, url FROM itop1x.externaldoc;
 
/**
 * Location
*/
TRUNCATE TABLE itop20.location;
INSERT INTO itop20.location (name, STATUS, org_id, address, postal_code, city, country)
SELECT name, STATUS, org_id, address, postal_code, city, country FROM itop1x.location;
 
/**
 * Contact
*/
TRUNCATE TABLE itop20.contact;
 
/**
 * Contact - Person
*/
INSERT INTO itop20.contact (id, name, STATUS, org_id, email, phone, finalclass)
SELECT
    id, name, STATUS, org_id, email, phone, 'Person'
FROM
    itop1x.contact
WHERE finalclass = 'Person';
 
TRUNCATE TABLE itop20.person;
INSERT INTO itop20.person (id, first_name, employee_number, location_id)
SELECT p.id, p.first_name, p.employee_id, c.location_id FROM itop1x.person AS p JOIN itop1x.contact AS c ON c.id = p.id;
 
/**
 * Contact - Team
*/
INSERT INTO itop20.contact (id, name, STATUS, org_id, email, phone, finalclass)
SELECT
    id, name, STATUS, org_id, email, phone, 'Team'
FROM
    itop1x.contact
WHERE finalclass = 'Team';
 
TRUNCATE TABLE itop20.team;
INSERT INTO itop20.team (id)
SELECT id FROM itop1x.team;
 
/**
 * Person to Team
*/
INSERT INTO itop20.typology (name, finalclass)
 SELECT DISTINCT ROLE, 'ContactType' FROM itop1x.lnkteamtocontact WHERE ROLE !='';
 
TRUNCATE TABLE itop20.contacttype;
INSERT INTO itop20.contacttype (id)
 SELECT id FROM itop20.typology WHERE finalclass = 'ContactType';
 
TRUNCATE TABLE itop20.lnkpersontoteam;
INSERT INTO itop20.lnkpersontoteam (team_id, person_id, role_id)
 SELECT l.team_id, l.contact_id, t.id
 FROM itop1x.lnkteamtocontact AS l LEFT JOIN itop20.typology AS t ON l.ROLE = t.name;
 
/**
 * User management
*/
TRUNCATE TABLE itop20.priv_user;
INSERT INTO itop20.priv_user SELECT * FROM itop1x.priv_user;
TRUNCATE TABLE itop20.priv_internaluser;
INSERT INTO itop20.priv_internaluser SELECT * FROM itop1x.priv_internaluser;
TRUNCATE TABLE itop20.priv_user_local;
INSERT INTO itop20.priv_user_local SELECT * FROM itop1x.priv_user_local;
TRUNCATE TABLE itop20.priv_urp_userorg;
INSERT INTO itop20.priv_urp_userorg SELECT * FROM itop1x.priv_urp_userorg;
TRUNCATE TABLE itop20.priv_urp_profiles;
INSERT INTO itop20.priv_urp_profiles SELECT * FROM itop1x.priv_urp_profiles;
TRUNCATE TABLE itop20.priv_urp_userprofile;
INSERT INTO itop20.priv_urp_userprofile SELECT * FROM itop1x.priv_urp_userprofile;

• Connect to the MySQL server and run the SQL script. This will import the Organizations, Documents, Contacts, Locations, Users and will create all the Typology (not yet all 🚧) objects in the new database.
• Use the CSV export (either interactive or scripted) to export the following data from the “old” instance.
• Use the CSV import (preferably scripted to avoid timeouts) on the new instance to import the data in the following order, and with the following field mapping: