I have written a perl script that does a time until resolution and
tracks the number of days a ticket remains open. It could be a great
starting point for you to modify and create a report that you are
looking for.
Keith
Rabindra Pandey wrote:
<div class="moz-text-flowed" style="font-family: -moz-fixed">Hi,
I am fairly new to otrs world. I want to create a report of all
tickets that have been untouched for last 3 days. Untouched is defined
as a ticket which have no activity during last 3 days. Any mail sent
from OTRS by system is not accounted as activity.
I want create report by writing mysql script and my otrs version is 1.2.3
Any help or guidance will be greatly appreciated.
Thanks
</div>
#!/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 Data::Dumper;
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="Desktop";
my $start='20080401';
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
$dbh = DBI->connect('dbi:mysql:otrs','otrsreport','q5epR-Ez')
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}};
}
}
}
#print Dumper(\%tickets);
#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;
}
}
}
}
}
}
#print Dumper( \%ttr );
#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";
}
---------------------------------------------------------------------
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/
