>> We have been happily using OTRS 1.3.1 on Suse 9 since 2005 and it has
>> been working flawlessly (I know v1.3.1 is very old but works the way
we
>> want it so there has never been an incentive to upgrade!) .
>>
>> A feature we use is FreeText fields which we pre-fill using an sql
>> statement in a Perl script that is run when an new ticket with a
keyword
>> in its subject field is automatically moved to a certain queue.
> This is interesting, can you explain how have you done it?
As I said in my original e-mail, up to now I do not have much experience
with how our OTRS works (so far I have been just a user). I have UNIX
and some MySQL experience so following my post I have started to
investigate how this works (the person who installed and configured our
OTRS back in early 2005 was a contractor and didn't document anything -
he left in 2006).
My initial investigation shows that we have two Perl scripts in the
/opt/otrs/CustomCode directory called NewStarts.pl and Leavers.pl which
are run when a ticket arrives for someone new joining our office and the
other is run when a ticket arrives for someone leaving. These scripts
fill in the free text fields differently depending on the Newstart or
Leave text string in the e-mail subject field (I have listed both Perl
scripts at the end of this e-mail).
Searching the dump of the otrs database show that Leavers.pl is found
once:
INSERT INTO generic_agent_jobs VALUES ('Move Leavers from
Incoming','NewCMD','/opt/otrs/CustomCode/Leavers.pl');
It may take me some time but if I find out how this all works then I
will let you know.
>> This brings me to thinking about upgrading as our system is old but
we
>> only have our working system and we have no backup system that I can
>> test a upgrade on. Also, I have inherited the OTRS system so I do not
>> know much about it.
> The upgrade process is straighforward unless you have customized otrs
> modifying PM files. You can do a test upgrade during your off-peak
times
> by backing up your DB and backing up the entire OTRS directory. Just
> remember to "suspend" getting email from your incoming otrs mail
account
> , aka comment out a cron job: this way if something goes wrong, at
least
> you can restore your previous OTRS without loosing any regular email.
> Otherwise you can temporarily change your OTRS email (from OTRS
> configuration) to a new account, just for testing incoming emails, so
> your original mailbox will be left untouched in case of rollback.
>
> We had some problems regarding the DB upgrade but we switched from
MSSQL
> to MYSQL during the process, so if you already are on mysql you should
> experience no problems.
As I said in my other reply, I may use the following command to make a
copy of the whole disk when the PC is rebooted with a Knoppix CD:
dd if=/dev/hda bs=1k conv=sync,noerror | gzip
/media/sda1/<filename>.gz
and then I will try an upgrade ensuring that there is no network
connection so that e-mail will be disabled.
>> Question:
>>
>> From reading the various documents it seems that the process of
>> upgrading OTRS must be done in steps. Am I correct in that I cannot
go
>> from version 1.3.1 straight to the present version but must do it in
>> steps. If I am correct, what steps must be taken to get up to the
>> current version of OTRS?
> The steps involve upgrading the database by using the provided scripts
> in the tar.gz you downloaded and replace OTRS files. The rest of OTRS
> (PM configuration) can be imported, if I remember well. Maybe docs or
> other people can help you better than me for this part.
It has been confirmed that I must go from 1.3.1 to 2.0 and then each
point release at a time after that.
>> Question:
>>
>> Reading the UPGRADING file that came with version 1.3.1 (I see that
it
>> is present in every subsequent release) one of its points says
"Backup
>> everything (database, Kernel/Config.pm,
Kernel/Config/GenericAgent.pm,
>> var/*)".
>>
>> I can backup the files and directories mentioned but is there any
>> documentation of how to backup the MySQL database as I have little
>> experience with it?
> For mysql just do from shell:
> $ mysqldump otrsdbname >otrsdump.sql
>
> Then backup the entire OTRS directory.
>
> The mysql documentation is your friend:
> http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html
After my post I found that each night our OTRS database is backed up to
a file and then copied with other OTRS files to a Solaris server which
is later in the night backed up to tape. The backup of the database uses
mysqldump.
Thanks for your reply.
Paul McIlfatrick
The original NewStarts.pl file - I have now added extra free text fields
so that all 8 are used.
NewStarts.pl
============
#!/usr/bin/perl -w
use DBI;
$TicketNumber = $ARGV[0];
# $TicketID = $ARGV[1];
$dbh = DBI->connect("dbi:mysql:otrs:otrs.<domain>", "<user>",
"<password>");
$contents = "";
$emailText="Not Done";
$phoneText="Not Done - NU";
$assetText="Not Done";
$pcText="Not Done";
# Get contents from submitted new start form
$sql = "SELECT article.a_body FROM article,ticket WHERE ticket.id =
article.ticket_id AND article.article_type_id = 1 AND article.a_subject
LIKE 'New User%' AND ticket.tn = $TicketNumber";
$sth = $dbh->prepare($sql);
$sth->execute();
@row = $sth->fetchrow_array;
$contents = $row[0];
$sth->finish;
# search for "Location: Off Shore" and change emailText
@lines = split(/\n/,$contents);
foreach my $line_val (@lines){
if ($line_val =~ m/^Location:/i && $line_val =~ m/Off Shore/i){
$emailText="Done - N/A OS";
$phoneText="Done - N/A OS";
$assetText="Done - N/A OS";
$pcText="Done - N/A OS";
}
elsif ($line_val =~ m/^User Start Date:.*/i && $pcText !~ /^Done.*/){
$line_val =~ s/User Start Date://;
$line_val =~ s/[^0-9\/]//g;
if ($line_val !~ m/\d\d\/\d\d\/\d\d*/){ $line_val=""; }
$pcText = "Not Done - $line_val";
}
}
$sql = "update ticket set freekey1='EMail', freetext1='$emailText',
freekey2='Phone', freetext2='$phoneText', freekey3='Assets',
freetext3='$assetText', freekey4='PC', freetext4='$pcText' WHERE
tn='$TicketNumber'";
$sth = $dbh->prepare($sql);
$sth->execute || die "Could not execute SQL statement ... maybe
invalid?";
$dbh->disconnect;
The original Leavers.pl file - I have now added extra free text fields
so that all 8 are used.
Leavers.pl
==========
#!/usr/bin/perl -w
use DBI;
$TicketNumber = $ARGV[0];
# $TicketID = $ARGV[1];
$dbh = DBI->connect("dbi:mysql:otrs:otrs.<domain>", "<user>",
"<password>");
$sql = "update ticket set freekey1='EMail', freetext1='Not Done',
freekey2='Phone', freetext2='Not Done - LE', freekey3='Assets',
freetext3='Not Done', freekey4='PC', freetext4='Not Done' WHERE
tn='$TicketNumber'";
$sth = $dbh->prepare($sql);
$sth->execute || die "Could not execute SQL statement ... maybe
invalid?";
---------------------------------------------------------------------
OTRS mailing list: otrs - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/otrs
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs
NEW! ENTERPRISE SUBSCRIPTION - Get more information NOW!
http://www.otrs.com/en/support/enterprise-subscription/
