Occasionally our customer service team wants to find the ticket that is related to a filename that was attached to an email that went through OTRS Free (the system we use to manage our customer-facing support tickets). As to be expected, we’ve accumulated a lot of tickets and a number of these include attachments and currently reverse lookup by attachment is not a feature in OTRS Free. We didn’t want to modify OTRS and complicate future upgrades, so we decided to create a separate web UI to run an attachment search. Having worked with Perl in my prior sysadmin job, I picked this language as a quick and easy way to handle the issue.This blog walks through procedure I used to build a solution for the team.
Finding the data
A search of the database dump of OTRS did not lead results. However, searching the filesystem where OTRS Free was installed shows that attachments are stored under:
/opt/otrs/var/article/ (e.g. /opt/otrs/var/article/2016/07/12/98725144/sample.pdf)
The number after the date is the article id. And OTRS lets you look up the ticket number and related information for a given article id easily.
Using Perl to collect the data and to insert it into MySql
After I installed ActivePerl (which was quick and painless), the next step was to collect the data and insert it into MySql
I just wanted a table with the following fields: Attachment name, Date, Otrs ticket, Otrs customer name, otrscustomerid
Once the data is in a table a search can be done on that table in many ways (I didn’t cover creating the web UI in this blog).
Step 1: Install ActivePerl on some host (a Linux x64 VM in my case)
I installed ActivePerl to
/data/otrs-attachment-search/updater-script/opt/ActivePerl-5.24:
mkdir -p /data/otrs-attachment-search/updater-script/opt/ActivePerl-5.24 tar xfz ActivePerl-5.24.0.2400-x86_64-linux-glibc-2.15-300561.tar.gz cd ActivePerl-5.24.0.2400-x86_64-linux-glibc-2.15-300561 ./install.sh
Step 2:After installation is complete install some packages for mysql and date functions
/data/otrs-attachment-search/updater-script/opt/ActivePerl-5.24/site/bin/ppm install DBI DBD-mysql DateTime
Step 3: Create a mysql table to populate (assuming MySql or MariaDB is ready)
CREATE TABLE `otrs_attachment_search_app_otrsattachment` ( `id` int(11) NOT NULL AUTO_INCREMENT, `attachment_name1` varchar(255) NOT NULL, `date1` varchar(10) NOT NULL, `otrs_ticket1` varchar(30) NOT NULL, `otrs_customer_name` varchar(200) NOT NULL, `otrs_customer_id` varchar(150) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=582763 DEFAULT CHARSET=latin1
Step 4: Write a Perl script, starting small and adding code until an acceptable end result
Here is the end result:
#!/data/otrs-attachment-search/updater-script/opt/ActivePerl-5.24/bin/perl
use feature 'signatures';
use POSIX qw(strftime);
use DateTime qw();
use DBI;
# exit if this script is already running:
my $np = `ps -ef | grep "ActivePerl-5.24/bin/perl.*update_attachment_info.pl" | grep -v grep | wc -l`;
chomp $np;
print "Number of running processes: $np\n";
if ($np > 2) {
print "update_attachment_info.pl is already running... exiting.\n";
exit 2;
}
# get directory listing of yesterday's attachments:
$yesterday = DateTime->now->subtract(days => 1)->strftime( "%Y/%m/%d");
print "updating article list with articles from yesterday: $yesterday\n";
$command = "ssh root\@otrs \'find /opt/otrs/var/article/$yesterday -type f -a ! -name plain.txt -a ! -name file-? -a ! -name file-?.content_type -a ! -name file-?.content_alternative -a ! -name \"*.content_type\" -a ! -name \"*.content_id\" -a ! -name \"*.disposition\"' > /data/otrs-attachment-search/updater-script/articles ";
print "command used: $command\n";
@result = `$command`;
if ($?) {
print "error updating articles: $? , $!\n"; exit 1;
}# iterate through the list that was just downloaded:
$dbh_otrs = DBI->connect('DBI:mysql:otrs5;host=10.99.10.99', 'username1', 'password1'
) || die "Could not connect to database: $DBI::errstr";
$dbh_searchdb = DBI->connect('DBI:mysql:otrs_attachment_search;host=127.0.0.1', 'username2', 'password2'
) || die "Could not connect to database: $DBI::errstr";
my $filename = '/data/otrs-attachment-search/updater-script/articles'; # articles is the result of find /opt/otrs/var/article/ -type f
open(my $fh, '<:encoding(UTF-8)', $filename)
or die "Could not open file '$filename' $!";
# loop over the file
while (my $row = <$fh>) {
chomp $row;
#example: /opt/otrs/var/article/2016/07/04/312490/questions.pdf
if ($row =~ /opt\/otrs\/var\/article\/(\d\d\d\d)\/(\d\d)\/(\d\d)\/(\d+)\/(.+)/s) {
$article = $4;
$attachment = $5;
$date1 = "$1-$2-$3";
$ticket = get_ticket($article);
# check if record is already in the db
if ( isrecordalreadypresent($attachment, $date1, $ticket) ) {
print "record for $attachment, $date1, $ticket already exists, skipping\n";
}
else{
$customer_id = get_customer_id($ticket);
$customer_name = get_customer_name($customer_id);
print "inserting: attachment:\t$5\tdate:\t$1-$2-$3\tarticle:\t$4\tticket:\t$ticket\tcustomer-id:\t$customer_id\tcustomer-name:\t$customer_name\n";
insert_attachment_data($attachment, $date1, $ticket, $customer_id, $customer_name);
}
}
}
# look up the ticket number:
sub get_ticket($article_id){
$sql = "select ticket_id from article where id=?";
$sth = $dbh_otrs->prepare($sql);
$sth->bind_param(1, $article_id);
$sth->execute();
while (my @row = $sth->fetchrow_array()) {
my ($tmp1) = @row;
$sth->finish();
return $tmp1;
}
}
# look up the customer number:
sub get_customer_id($ticket_id){
print "select customer_id from ticket where id=$ticket_id\n";
$sql = "select customer_id from ticket where id=?";
$sth = $dbh_otrs->prepare($sql);
$sth->bind_param(1, $ticket_id);
$sth->execute();
while (my @row = $sth->fetchrow_array()) {
my ($tmp1) = @row;
$sth->finish();
return $tmp1;
}
}
# look up the customer name:
sub get_customer_name($customer_id){
$sql = "select name from customer_company where customer_id=?";
$sth = $dbh_otrs->prepare($sql);
$sth->bind_param(1, $customer_id);
$sth->execute();
while (my @row = $sth->fetchrow_array()) {
my ($tmp1) = @row;
$sth->finish();
return $tmp1;
}
return $customer_id;
}# insert attachment data into table
#MariaDB [otrs_attachment_search]> describe otrs_attachment_search_app_otrsattachment;
#+--------------------+--------------+------+-----+---------+----------------+
#| Field | Type | Null | Key | Default | Extra |
#+--------------------+--------------+------+-----+---------+----------------+
#| id | int(11) | NO | PRI | NULL | auto_increment |
#| attachment_name1 | varchar(255) | NO | | NULL | |
#| date1 | varchar(10) | NO | | NULL | |
#| otrs_ticket1 | varchar(30) | NO | | NULL | |
#| otrs_customer_name | varchar(200) | NO | | NULL | |
#| otrs_customer_id | varchar(150) | NO | | NULL | |
#+--------------------+--------------+------+-----+---------+----------------+
sub insert_attachment_data($attachmentname, $date, $ticket, $customer_id, $customer_name){
$query = 'INSERT INTO otrs_attachment_search.otrs_attachment_search_app_otrsattachment (
attachment_name1,
date1,otrs_ticket1,
otrs_customer_id,
otrs_customer_name) VALUES (?,?,?,?,?)';
print "\ninserting: $attachmentname,$date,$ticket,$customer_id,$customer_name\n";
$dbh_searchdb->do($query, undef, $attachmentname, $date, $ticket, $customer_id, $customer_name);
}
sub isrecordalreadypresent($attachmentname, $date, $ticket){
$sql = "select id from otrs_attachment_search.otrs_attachment_search_app_otrsattachment where attachment_name1=? and date1=? and otrs_ticket1=?";
$sth = $dbh_searchdb->prepare($sql);
$sth->bind_param(1,$attachmentname);
$sth->bind_param(2,$date);
$sth->bind_param(3,$ticket);
$sth->execute();
if (my @row = $sth->fetchrow_array()) {
$sth->finish();
return 1;
}
return 0;
}
$dbh_otrs->disconnect();
$dbh_searchdb->disconnect();
Note: I used a new Perl feature (which has been available since version 5.20) that makes passing of arguments to functions easier:
use feature 'signatures';
sub fn1 ($a, $b) {
return $a + $b;
}
The above script was setup to run twice a day and has been keeping the table up-to-date.
For me, I found ActivePerl was easy to install and to use. I was able to get the data I needed, parse it and insert it into a database quickly. A vast number of Perl examples all over the web made it easy to find examples when I was stuck…so I was never really stuck for more than a minute. The whole solution can be backed up easily by just backing up the script and the entire ActivePerl-5.24 installation folder. And it will run on any Linux! If any changes or additions are needed in the future the script can be adapted very easily.
Download the free community edition of ActivePerl 5.24
Title image courtesy of Mika Baumeister on Unsplash.
