Luca,I wrote a perl script which does some of what you are looking for. With a little work it should be trivial to get the remainder of the data. In the current output it does a summary of close times for a queue you should be able to grab the data before it is summarized and create your report. Attached is the perl script and readme for your review you will need to get a copy of the date-business module from CPAN.
Keith Pierno Lulu.com Luca Maranzano wrote:
Hi all, we are using OTRS 2.3.4 and we are very happy with it! We are in the need to generate a report with the following data: - For all the ticket of the last month in a particular Queue: - date and time of creation time - subject of the ticket - date and time of closing time - operator who worked on the ticket - (if possible) time elapsed between open and close time We have tryed the Stat module but without success. Is it possibile or is it necessary to query directly the Database? Thank you in advance. Kind regards, Luca
#!/usr/bin/perl
#Description
# This script will produce a way to get time to resolution for otrs tickets
#Modules
use strict;
use warnings;
use DBI;
use Getopt::Long qw(:config pass_through);
use Date::Business;
#vars
my $dbh;
my $sql;
my $sth;
my $i;
my $j;
my @loh;
my %tickets;
my %ttr;
my $ddiff;
my $format='tab';
my $help=0;
my $headers=0;
my $queue="Raw";
#update below value to set your default start time
my $start='YYYYMMDD';
my $end = `/bin/date +%Y%m%d`;
chomp $end;
#Fetch options
GetOptions ('format=s' => \$format,
'start=i' => \$start,
'end=i' => \$end,
'noheaders' =>\$headers,
'queue=s' =>\$queue,
'help|?' => \$help);
#make sure that $start and $end are set to 8 digit values or bail
help_msg() unless ($end =~m/^\d{8}$/)&&($start=~m/^\d{8}$/);
#make sure that the end date is set to 11:59 pm so you get the activity for the
whole day
$end = $end."235959";
help_msg() if $ARGV[0] || $help == 1;
help_msg() unless ($format eq 'tab') || ($format eq 'csv')|| ($format eq
'html');
#Help message
sub help_msg
{
print "\notrs.pl\n\nDescription:\n\tThis script runs against the OTRS
ticket tracking database calculates time to resolution. ";
print "By default the script queries tickets in the Desktop
queue.\n\nOptions:";
print "\n\t--queue\tSets the queue name for the report.\n\t\t\tCheck
your ORTS installation for a list of valid queue names\n\t\t\t Default queue is
Desktop\n";
print "\n\t--format\tSets the output format (default format is
tabbed).\n\t\t\tValid formats are: tab csv html\n";
print "\n\t--start\t\tSets the start date for the report (default value
is 04/01/2008).\n\t\t\tValid format is YYYYMMDD\n";
print "\n\t--end\t\tSets the end date for the report (default value is
the current date).\n\t\t\tValid format is YYYYMMDD\n";
print "\n\t--noheaders\tCreates a single line of output devoid of
headers and titles.\n\t\t\tOutput will be in the format start time end time
total tickes ticket count and percent of total";
print "\n\t\t\tfor each of the 5 normal ranges\n";
print "\n\t--help\t\tDisplays this help message.\n";
print "\nUsage:\n\tTo run the default report (20080401 until now) in
tabbed output:\n\t\tprompt> otrs.pl\n";
print "\tTo create a report formatted for csv output\n\t\tprompt>
otrs.pl --format=csv\n";
print "\tTo create a report formatted for html output\n\t\tprompt>
otrs.pl --format=html\n";
print "\tTo create a report from 01/01/2009 until now in csv
output\n\t\tprompt> otrs.pl --start=20090101 --format=csv\n";
print "\tTo create a report from 01/01/2009 to 03/31/2009 at 23:59:59
in html\n\t\tprompt> otrs.pl --start=20090101 --end=20090331 --format=html\n";
print "\tTo create a report as a single line of output in csv
formatting\n\t\tprompt> otrs.pl --format=csv --noheaders\n";
print "\tTo query the queue spam and return csv output.\n\t\tprompt>
otrs.pl --format=csv --queue=spam\n";
exit;
}
#logic
#replace xxxxx below with real password
$dbh = DBI->connect('dbi:mysql:otrs','otrsreport','xxxxx')
or die "Connection Error: $DBI::errstr\n";
#test does the queue exist
$sql="select count(*) from queue where name='$queue'";
$sth = $dbh->prepare($sql);
$sth->execute
or die "SQL Error: $DBI::errstr\n";
my @test1;
@test1 = $sth->fetchall_arrayref();
if ( $test1[0][0][0] == 0 )
{
print "Error: queue $queue does not exist. Check your available queues
and run again.\n";
exit;
}
$sql = "select
min(a.id) as uid,
a.ticket_id,
CASE d.name WHEN 'merged' THEN 'closed' WHEN 'closed successful' THEN
'closed' WHEN 'closed unsuccessful' THEN 'closed' WHEN 'pending reminder' THEN
'closed' WHEN 'pending auto close+' THEN 'closed' WHEN 'pending auto close-'
THEN 'closed' ELSE 'open' END as status,
cast(a.change_time as date) as day,
cast(a.change_time as time) as time
from
ticket_history as a,
queue as c,
ticket_state as d,
ticket e
where
a.queue_id = c.id and
a.state_id = d.id and
a.ticket_id = e.id and
c.name='$queue' and
d.name in ('new','closed successful','closed
unsuccessful','open','pending reminder','pending auto close+','pending auto
close-','merged') and
e.create_time >= $start and
e.create_time <= $end
group by a.ticket_id, d.name, a.change_time
order by a.ticket_id, a.change_time";
$sth = $dbh->prepare($sql);
$sth->execute
or die "SQL Error: $DBI::errstr\n";
@loh = $sth->fetchall_arrayref({});
for $i ( 0 .. $#{$loh[0]} )
{
$j = $i;
$j--;
unless ( exists $tickets{$loh[0][$i]{ticket_id}} )
{
$tickets { $loh[0][$i]{ticket_id} } = [ {day =>
$loh[0][$i]{day} , status => $loh[0][$i]{status}}];
}
else
{
if ( $loh[0][$i]{status} ne $loh[0][$j]{status} ){
push @{ $tickets{$loh[0][$i]{ticket_id}} }, {day =>
$loh[0][$i]{day} , status => $loh[0][$i]{status}};
}
}
}
#ttr
for $i ( keys %tickets )
{
my ($var1, $var2, $day1, $day2) = ( '0', '0', '0', '0');
if ( $#{$tickets{$i}} == 0 )
# ticket is open with no close state so use todays date
# need to check state some exist only as closed in the history table
{
if ( $tickets{$i}[0]{day} eq "open" )
{
$var1=$tickets{$i}[0]{day};
$var1 =~ s/-//g;
$day1 = new Date::Business(DATE => $var1 );
$day2 = new Date::Business();
$ddiff = $day2->diffb($day1);
$ttr{$i} = $ddiff;
}
#else start day and end day are equal
$ttr{$i} = 0;
}
elsif ( $#{$tickets{$i}} == 1 )
# We know that there are only 2 states so lets do the math and go to the
next ticket
{
#need to find out if the first state is open or closed
#if first state is open then do bellow otherwise we need to do
some additional checking
if ( $tickets{$i}[0]{status} eq 'open' )
{
$var1=$tickets{$i}[0]{day};
$var2=$tickets{$i}[1]{day};
$var1 =~ s/-//g;
$var2 =~ s/-//g;
$day1 = new Date::Business(DATE => $var1 );
$day2 = new Date::Business(DATE => $var2 );
$ddiff = $day2->diffb($day1);
$ttr{$i} = $ddiff;
}
else
{
#did it move queues
#do sql here
my ($sth1, $sth2, @return1, @return2, $sql1, $sql2,
$tempval);
$sql1="select max(id) from ticket_history where
ticket_id = $i";
$sth1 = $dbh->prepare($sql1);
$sth1->execute
or die "SQL Error: $DBI::errstr\n";
@return1 = $sth1->fetchall_arrayref();
$sql2 = "select b.name from ticket_history a, queue b
where a.id=$return1[0][0][0] and a.queue_id=b.id";
$sth2 = $dbh->prepare($sql2);
$sth2->execute
or die "SQL Error: $DBI::errstr\n";
@return2 = $sth2->fetchall_arrayref();
#check if not do below
if ( $return2[0][0][0] eq $queue )
{
$var1 = $tickets{$i}[$j]{day};
$var1 =~ s/-//g;
$day1 = new Date::Business(DATE => $var1 );
$day2 = new Date::Business();
$ddiff = $day2->diffb($day1);
$ttr{$i} += $ddiff;
}
}
}
else
# More than one complete set of state changes need a cumlative tab of
days open
{
if ( $tickets{$i}[0]{status} eq 'open')
{
$ttr{$i} = 0; # start with a zero value and add to it
my $no_close=0;
if ( $#{$tickets{$i}}%2 == 0 )
{
# ok the there is no close date
$no_close=1;
}
for $j ( 0 .. $#{$tickets{$i}})
{
if ( $j == $#{$tickets{$i}} )
{
if ( $no_close == 1 )
#Need to add a db call to see if the
ticket was moved into another queue
#if ticket was moved use that move date
as the end date
#else assume ticket is still open
{
#did it move queues
#do sql here
my ($sth1, $sth2, @return1,
@return2, $sql1, $sql2, $tempval);
$sql1="select max(id) from
ticket_history where ticket_id = $i";
$sth1 = $dbh->prepare($sql1);
$sth1->execute
or die "SQL Error:
$DBI::errstr\n";
@return1 =
$sth1->fetchall_arrayref();
$sql2 = "select b.name from
ticket_history a, queue b where a.id=$return1[0][0][0] and a.queue_id=b.id";
$sth2 = $dbh->prepare($sql2);
$sth2->execute
or die "SQL Error:
$DBI::errstr\n";
@return2 =
$sth2->fetchall_arrayref();
#check if not do below
if ( $return2[0][0][0] eq
$queue )
{
$var1 =
$tickets{$i}[$j]{day};
$var1 =~ s/-//g;
$day1 = new
Date::Business(DATE => $var1 );
$day2 = new
Date::Business();
$ddiff =
$day2->diffb($day1);
$ttr{$i} += $ddiff;
}
}
else
{
$var2=$tickets{$i}[$j]{day};
$var2 =~ s/-//g;
$day2 = new Date::Business(DATE
=> $var2 );
$ddiff = $day2->diffb($day1);
$ttr{$i} += $ddiff;
}
}
else
{
if ( $j%2 == 0 )
{
$var1 = $tickets{$i}[$j]{day};
$var1 =~ s/-//g;
$day1 = new Date::Business(DATE
=> $var1 );
}
else
{
$var2=$tickets{$i}[$j]{day};
$var2 =~ s/-//g;
$day2 = new Date::Business(DATE
=> $var2 );
$ddiff = $day2->diffb($day1);
$ttr{$i} += $ddiff;
}
}
}
}
}
}
#count the number of keys in %ttr
my $total_keys=keys(%ttr);
#tickets 1 day or less
# 2 days
# 3 days
# 4 days
# 5 days or more
my @summary = (0) x 5;
for $i ( keys %ttr )
{
if ( $ttr{$i} <= 1 )
{
++$summary[0];
}
elsif ( $ttr{$i} == 2 )
{
++$summary[1];
}
elsif ( $ttr{$i} == 3 )
{
++$summary[2];
}
elsif ( $ttr{$i} == 4 )
{
++$summary[3];
}
else
{
++$summary[4];
}
}
#set params for tab delimited output change if other
my $header="\n";
my $title="";
my $endtitle="\n";
my $linebreak="\n";
my $begintable="";
my $endtable="\n";
my $beginrow="";
my $endrow="\n";
my $delimiter="\t";
my $footer="\n";
if ($format eq 'csv')
{
$delimiter=",";
}
elsif ($format eq 'html')
{
$header="<html>\n";
$title="<h2>";
$endtitle="</h2>\n";
$linebreak="<br>\n";
$begintable="<table border=1 cellspacing=0 cellpadding=10>";
$endtable="</table>\n";
$beginrow="<tr><td>";
$endrow="</td></tr>\n";
$delimiter="</td><td>";
$footer="</html>\n";
}
#printable date format for $start and $end
my $date_pattern = qr'(\d{4})(\d{2})(\d{2})(\d{2})?(\d{2})?(\d{2})?';
my
($start_year,$start_month,$start_day,$start_hour,$start_minute,$start_second)=(0,0,0,0,0,0);
my
($end_year,$end_month,$end_day,$end_hour,$end_minute,$end_second)=(0,0,0,0,0,0);
($start_year,$start_month,$start_day,$start_hour,$start_minute,$start_second)=($start=~m/$date_pattern/);
($end_year,$end_month,$end_day,$end_hour,$end_minute,$end_second)=($end=~m/$date_pattern/);
#Normal formatting looks like
#
# Title = OTRS Time To Resolution for $start to
$end
# Total tickets = xxxx
#
# Days to Resolution <1 2 3 4 5>
# Total Tickets x x x x x
# Percentage of tickets y y y y y
#
# No headers looks like this with specified delimiters
#
# start_date end_date total_tickets count_for_<1 count_for_2 .... count_for_5>
%_for_<1 %_for_2 %_for_5>
if ( $headers == 0 )
{
print "$header";
print "$title"."OTRS Time To Resolution for queue $queue
$start_month/$start_day/$start_year ";
print "to $end_month/$end_day/$end_year ";
print "$end_hour\:$end_minute "if (defined $end_minute);
print "$endtitle";
print "Total Tickets = $total_keys $linebreak";
print "$linebreak";
print "$begintable"."$beginrow"."Days to Resolution"."$delimiter";
print "$delimiter" if ($format eq 'tab');
print
"<1"."$delimiter"."2"."$delimiter"."3"."$delimiter"."4"."$delimiter"."5>"."$endrow";
print "$beginrow"."Total Tickets"."$delimiter";
print "$delimiter"."$delimiter" if ($format eq 'tab');
for $i (0 .. 3)
{
print "$summary[$i]"."$delimiter";
}
print "$summary[4]"."$endrow";
print "$beginrow"."Percentage of Tickets"."$delimiter";
print "$delimiter" if ($format eq 'tab');
for $i (0 .. 3)
{
printf("%.1f",(($summary[$i]/$total_keys)*100));
print "%"."$delimiter";
}
printf("%.1f",(($summary[4]/$total_keys)*100));
print "%"."$endrow"."$endtable";
print "$footer";
}
else
{
print "$beginrow";
print
"$start_month/$start_day/$start_year"."$delimiter"."$end_month/$end_day/$end_year"."$delimiter"."$total_keys"."$delimiter";
for $i (0 .. 3)
{
print "$summary[$i]"."$delimiter";
}
print "$summary[4]"."$delimiter";
for $i (0 .. 3)
{
printf("%.1f",(($summary[$i]/$total_keys)*100));
print "%"."$delimiter";
}
printf("%.1f",(($summary[4]/$total_keys)*100));
print "%"."$endrow";
}
README
Description: application/text
--------------------------------------------------------------------- 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/
