RyanFrantz

Request Tracker: Hacking Reports Update

A few months ago I wrote about some reports I needed to generate out of Request Tracker (RT).  After some months of using RT, I found that one of the scripts (rtTicketFirstResponse.pl) did not always output accurate data; sometimes, due to the conditions I had coded for selecting transactions, some tickets were not counted in the final tally.  After some debugging, I found that I simply needed to simplify the code.  Instead of looking for specific transactions, I modified the code to compare the 'Created' and 'Started' dates for the ticket.  Simple.

rtTicketFirstResponse.pl

#!/usr/bin/perl

#
# rtTicketFirstResponse.pl - query RT and generate a report on how long it took to respond to a request
# Author: Ryan Frantz ryanfrantz [at] informed-llc [dot] com
#

use warnings;
use strict;

use lib "/usr/local/rt/lib/";

use RT;
use RT::User;
use RT::Interface::CLI qw( CleanEnv GetCurrentUser );   # I guess these aren't exported?

use Date::Calc qw( Date_to_Time );
use Time::Interval;

## start me up!

# set the stage...
CleanEnv();
RT::LoadConfig;
RT::Init;

my $currentUser = GetCurrentUser();
my $tickets = RT::Tickets->new( $currentUser );
my $query = qq[ Created > '7 days ago' AND ( Queue = 'Support Desk (Level 1)' OR Queue = 'Support Desk (Level 2)' ) AND Status = 'resolved' ];

my $binThreshold = '7200';	# 2 hours, in seconds
# define the response times for each bin; in seconds
my %histogramData = (
	'900'	=>	'0',	# 15min
	'1800'	=>	'0',	# 30min
	'2700'	=>	'0',	# 45min
	'3600'	=>	'0',	# 1hour
	'4500'	=>	'0',	# 1hour15min
	'5400'	=>	'0',	# 1hour30min
	'6300'	=>	'0',	# 1hour45min
	$binThreshold	=>	'0',	# 2hours
	#'more'	=>	'0'	# $binThreshold + 1; we'll add this key in at the end
);

my $numAboveBinThreshold;
sub tallyResponseTime {

	my $responseTime = shift;
	#print "\nTEST VALUE: $responseTime\n";	# debug
	my $rangeLow = '0';

	foreach my $binResponseTime ( sort { $a <=> $b } keys %histogramData ) {	# ensure a numeric sort; not ASCII-betical
		if ( $responseTime >= $rangeLow and $responseTime < $binResponseTime ) {
			$histogramData{ $binResponseTime }++;
			last;   # no need to continue
		} elsif ( $responseTime > $binThreshold ) {
			$numAboveBinThreshold++;	# we'll add this value to a 'more' key in the hash at the end of the script
			last;
		}

		$rangeLow = $binResponseTime;
	}

}	# end tallyResponseTime()

my $validQuery = $tickets->FromSQL( $query );
#print "VALID QUERY!\n" if $validQuery;	# debug

# compare the ticket Created and Started times to determine response time
my $totalTickets = '0';
while ( my $ticket = $tickets->Next() ) {
	my $dateTicketCreated = $ticket->CreatedObj->Get( Timezone => 'server' );
	my @dateTicketCreated = split( /-|:| /, $dateTicketCreated );
	my $timeTicketCreated = Date_to_Time( @dateTicketCreated );	# seconds since epoch
	my $dateTicketStarted = $ticket->StartedObj->Get( Timezone => 'server' );
	my @dateTicketStarted = split( /-|:| /, $dateTicketStarted );
	my $timeTicketStarted = Date_to_Time( @dateTicketStarted );	# seconds since epoch
	my $timeDiff = $timeTicketStarted - $timeTicketCreated;

	tallyResponseTime( $timeDiff );
	$totalTickets++;
}

# after all tallies, add the key/value pair for those tickets whose response time was above our bin threshold
$histogramData{ $binThreshold + 1 } = $numAboveBinThreshold || '0';	# 7201 seconds; NOTE: there may be none at this level, default to '0'

# report!
print "\n" . localtime() . "\n";
print "\nQUERY: $query\n\n";
foreach my $key ( sort { $a <=> $b } keys %histogramData ) {    # ensure a numeric sort; not ASCII-betical
	my $timeInterval = parseInterval( seconds => $key );
	if ( $key < $binThreshold + 1 ) {
		print "< ";
	} else {
		print "> ";
	}
	print $timeInterval->{'hours'} . 'h ' . $timeInterval->{'minutes'} . 'm: ' . $histogramData{ $key } . "\n";
}

print "\nTOTAL TICKETS: $totalTickets\n\n";