Trace: » How to import bulk of tickets into GLPI?

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:

  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).

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 1Very 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
id1000xis 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_id187xis the id of the cost centermust match the ID from entity listEntitiesif 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 traineexis the ticket title
date 2009-04-09 14:09:00xis the creation dateincludes date and time
closedateNULLxis the date for 'closed' tickets, otherwise NULLincludes date and time
solvedateNULLxis the resolution date for resolved ticket, otherwise NULLincludes date and time
date_mod 2009-04-20 11:00:59xis date of last modificationincludes date and time
users_id_lastupdater937xis the id of the last updater (otherwise 0)is the ID (with vlookup from glpi_users sheet) of the last modifierglpi_users
statusnewxmust be 'new', 'assign', 'plan', 'waiting', 'solved', or 'closed'is the GLPI status from sheet 'ticket status mapping'Ticket status mapping
users_id_recipient937xis the id of the originatoris the ID (with vlookup from glpi_users sheet) of ticket creatorglpi_users
requesttypes_id1xis the 'request source' in GLPI ticket (SLA, TBI, …)is the ID, result of vlookup from glpi_requesttypesglpi_requesttypes
suppliers_id_assign0x0 means no supplier (was not managed in legacy application)must be zero (0)
itemtypexis an empty string
items_id0xlinked to itemtype field (must be 0)Not used at the moment
content Need to rent a PC (Std one)xis the ticket description
urgency3xdefault value
impact3xdefault value
priority3xis the priorityis the GLPI result of the vlookup from 'Ticket priority mapping' sheetTicket Priority mapping
ticketcategories_id154xis the id of the single serviceis category ID from vlookup from glpi_ticketcategories sheet (see portfolio of financial dashboard)glpi_ticketcategories
type1x1 = incident, 2 = request
cost_time 0.0000x
cost_fixed 0.0000x
cost_material 0.0000x
ticketsolutiontypes_id2x1 = 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>xHTML transformed to textis the text input when changing status to 'resolved' transformed to HTML
global_validationnonexis 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_id0x
slalevels_id0x
due_dateNULLxis the due date (currently not used)
begin_waiting_dateNULLx
sla_waiting_duration0x
ticket_waiting_duration0x
close_delay_stat0x
solve_delay_stat0x
takeintoaccount_delay_stat0x
actiontime0x

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_id1000xis the ticket number glpi_tickets
users_id937xis the user idglpi_users
type1x1 for 'requester', 2 for 'resolver', 3 for 'watcher'
use_notification0x0 for no email notification, 1 for email notifications
alternative_emailNULLxto 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 followupglpi_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.

fieldmeaningcomment
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 linemeaning
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 file
  • AddDocument.php: is used by next php script
  • AddDocuments.php: starts the whole documents attach process

Download

AddDocumentsScripts

Olivier Moron 2012/03/06 16:58