OTRS Ticket Export and Deletion Routine

We had a user of OTRS that required an extraction of their data from the system, prior to it being removed from OTRS.

We searched the net and could not find a suitable solution to this problem, so we came up with our own solution. This is documented here, in the hope that others find it useful.

First we need to find the queue_id that OTRS has assigned to this particular queue. To do this run the following query directly against the mysql database:

select id from queue where name = '<queue name>';

OTRS Ticket Export:

We are now in a position to start extracting the data from the database. The user required a csv file containing information about the tickets, that they could then use as a reference to the location of the files. So we therefore devised a script to pull out relevant information for each ticket. We chose to search for a particular creation date for the ticket and then pull it out along with all subsequent updates of the ticket (hence each ticket can have multiple lines outputted). The script we used was:

select b.tn "ticket number", a.a_from "from", a.a_to "to", a.a_subject "subject", a.a_body "body", a.create_time "creation time",concat(ifnull(c.salutation,''),' ',ifnull(c.first_name,''),' ',ifnull(c.last_name,'')) who, content_path, a.id from article a, ticket b, users c
where a.ticket_id = b.id
and a.create_by = c.id
and a.ticket_id in (select d.ticket_id from article d, ticket e where
substr(d.content_path,1,10) in ('2009/10/01') -- our ticket start date
and d.ticket_id = e.id
and e.queue_id = <QUEUE ID FOUND ABOVE>
and d.incoming_time = e.create_time_unix
order by d.id)
order by a.ticket_id;

This data was then outputted as a CSV file. The user could then use the content_path and id values to located the attachments on disk, see below.

Feel free to change the criteria, e.g. to pull back all tickets created in a particular month/year instead of date above.

Once we have this data, we can run a similar script to copy the data off the filesystem. Our attachments are stored on disk, under /Attachments. We attached another disk to the system and mounted this as /Attachments_Extract in order to get a second copy of the files.

We ran other query to generate a script containing the relevant copy commands (cp – we are running on linux). Modify this script to change the copy command to suit your environment:

select concat('mkdir -p /Attachments_Extract/',a.content_path,'/',a.id,';cp -rp /Attachments/',a.content_path,'/',a.id,'/* /Attachments_Extract/',a.content_path,'/',a.id,';')
from article a, ticket b, users c
where a.ticket_id = b.id
and a.create_by = c.id
and a.ticket_id in (select d.ticket_id from article d, ticket e where
substr(d.content_path,1,10) in ('2009/10/01') -- our ticket start date
and d.ticket_id = e.id
and e.queue_id = <QUEUE ID FOUND ABOVE>
and d.incoming_time = e.create_time_unix
order by d.id)
order by a.ticket_id;

Save this file as a shell script, open it and run the vi command:

:1,$s/”//g

to remove the additional quotes in the file.

Next run this command against the filesystem. Once it completes you should see that /Attachments_Extract has all the attachments for the tickets in question.


OTRS Attachment Deletion Routine:

To be on the safe side we decided that rather than do a straight deletion of the data, we would move the data to another volume. Once we were happy with this data, we will then delete the volume. This volume was mounted on the system as /Attachments_Deletion. The following script was then run to generate the script to move the images (again modify it to suit the correct move command based on your environment):

select concat('mkdir -p /Attachments_Deletion/',a.content_path,'/',a.id,';mv /Attachments/',a.content_path,'/',a.id,'/* /Attachments_Deletion/',a.content_path,'/',a.id,';')
from article a, ticket b, users c
where a.ticket_id = b.id
and a.create_by = c.id
and a.ticket_id in (select d.ticket_id from article d, ticket e where
substr(d.content_path,1,10) in ('2009/10/01') -- our ticket start date
and d.ticket_id = e.id
and e.queue_id = <QUEUE ID FOUND ABOVE>
and d.incoming_time = e.create_time_unix
order by d.id)
order by a.ticket_id;

Save this file as a shell script, open it and run the vi command:

:1,$s/”//g

Finally run this as a script against the OTRS server.

You should now find that the /Attachments_Deletion volume has all the attachments associated with these tickets. The files will now NOT exist in the master /Attachments volume.

When you are happy the /Attachments_Deletion volume can be removed.


Deleting of the tickets in OTRS can then be performed using the documented ticket deletion routines on the OTRS website.