Re: [otrs] How to get a monthly report of tickets like this

30 Mar 2009

      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";
}