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 morning I found out how this all works in our OTRS system. In the Admin-Area the System Log showed that GenericAgent jobs were being run every 10 minutes with names such as 'New Start' and 'Leavers'. The OTRS manual mentioned that the GenericAgent can have jobs set up user the webpage. In Admin-Area I clicked on GenericAgent and this listed each of jobs. Editing the GenericAgent 'Leavers' job showed how often it had been set to be run, what string must be in the subject field, what queue the ticket is to be moved to, what CMD is to be run, etc. The CMD field has /opt/otrs/CustomCode/Leavers.pl in it. So every 10 minutes when the following OTRS cron job is run: */10 * * * * $HOME/bin/GenericAgent.pl -c db >> /dev/null any tickets with the 'Leavers' string in its subject field will be moved to our Leavers queue and the Perl script will be run which will fill in the free text fields for this ticket. Hope this is of some help. Regards 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?";