Re: [otrs] OTRS Reporting

27 Feb 2009

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

Re: [otrs] OTRS Reporting

Keith Pierno