Merging 2 OTRS systems.

[Evanscheijen]

Hello all,

At my company we have 2 instances of OTRS for 2 different departments.
For now any interaction between the two departments is handled by mailing the ticket from 1 system to the other and vice versa.
We have built a new system with two goals, implementation of change management and integration of the call registration for 3 departments, the 2 mentioned before and another one.
Problem is that both departments allready using OTRS have a large history of tickets both opened and closed they both need to keep.
I have searched this forum and the internet on any info on how to tackle this, but came up with not much more then the statement that export/import of tickets is not supported.
The 2 systems are running OTRS 3.0.11 with ITSM 3.0.5 and the version we want to move to is running 3.1.10 with ITSM 3.1.6.
I was wondering if anyone has ever tried this, what would be the best approach, what should I avoid here and what are the risks for the data integrety?.
I was thinking about making sql dump of both systems, somehow merge these dumps and import merged dumps.
Would there be any other (better) way to do this

Kind regards,

Erik van Scheijen

[crythias]

avoid merging tickets. There's no *easy* way to manage the consistency and possible collision of ticket numbers, let alone ticket IDs because they're so entrenched in what they mean.

Oh, if I were to consider doing this, I'd probably try to do some sort of sql dump text search/replace mass edit of ticket ids from one system, but in reality, it's going to severely challenge you to adjust the ids for all tickets, all articles, just to be sure they don't collide with the other system. Is it possible? Oh, sure, almost anything's possible. It it reasonable? I don't think so. In my opinion, you should break ties with one of the systems ASAP and use a different SystemID in the new system (optional, but think about it). The new SystemID will help separate "old" system tickets from "new" system tickets.

Can it be done? I wouldn't even want to try this. It's not supported, and will the effort to do this be worth the aggravation? If there's a business case for it, there probably should also be a budget for getting someone professional to assist you with this.

[Evanscheijen]

Thanks for the quick reply,

I am aware of the risks involved, but it is allways good to have my suspicions confirmed. What we need to consider is wether the needs are woth the effort needed to do this.
I am leaning towards having one of the departments keeping their old OTRS system as a historical reference of some sort.
That way only the open tickets need to be moved to the new system which can be done through mail.
I will leave this post open on the off-chance that someone in the comunity has a usable method. I still have to inform the departments of situation and you never know if they turn it into a bussiness case after all.

[Evanscheijen]

It seems each department has its own system_id so as far as I can see there will be no overlap in the ticket_id's would that be enough for a fair chance at making a merge work?
Or do you see more challenges in merging both databases?

[jojo]

it is not possible to merge the two systems. All internal ids will collide

[crythias]

will be no overlap in the ticket_id's would that be enough for a fair chance at making a merge work?

ticket number overlap? no. Ticket.id? yes

the ticket table has (among others) an id column and a tn (ticket number) column. the tns not colliding will only be part of the issue. And that's not a good part, either. Since the code checks for SystemID as part of the ticket number, followups to open tickets with a different SystemID will create new tickets in the common database.

Next, you'll need to look for ticket_id in all the other tables ... this corresponds to ticket.id and needs to match. To do this, you'll need to (at least?) get max(ticket.id)+1 in the destination and add that value to all the ticket_id values everywhere in the source as well as ticket.id in the source. Then you can append every ticket* and article* table .... maybe. you'll also need to deal with any ticketfreetext fields and whatever else you'll need. There are queues and services that might collide on ID and any other *_id in the ticket table.

It's not as simple as just importing tickets. You have to look at ownership, queues, customers, responsibles, groups, priorities, states, types, services, SLAs ... Any little discrepancy and you'll have a ticket attached to the wrong customer, or wrong permissions ...

Start with looking at the schema for ticket and go from there. Use the PNGs for links, and make your own determination. The challenges are beyond ticket numbers.

[Evanscheijen]

Hm, challenges challenges.....
I am still considering to have a go at it, this will involve a lot of staring at the database schema it seems.
I allready noticed there are at least 7 linked fields to the ticket_id field in the ticket table so this will be fun
I will keep track of all fields and links involved and will post follow ups for my fellow forum members to take advantage off.
Thing is both departments for now hold on to their functional demand to keep all history.
First step will be determining what kind of db dump I need to work with, the standard postgress dbdump probably will only complicate it so I guess Iĺl have to create my own through the SQL engine.
Thanks so far for pointing me at the right direction I will keep you posted on the progress.