FW: Query about FreeText in OTRS v1.3.1 and also Upgrading questions

13 May 2009

      ...
...
...
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?";

paul.mcilfatrick＠bt.com

tags

participants (1)