List,
Per a request from sebsua@gmail.com, I’m providing a script I recently completed that generates a simple report demonstrating the response time for all tickets returned in a given query. The purpose of this report is to understand if an internal service level for response time is being met. My service level is to have all tickets addressed within 30 minutes of submission; I also want to know how far beyond that service level tickets are being addressed, hence the histogram bins ranging from 15 minutes to 2 hours. Modify it as you see fit for your purposes (i.e. the histogram bins). The script could be a little more dynamic, but it works.
– begin rtTicketFirstResponse.pl –
#!/usr/bi/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 );
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 > ‘1 month ago’ AND Queue = ‘Support Desk’ 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
iterate over the transactions, find those where Type == Status, then where status changes from ‘new’ to ‘open’ or ‘new’ to ‘resolved’
and compare the date the transaction was created against the Created date for the ticket
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
my $totalTickets = ‘0’;
while ( my $ticket = $tickets->Next() ) {
my $dateTicketCreated = $ticket->CreatedObj->Get( Timezone => ‘server’ );
my $transactions = RT::Transactions->new( $currentUser );
$transactions->LimitToTicket( $ticket->id );
while ( my $transaction = $transactions->Next() ) {
next unless $transaction->Type eq 'Status';
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
my $dateTransactionCreated = $transaction->CreatedObj->Get( Timezone => 'server' );
my @dateTicketCreated = split( /-|:| /, $dateTicketCreated );
my @dateTransactionCreated = split( /-|:| /, $dateTransactionCreated );
my $timeTicketCreated = Date_to_Time( @dateTicketCreated ); # seconds since epoch
my $timeTransactionCreated = Date_to_Time( @dateTransactionCreated ); # seconds since epoch
my $timeDiff = $timeTransactionCreated - $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; # 7201 seconds
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 < 7201 ) {
print "< ";
} else {
print "> ";
}
print $timeInterval->{‘hours’} . 'h ’ . $timeInterval->{‘minutes’} . 'm: ’ . $histogramData{ $key } . “\n”;
}
print “\nTOTAL TICKETS: $totalTickets\n\n”;
– end rtTicketFirstResponse.pl –
Ryan Frantz
Technical Services Director
InforMed, LLC
410-972-2025 x2131
ryanfrantz@informed-llc.comFrom: sebsua@gmail.com
To: “Ryan Frantz” ryanfrantz@informed-llc.com
Cc: rt-users@lists.bestpractical.com
Sent: Friday, July 1, 2011 3:55:32 PM
Subject: Re: [rt-users] Ticket Lifetime Report
Hi Ryan, great to hear you need the same report, cause I’m very
limited in RT hacking.
I’ll be waiting for that script when you get it done.
Thx,
Seb.-