This tuto has been created to show how to import large amount of tickets from a legacy system into GLPI. We, in the past, had a tool managing tickets, this tool was tailored to manage applications bugs only, and not complete IT needs (tickets, CMDB,…). So we made the choice to migrate to GLPI, starting with what was missing: the CMDB. After this step, came the need to migrate the tickets from the legacy application into GLPI. This tuto will show you how to do that.

Important Note: This tuto is based on the 0.80.2 version of GLPI, but I believe it can be adapted to any recent versions.

When doing migrations there are always several steps:

1. extract data from legacy system
2. clean data (improve data quality)
3. prepare data for importation (based on so called “master data”)
4. import data

Here, we'll see only steps 3 and 4 (as 1 and 2 are application dependent).

The system that the script to upload documents (files attached to tickets) has been tested and is running on is a Windows 2003 server STD Edition SP2 with GLPI 0.80.2, and MySQL 5.1.50 community.

• For MySQL database accesses, we are using a free tool called HeidiSQL, but any tool that can import CSV files into MySQL would do the job.
• We use also another free tool called Notepad++ Unicode version.
• For managing data preparation, we are using MSExcel, but I'm sure that it could be done also with OpenOffice spreadsheets.

• entities (only if you want to use entities in GLPI, otherwise entity will be “root entity”)
• users
• groups

And more if you need more (like ticket categories) These Master Data must be imported into worksheets from GLPI database (see below). Table names are usually composed like glpi_NameOfObject (for entities: glpi_entities, for users: glpi_users, and so on).

Note: These mappings will be helpers for data transformation between legacy application and GLPI. You need to transform some values from your legacy application so that GLPI will understand them properly. for example: in our legacy system, priorities are not ordered the same way than in GLPI. Another example is the statuses: statuses were numbers in legacy application, and in GLPI they are strings.

• Ticket priority mapping
• Ticket status mapping
• Ticket category mapping (if used)
• Ticket type mapping
• Ticket satisfaction survey mapping

Example for priority mapping:

Example for status mapping

Note: I did that with MySQL ODBC driver installed on my PC, and creating a data source to MySQL server.

• entities from glpi_entities
• users from glpi_users
• groups from glpi_groups

you need to fill in :

Note: This table is the heart of ticketing in GLPI. It contains the ticket headers: the one you can find on any ticket.

Note: This table is used to store all the users who act on a ticket: requesters as well as watchers as well as resolvers. For groups, please see table 'glpi_groups'. Only used fields are described below: other fields are set to default values.

Note: This table is used to store followups of tickets. Only used fields are described below: other fields are set to default values.

Note: This table is used to store satisfaction survey for tickets. Normally used only when ticket is solved or closed.

Note: This table is used to associate a group to a ticket as requester, watcher or resolver. Only used fields are described below: other fields are set to default values.

tickets ticket_users followups and so on for each tables you need

Export data from you old system fill in the sheets in Excel with your exported data (and don't forget to use the mapping and the master data values then when Excel sheets seem OK, save them to Unicode text files use a tool to import the text files into MySQL (I used HeidiSQL: a free tool, very useful: HeidiSQL). At each import, check in GLPI that everything is OK

Note: I have developed some scripts in order to upload documents into tickets. This explains how to generate PHP lines in order to load attached documents into GLPI tickets.

Example:

$myDocs[]="entities_id=230&items_id=55562&prtrackerfile=attachments/55562/Quotation_2008_RC_55562_1007.pdf&upload_file=Quotation_2008_RC_55562_1007.pdf";
$myDocs[]="entities_id=220&items_id=55650&prtrackerfile=attachments/55650/PC 3D OS  XP.pdf&upload_file=PC 3D OS  XP.pdf";

These lines must be copied/pasted into the all_docs.php file. Like following:

<?php
$myDocs[]="entities_id=254&items_id=100001&prtrackerfile=attachments/1/HelpDesk.zip&upload_file=HelpDesk.zip";
$myDocs[]="entities_id=254&items_id=100002&prtrackerfile=attachments/2/notice.htm&upload_file=notice.htm";
...
$myDocs[]="entities_id=230&items_id=100549&prtrackerfile=attachments/549/Capture2.jpg&upload_file=Capture2.jpg";
?>

The AddDocuments.php script must be run in the glpiscripts folder directly with the php -f AddDocuments.php command line. Note: In the ZIP file below in download section, there are three files:

• all_docs.php: must contains the list of documents to attached to tickets see above for description of that file
• AddDocument.php: is used by next php script
• AddDocuments.php: starts the whole documents attach process

AddDocumentsScripts

— Olivier Moron 2012/03/06 16:58