RyanFrantz

Request Tracker: Hacking Reports

Request Tracker (RT) is a solid ticketing system and I've been piloting it for a few months.  Now, I'm getting ready to deploy it in production.  RT is extremely flexible in that it is highly customizable and extensible.  There are a number of ways to access ticket content including its REST API and CLI tools that come with the implementation.  In addition, one can code against the system by pulling in RT's Perl modules.  I need some reports that aren't available in RT core so I've hacked a couple together to meet my needs.  I present them here in the hopes that they may prove useful to others.

rtTicketLifetime.pl

The first report demonstrates how long tickets live in my Support Desk queue.  I have a service level that states all support requests must be addressed and resolved within 7 days.  This report tells me how effective my team is.  The output can be used to generate a histogram, if need be.
NOTE: I posted about this before but I referenced another site; I'm posting the code here as well.

  1 #!/usr/bin/perl
  2 
  3 #
  4 # rtTicketLifetime.pl - query RT and generate a report on the lifetime of resolved tickets
  5 # Author: Ryan Frantz ryanfrantz [at] informed-llc [dot] com
  6 #
  7 
  8 use strict;
  9 use warnings;
 10 
 11 use lib "/usr/local/rt/lib";
 12 
 13 use RT;
 14 use RT::User;
 15 use RT::Interface::CLI qw( CleanEnv GetCurrentUser );
 16 
 17 use Date::Calc qw( Delta_DHMS );
 18 
 19 # TODO:
 20 # 1. add a break out of ticket lifetime by owner
 21 # 2. add email support
 22 # 3. make this available via the web interface with graphing goodness
 23 
 24 ## start me up!
 25 
 26 # set the stage...
 27 CleanEnv();
 28 RT::LoadConfig;
 29 RT::Init;
 30 
 31 my $currentUser = GetCurrentUser();
 32 my $tickets = RT::Tickets->new( $currentUser );
 33 my $query = qq[ Created > '3 months ago' AND Queue = 'Support Desk' AND Status = 'resolved' ];
 34 
 35 my $validQuery = $tickets->FromSQL( $query );
 36 #print "VALID QUERY!\n" if $validQuery;
 37 
 38 my $binThreshold = '7';	# 7 days, trust me...
 39 my @histogramData;	# prep dat
 40 # initialize the bins, in case there are any that don't get incremented later
 41 # we'll use the array's indices to define the time period in which the ticket lived (i.e. $histogramData[0] is for tickets resolved in < 1 day)
 42 foreach my $day ( 0..$binThreshold ) {
 43 	$histogramData[ $day ] = '0';
 44 }
 45 
 46 while ( my $ticket = $tickets->Next() ) {
 47 	#my $owner = $ticket->OwnerObj;	# we're not using this yet...
 48 
 49 	# CreatedObj is available via RT::Record
 50 	my $dateCreated = $ticket->CreatedObj->Get( Timezone => 'server' );
 51 	my $dateResolved = $ticket->ResolvedObj->Get( Timezone => 'server' );
 52 	my @dateCreated = split( /-|:| /, $dateCreated );
 53 	my @dateResolved = split( /-|:| /, $dateResolved );
 54 	my ( $deltaDays, $deltaHours, $deltaMinutes, $deltaSeconds ) = Delta_DHMS( @dateCreated, @dateResolved );
 55 
 56 	# increment the bins; if the value is above the bin threshold, simply lump it into a "more" bin ( $binThreshold )
 57 	if ( $deltaDays > $binThreshold ) {
 58 		#print "DEBUG: $deltaDays > $binThreshold\n";
 59 		$histogramData[ $binThreshold ]++;
 60 	} else {
 61 		#print "DEBUG: $deltaDays <= $binThreshold\n";
 62 		$histogramData[ $deltaDays ]++;
 63 	}
 64 }
 65 
 66 print "\n" . localtime() . "\n";
 67 print "\nQuery: $query\n";
 68 print "\nFound " . $tickets->CountAll . " tickets\n\n";
 69 
 70 my $day = '1';
 71 foreach my $ticketsResolved ( @histogramData ) {
 72 	if ( $day <= $binThreshold ) {
 73 		print $day - 1 . " < " . $day . ": " . $ticketsResolved . "\n";
 74 	} else {
 75 		print $day . "+ : " . $ticketsResolved . "\n";
 76 	}
 77 	$day++;
 78 }
 79 print "\n";
 80 </code>
 81 The output looks similar to the following:
 82 <code>
 83 Mon Aug 15 22:45:14 2011
 84 
 85 Query:  Created > '3 months ago' AND Queue = 'Support Desk' AND Status = 'resolved'
 86 
 87 Found 108 tickets
 88 
 89 0 < 1: 73
 90 1 < 2: 5
 91 2 < 3: 9
 92 3 < 4: 6
 93 4 < 5: 4
 94 5 < 6: 2
 95 6 < 7: 0
 96 8+ : 9
 97 
 98 </code>
 99 
100 This shows my team resolves the majority of tickets in less than a day.  Very nice.
101 
102 <strong>rtTicketFirstResponse.pl</strong>
103 
104 The second report tells me how quickly my team is responding to new requests.  My service level for responses is 30 minutes or less (remember the old Domino's Pizza guarantee?).
105 
106 <code>
107 #!/usr/bin/perl
108 
109 #
110 # rtTicketFirstResponse.pl - query RT and generate a report on how long it took to respond to a request
111 # Author: Ryan Frantz ryanfrantz [at] informed-llc [dot] com
112 #
113 
114 use warnings;
115 use strict;
116 
117 use lib "/usr/local/rt/lib/";
118 
119 use RT;
120 use RT::User;
121 use RT::Interface::CLI qw( CleanEnv GetCurrentUser );
122 
123 use Date::Calc qw( Date_to_Time );
124 use Time::Interval;
125 
126 ## start me up!
127 
128 # set the stage...
129 CleanEnv();
130 RT::LoadConfig;
131 RT::Init;
132 
133 my $currentUser = GetCurrentUser();
134 my $tickets = RT::Tickets->new( $currentUser );
135 my $query = qq[ Created > '3 months ago' AND Queue = 'Support Desk' AND Status = 'resolved' ];
136 
137 my $binThreshold = '7200';	# 2 hours, in seconds
138 # define the response times for each bin; in seconds
139 my %histogramData = (
140 	'900'	=>	'0',	# 15min
141 	'1800'	=>	'0',	# 30min
142 	'2700'	=>	'0',	# 45min
143 	'3600'	=>	'0',	# 1hour
144 	'4500'	=>	'0',	# 1hour15min
145 	'5400'	=>	'0',	# 1hour30min
146 	'6300'	=>	'0',	# 1hour45min
147 	$binThreshold	=>	'0',	# 2hours
148 	#'more'	=>	'0'	# $binThreshold + 1; we'll add this key in at the end
149 );
150 
151 my $numAboveBinThreshold;
152 sub tallyResponseTime {
153 
154 	my $responseTime = shift;
155 	#print "\nTEST VALUE: $responseTime\n";	# debug
156 	my $rangeLow = '0';
157 
158 	foreach my $binResponseTime ( sort { $a <=> $b } keys %histogramData ) {	# ensure a numeric sort; not ASCII-betical
159 		if ( $responseTime >= $rangeLow and $responseTime < $binResponseTime ) {
160 			$histogramData{ $binResponseTime }++;
161 			last;   # no need to continue
162 		} elsif ( $responseTime > $binThreshold ) {
163 			$numAboveBinThreshold++;	# we'll add this value to a 'more' key in the hash at the end of the script
164 			last;
165 		}
166 
167 		$rangeLow = $binResponseTime;
168 	}
169 
170 }	# end tallyResponseTime()
171 
172 my $validQuery = $tickets->FromSQL( $query );
173 #print "VALID QUERY!\n" if $validQuery;	# debug
174 
175 # iterate over the transactions, find those where Type == Status, then where status changes from 'new' to 'open' or 'new' to 'resolved'
176 # and compare the date the transaction was created against the Created date for the ticket
177 # NOTE: I've seen tickets move from 'new' to 'resolved' because techs log the ticket after resolving the issue (i.e. password resets); we need these too
178 my $totalTickets = '0';
179 while ( my $ticket = $tickets->Next() ) {
180 	my $dateTicketCreated = $ticket->CreatedObj->Get( Timezone => 'server' );
181 	my $transactions = RT::Transactions->new( $currentUser );
182 	$transactions->LimitToTicket( $ticket->id );
183 
184 	while ( my $transaction = $transactions->Next() ) {
185 		next unless $transaction->Type eq 'Status';
186 		next unless ( ($transaction->OldValue eq 'new' and $transaction->NewValue eq 'open') or ($transaction->OldValue eq 'new' and $transaction->NewValue eq 'resolved') );	# only new -> open transactions
187 
188 		my $dateTransactionCreated = $transaction->CreatedObj->Get( Timezone => 'server' );
189 	        my @dateTicketCreated = split( /-|:| /, $dateTicketCreated );
190 		my @dateTransactionCreated = split( /-|:| /, $dateTransactionCreated );
191 		my $timeTicketCreated = Date_to_Time( @dateTicketCreated );	# seconds since epoch
192 		my $timeTransactionCreated = Date_to_Time( @dateTransactionCreated );	# seconds since epoch
193 		my $timeDiff = $timeTransactionCreated - $timeTicketCreated;
194 
195 		tallyResponseTime( $timeDiff );
196 		$totalTickets++;
197 	}
198 }
199 
200 # after all tallies, add the key/value pair for those tickets whose response time was above our bin threshold
201 $histogramData{ $binThreshold + 1 } = $numAboveBinThreshold;	# 7201 seconds
202 
203 # report!
204 print "\n" . localtime() . "\n";
205 print "\nQUERY: $query\n\n";
206 foreach my $key ( sort { $a <=> $b } keys %histogramData ) {    # ensure a numeric sort; not ASCII-betical
207 	my $timeInterval = parseInterval( seconds => $key );
208 	if ( $key < $binThreshold + 1 ) {
209 		print "< ";
210 	} else {
211 		print "> ";
212 	}
213 	print $timeInterval->{'hours'} . 'h ' . $timeInterval->{'minutes'} . 'm: ' . $histogramData{ $key } . "\n";
214 }
215 
216 print "\nTOTAL TICKETS: $totalTickets\n\n";

The output looks similar to the following:

Mon Aug 15 22:46:32 2011

QUERY: Created > '3 months ago' AND Queue = 'Support Desk' AND Status = 'resolved'

< 0h 15m: 43
< 0h 30m: 2
< 0h 45m: 6
< 1h 0m: 4
< 1h 15m: 5
< 1h 30m: 2
< 1h 45m: 3
< 2h 0m: 5
> 2h 0m: 35

TOTAL TICKETS: 105

In this case, my guys respond to most tickets in 15 minutes or less, or 2+ hours. Generally, many of those late responses are related to tickets received close to the end of the business day. This script doesn't calculate business days, so the output may appear artificially inflated (i.e. a ticket could come in Friday at 4:59PM and be addressed at 8:31AM the next Monday but would still show here at 2+ hours instead of 2 minutes).