1

Topic: Ticket bulk upload into GLPI

Hello Forum!
In our company we want to get rid of our old ticketing system called "PR Tracker" and want to use GLPI in the future (Yeah!).
The management wants that when we start with GLPI all old tickets from PR Tracker should be available in the GLPI ticketing system. Does anybody know of an efficient way how to bulk upload those tickets into GLPI?

Best regards,
Reini

2

Re: Ticket bulk upload into GLPI

Dear Reini,
I made a test, it is possible to write directly to the database...and the ticket number is kept.
I'm preparing a complete HowTo document to bulk import tickets.

Prod: GLPI 0.83.8 / Windows 2008 x64 / PHP 5.4.14 x64 / IIS 7.5 / MySQL 5.6.10-community x64
Dev: GLPI 0.83.8 / Windows 2008 x64 / PHP 5.4.14 x64 / IIS 7.5 / MySQL 5.6.10-community x64
Worldwide: ~5000 PC, ~700 servers, ~5700 users (15 languages), >160000 tickets, ~750 entities
Raynet is ARaymond (http://www.araymond.com) IT service management

3

Re: Ticket bulk upload into GLPI

hello,
which version of glpi are you using ?

4

Re: Ticket bulk upload into GLPI

sorry, I forgot that!
I've done the test with the 0.80.1 which is currently installed on my development server.

Prod: GLPI 0.83.8 / Windows 2008 x64 / PHP 5.4.14 x64 / IIS 7.5 / MySQL 5.6.10-community x64
Dev: GLPI 0.83.8 / Windows 2008 x64 / PHP 5.4.14 x64 / IIS 7.5 / MySQL 5.6.10-community x64
Worldwide: ~5000 PC, ~700 servers, ~5700 users (15 languages), >160000 tickets, ~750 entities
Raynet is ARaymond (http://www.araymond.com) IT service management

5

Re: Ticket bulk upload into GLPI

Oh, I can't wait to see the solution! :-)

6

Re: Ticket bulk upload into GLPI

Any update on your project, tomolimo?

7

Re: Ticket bulk upload into GLPI

dear all,
I have written a document in Excel which contains mainly a modelization of the ticket migration I need.
I'll provide you something as soon as I'll have done the migration.
Regards,
Tomolimo

Prod: GLPI 0.83.8 / Windows 2008 x64 / PHP 5.4.14 x64 / IIS 7.5 / MySQL 5.6.10-community x64
Dev: GLPI 0.83.8 / Windows 2008 x64 / PHP 5.4.14 x64 / IIS 7.5 / MySQL 5.6.10-community x64
Worldwide: ~5000 PC, ~700 servers, ~5700 users (15 languages), >160000 tickets, ~750 entities
Raynet is ARaymond (http://www.araymond.com) IT service management

8

Re: Ticket bulk upload into GLPI

slinx, are you still interested by this topic?
I have done a complete migration from our old prtracker ticketing system to GLPI, using the same IDs for the tickets (in order to keep the link with the running tickets at migration time).
and I have generalized this procedure in order to migrate tickets from another tool used in another subsidiary of our company.
regards,
Tomolimo

Prod: GLPI 0.83.8 / Windows 2008 x64 / PHP 5.4.14 x64 / IIS 7.5 / MySQL 5.6.10-community x64
Dev: GLPI 0.83.8 / Windows 2008 x64 / PHP 5.4.14 x64 / IIS 7.5 / MySQL 5.6.10-community x64
Worldwide: ~5000 PC, ~700 servers, ~5700 users (15 languages), >160000 tickets, ~750 entities
Raynet is ARaymond (http://www.araymond.com) IT service management

9

Re: Ticket bulk upload into GLPI

tomolimo wrote:

slinx, are you still interested by this topic?
I have done a complete migration from our old prtracker ticketing system to GLPI, using the same IDs for the tickets (in order to keep the link with the running tickets at migration time).
and I have generalized this procedure in order to migrate tickets from another tool used in another subsidiary of our company.
regards,
Tomolimo

Yes, I would like to know how you did it.

10 (edited by tomolimo 2012-01-10 11:53:46)

Re: Ticket bulk upload into GLPI

Ok, then follow this:

1) Prepare in GLPI the needed master data:

  • entities

  • users

  • groups

  • and more if you need more (like categories)

2) Prepare in Excel the master data mapping you need:

  • Ticket priority mapping

  • Ticket status mapping

  • Ticket category mapping (if used)

  • Ticket type mapping

  • Ticket satisfaction survey mapping

3) Extract master data from glpi tables to Excel (I did that with mySQL ODBC driver, creating a data source to mySQL server)

  • entities from glpi_entities

  • users from glpi_users

  • groups from glpi_groups

4) You need the detailed description of the glpi tables you need to fill in (for this I have an excel file, but I don't know how to send it to the forum):

  • glpi_tickets

  • glpi_tickets_users

  • glpi_ticketfollowups

  • glpi_ticketsatisfactions

  • glpi_groups_tickets

  • 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: http://www.heidisql.com/)

  • At each import, check in GLPI that everything is OK

7) Special case: attached documents (if you have some)
I have developed a small script in order to upload documents into tickets.

at your disposal for more info,

regards,
Tomolimo

Prod: GLPI 0.83.8 / Windows 2008 x64 / PHP 5.4.14 x64 / IIS 7.5 / MySQL 5.6.10-community x64
Dev: GLPI 0.83.8 / Windows 2008 x64 / PHP 5.4.14 x64 / IIS 7.5 / MySQL 5.6.10-community x64
Worldwide: ~5000 PC, ~700 servers, ~5700 users (15 languages), >160000 tickets, ~750 entities
Raynet is ARaymond (http://www.araymond.com) IT service management

11

Re: Ticket bulk upload into GLPI

slinx: is it ok for you?
Or do you need something else?
regards,
Tomolimo

Prod: GLPI 0.83.8 / Windows 2008 x64 / PHP 5.4.14 x64 / IIS 7.5 / MySQL 5.6.10-community x64
Dev: GLPI 0.83.8 / Windows 2008 x64 / PHP 5.4.14 x64 / IIS 7.5 / MySQL 5.6.10-community x64
Worldwide: ~5000 PC, ~700 servers, ~5700 users (15 languages), >160000 tickets, ~750 entities
Raynet is ARaymond (http://www.araymond.com) IT service management

12

Re: Ticket bulk upload into GLPI

Hi tomolimo.
I'm interesting about bulk upload into GLPI, ist posible to import from remedy to GLPI?
Remedy that we use is own by outsource company, and my Manager want to have our own record in own server.
please advise.

GLPI  0.80.5 / windows 2003

Fresh installed GLPI 0.83 upgraded to GLPI  0.83.1
Operating Systems : Windows Server 2003 R2 Enterprise Edition moved to Linux Centos 6.2
PHP 5.3.3, Apache/2.2.15 (CentOS), MySQL: 5.1.61

13

Re: Ticket bulk upload into GLPI

hi teguh.umar,
I think it is possible to upload from any tool to GLPI, the only problem is to export the data from the tools.
As far as I know Remedy, it's difficult to export the data from it. We had in the past Remedy in our company, it was used for the CMDB only, not the ticketing.
My purpose here in this post is to upload the data for the ticketing, not for the CMDB. For uploading data in the GLPI CMDB, there are functions in GLPI itself to do that.
What is your purpose?
  to upload CMDB into GLPI? the answer is: https://forge.indepnet.net/projects/datainjection
  or to upload tickets into GLPI? the answer is use my procedure described above.

regards,
Tomolimo

Prod: GLPI 0.83.8 / Windows 2008 x64 / PHP 5.4.14 x64 / IIS 7.5 / MySQL 5.6.10-community x64
Dev: GLPI 0.83.8 / Windows 2008 x64 / PHP 5.4.14 x64 / IIS 7.5 / MySQL 5.6.10-community x64
Worldwide: ~5000 PC, ~700 servers, ~5700 users (15 languages), >160000 tickets, ~750 entities
Raynet is ARaymond (http://www.araymond.com) IT service management

14

Re: Ticket bulk upload into GLPI

Thanks Tomolino, that looks very complicated! I will try it out. I hope I don't break anything.

If you could send me that excel sheet, I'd appreciate it. Do you have a Dropbox, or other public file sharing site?

15 (edited by tomolimo 2012-01-27 10:08:23)

Re: Ticket bulk upload into GLPI

yes it is a little bit complex, but at the end you've got the former tickets up and ready in GLPI...
I've currently upload like this about 2900 tickets (1200 were live, and 1700 were already closed tickets), and I'm going to upload the rest (I mean the very old tickets) and the qt is about 99500! I'll do it 20000 per 20000!
for the excel file I don't have shared folder!
Could it be possible to upload this somewhere in the GLPI project docs?

Note: don't forget to work on a test system and to backup the database before any uploads!

regards,
Tomolimo

Prod: GLPI 0.83.8 / Windows 2008 x64 / PHP 5.4.14 x64 / IIS 7.5 / MySQL 5.6.10-community x64
Dev: GLPI 0.83.8 / Windows 2008 x64 / PHP 5.4.14 x64 / IIS 7.5 / MySQL 5.6.10-community x64
Worldwide: ~5000 PC, ~700 servers, ~5700 users (15 languages), >160000 tickets, ~750 entities
Raynet is ARaymond (http://www.araymond.com) IT service management

16 (edited by tomolimo 2012-03-06 17:03:37)

Re: Ticket bulk upload into GLPI

Hello to all and specially slinx,
I have finished my migration of tickets from prtracker to GLPI, and I've imported more than 100000 tickets (currently having more than 105000 tickets in GLPI).
I've prepared a tuto in the Wiki: http://www.glpi-project.org/wiki/doku.p … tsintoglpi.
Could be interesting for anyone who wants to migrate from any ticketing application to GLPI...:)!
If you have any questions or remarks: don't hesitate, post, post, post!
regards,
Tomolimo

Prod: GLPI 0.83.8 / Windows 2008 x64 / PHP 5.4.14 x64 / IIS 7.5 / MySQL 5.6.10-community x64
Dev: GLPI 0.83.8 / Windows 2008 x64 / PHP 5.4.14 x64 / IIS 7.5 / MySQL 5.6.10-community x64
Worldwide: ~5000 PC, ~700 servers, ~5700 users (15 languages), >160000 tickets, ~750 entities
Raynet is ARaymond (http://www.araymond.com) IT service management

17

Re: Ticket bulk upload into GLPI

Hi tomolino. I have to migrate tickets from JIRA to GLPI. Do you know if your method still works on GLPI 0.84.5?

18

Re: Ticket bulk upload into GLPI

Hello jroza,
I've got no ideas about this, then you have to investigate GLPI tables and to make some tests and to adapt the method if needed:)!
And last but not least you should post about your experience in the Wiki:)!
thank you,
regards,
Tomolimo

Prod: GLPI 0.83.8 / Windows 2008 x64 / PHP 5.4.14 x64 / IIS 7.5 / MySQL 5.6.10-community x64
Dev: GLPI 0.83.8 / Windows 2008 x64 / PHP 5.4.14 x64 / IIS 7.5 / MySQL 5.6.10-community x64
Worldwide: ~5000 PC, ~700 servers, ~5700 users (15 languages), >160000 tickets, ~750 entities
Raynet is ARaymond (http://www.araymond.com) IT service management