How to import bulk of tickets into GLPI?
Introduction
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.
General Migration steps
When doing migrations there are always several steps:
- extract data from legacy system
- clean data (improve data quality)
- prepare data for importation (based on so called “master data”)
- import data
Here, we'll see only steps 3 and 4 (as 1 and 2 are application dependent).
Environment
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.
Requirements
- 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.
Detailed steps
1) Prepare in GLPI the needed Master Data:
- 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).
2) Prepare in Excel the Master Data mapping:
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:
| Legacy ID | GLPI ID | Meaning |
|---|---|---|
| 1 | 5 | Very High |
| 2 | 4 | High |
| 3 | 3 | Normal |
| 4 | 2 | Low |
| 5 | 1 | Very low ← not in used in GLPI |
Example for status mapping
| Legacy ID | Legacy Meaning | GLPI Status |
|---|---|---|
| 1 | New | new |
| 2 | Assigned | assign |
| 3 | Investigated | assign |
| 4 | Resolved | solved |
| 5 | Failed | ← not used any more, so we are not going to migrate this status |
| 6 | Verified | solved |
| 7 | Postponed | closed |
| 8 | Closed | closed |
3) Extract master data from glpi tables to Excel
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
4) Detailed description of glpi tables
you need to fill in :
glpi_tickets
Note: This table is the heart of ticketing in GLPI. It contains the ticket headers: the one you can find on any ticket.
| GLPI field | example: | to be set | description | Comments | see table | Comments | |
|---|---|---|---|---|---|---|---|
| id | 1000 | x | is the ticket NUMBER! | It is an automatic ticket number (ticket number from former applications will be input in a custom field called formerticketnumber) | glpi_plugin_customfields_tickets | ||
| entities_id | 187 | x | is the id of the cost center | must match the ID from entity list | Entities | if cost center is not found in Entities, then use the company code to find an ID (4 or 6 chars), if not found, then use 0 (=entity root ID) | |
| name | Need to rent a PC for a trainee | x | is the ticket title | ||||
| date | 2009-04-09 14:09:00 | x | is the creation date | includes date and time | |||
| closedate | NULL | x | is the date for 'closed' tickets, otherwise NULL | includes date and time | |||
| solvedate | NULL | x | is the resolution date for resolved ticket, otherwise NULL | includes date and time | |||
| date_mod | 2009-04-20 11:00:59 | x | is date of last modification | includes date and time | |||
| users_id_lastupdater | 937 | x | is the id of the last updater (otherwise 0) | is the ID (with vlookup from glpi_users sheet) of the last modifier | glpi_users | ||
| status | new | x | must be 'new', 'assign', 'plan', 'waiting', 'solved', or 'closed' | is the GLPI status from sheet 'ticket status mapping' | Ticket status mapping | ||
| users_id_recipient | 937 | x | is the id of the originator | is the ID (with vlookup from glpi_users sheet) of ticket creator | glpi_users | ||
| requesttypes_id | 1 | x | is the 'request source' in GLPI ticket (SLA, TBI, …) | is the ID, result of vlookup from glpi_requesttypes | glpi_requesttypes | ||
| suppliers_id_assign | 0 | x | 0 means no supplier (was not managed in legacy application) | must be zero (0) | |||
| itemtype | x | is an empty string | |||||
| items_id | 0 | x | linked to itemtype field (must be 0) | Not used at the moment | |||
| content | Need to rent a PC (Std one) | x | is the ticket description | ||||
| urgency | 3 | x | default value | ||||
| impact | 3 | x | default value | ||||
| priority | 3 | x | is the priority | is the GLPI result of the vlookup from 'Ticket priority mapping' sheet | Ticket Priority mapping | ||
| ticketcategories_id | 154 | x | is the id of the single service | is category ID from vlookup from glpi_ticketcategories sheet (see portfolio of financial dashboard) | glpi_ticketcategories | ||
| type | 1 | x | 1 = incident, 2 = request | ||||
| cost_time | 0.0000 | x | |||||
| cost_fixed | 0.0000 | x | |||||
| cost_material | 0.0000 | x | |||||
| ticketsolutiontypes_id | 2 | x | 1 = Closed (not solved), 2 = Closed (solved) | Must be 2 most of the time, but postponed ticket will have 'not solved' (=1) | |||
| solution | <p>Win XP SP3 installation</p> | x | HTML transformed to text | is the text input when changing status to 'resolved' transformed to HTML | |||
| global_validation | none | x | is the approval for the quotation (used only for RFC!) | possible values are: none (for not subject to approval), waiting (approval sent), accepted (for granted), rejected (for refused) | |||
| slas_id | 0 | x | |||||
| slalevels_id | 0 | x | |||||
| due_date | NULL | x | is the due date (currently not used) | ||||
| begin_waiting_date | NULL | x | |||||
| sla_waiting_duration | 0 | x | |||||
| ticket_waiting_duration | 0 | x | |||||
| close_delay_stat | 0 | x | |||||
| solve_delay_stat | 0 | x | |||||
| takeintoaccount_delay_stat | 0 | x | |||||
| actiontime | 0 | x | |||||
glpi_tickets_users
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.
| Fields | example: | to be set | description | see table | |
|---|---|---|---|---|---|
| id | (automatic id) | ||||
| tickets_id | 1000 | x | is the ticket number | glpi_tickets | |
| users_id | 937 | x | is the user id | glpi_users | |
| type | 1 | x | 1 for 'requester', 2 for 'resolver', 3 for 'watcher' | ||
| use_notification | 0 | x | 0 for no email notification, 1 for email notifications | ||
| alternative_email | NULL | x | to be set only when user has no email in his GLPI account AND use_notification is 1 | ||
glpi_ticketfollowups
Note: This table is used to store followups of tickets. Only used fields are described below: other fields are set to default values.
| tickets_id | is the GLPI ticket id | see table |
|---|---|---|
| date | is the date of the followup | |
| users_id | is the user id of the writer of the followup | glpi_users |
| content | is the content (text only) of the followup |
glpi_ticketsatisfactions
Note: This table is used to store satisfaction survey for tickets. Normally used only when ticket is solved or closed.
| field | meaning | comment |
|---|---|---|
| ticket_id | is the GLPI ticket id | |
| type | must be 1 | |
| date_begin | is the date when the request for the satisfaction survey has been sent to requester | Format must be “YYYY-MM-DD HH:MM:SS” |
| date_answered | is the date when the satisfaction survey has been filled out by requester | Format must be “YYYY-MM-DD HH:MM:SS” |
| satisfaction | Must be 1, 2, or 3 | see 'Satisfaction Mapping' sheet |
| comment | is the comment for the survey |
glpi_groups_tickets
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.
| field | meaning | comment |
|---|---|---|
| tickets_id | is the GLPI ticket id | |
| groups_id | is the GLPI group id | see Group mapping |
| type | is the type | 1 for 'requester', 2 for 'resolver', 3 for 'watcher' |
and more if needed (like custom fields)
5) Write mapping formulas or macros in Excel in order to map the old values to the one used in GLPI:
tickets ticket_users followups and so on for each tables you need
6) Migration steps:
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
7) Special case: attached documents (if you have some)
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.
| Syntax of each line | meaning |
|---|---|
| entities_id | is the entity of the ticket into which the document has to be loaded |
| items_id | is the GLPI ticket number |
| prtrackerfile | is the relative path to the source document from the _uploads folder in GLPIfiles |
| upload_file | is the name which will be used to load the document into GLPI |
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 fileAddDocument.php: is used by next php scriptAddDocuments.php: starts the whole documents attach process
Download
— Olivier Moron 2012/03/06 16:58