Page History

At the start of the month, send an email to oc@geant.org asking him to get an extract of the OTRS data and dump it onto them to export the OTRS database - they have instructions for this. Currently they put it on test-msr.geant.net in the /tmp/ directory.

Copy the file from the /tmp directory to your laptop into a directory called c:\otrs\database.

Using 7-Zip (http://www.7-zip.org/download.html) unzip the file into the c:\otrs\database directory.

Run the Java Batch Program called SQLGenerator. This will take the very large .sql file and split it up into it's separate table files and store them in the c:\otrs\database\datafiles directory.

** Richard to distribute SQLGenerator code to team.

Before you import the data, please delete the c:\otrs\database\datafiles\header_stuff.sql

, it would be better if we get them to export it to test-dwarehouse01.geant.net in the /msr directory.

(Alternative to the above, ask Emma to do this.)

Unzip the file using gunzip into the same directory.

Rename the file to /msr/DatabaseBackup.sql

Run the script /msr/splitsql.sh 

This will split the very large DatabaseBackup.sql into seperate .sql files in the /msr/datafiles directory. The script takes about 3 minutes to complete.

This next bit may not work properly as it may run out of disk space when trying to dump the tables to otrs.sql; in which case you will need to delete the expanded DatabaseBackup.sql file and export the tables manually:

sudo mysqldump-as-root otrs dynamic_field_value ticket ticket_history ticket_state ticket_type > otrs.sql

Now run the script /msr/dataload.sh

This loads the data into the OTRS database on test-dwarehouse01.geant.net this may take about 10 - 20 minutes due to the size of the database. The script also exports the following tables into a file called /msr/otrs.sqlUsing MYSQL Workbench, import the following tables into the otrs database on test-msr.geant.net:

dynamic_field_value

ticket

ticket_history

ticket_state

ticket_type

Log into test-msr.geant.net and scp the file from test-dwarehouse01.geant.net:/msr/otrs.sql :

scp test-dwarehouse01.geant.net:/msr/otrs.sql /tmp/

Load the sql into the otrs database using the reportsUser user:

mysql -ureportsUser -p otrs < /tmp/otrs.sql

Using MYSQL Workbench, import the otrs.sql file into the otrs database on test-msr.geant.net.

The queues used generate the results are below:

Business Development

Eduroam

Eduroam::Eduroam_SMEs

Educonf

Educonf::Educonf_SME

GTS

GTS::GTS SA2
Implementation
Maintenance

Operations Centre
Operations Centre::Internal
Operations Centre::Resolved
Pending

Security::CERT
Service Assurance::Customer Orders