[rt-users] Ticket First Response Report (was Re: Ticket Lifetime Report)
Ryan Frantz
ryanfrantz at informed-llc.com
Fri Jul 15 16:24:57 EDT 2011
List,
Per a request from sebsua at 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 at informed-llc.com
----- Original Message -----
From: sebsua at gmail.com
To: "Ryan Frantz" <ryanfrantz at informed-llc.com>
Cc: rt-users at 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.-
On Fri, Jul 1, 2011 at 4:43 PM, Ryan Frantz <ryanfrantz at informed-llc.com> wrote:
> ----- Original Message -----
>> From: sebsua at gmail.com
>> To: "Ryan Frantz" <ryanfrantz at informed-llc.com>, rt-users at lists.bestpractical.com
>> Sent: Friday, July 1, 2011 3:16:20 PM
>> Subject: Re: [rt-users] Ticket Lifetime Report
>> Hi Ryan,
>> We were looking for this type of report, but with some difference...
>> Instead of calculate "created to resolved time", we are looking for a
>> "created to open time" approach. This is because our SLA's are based
>> on time to reply instead of time to resolution.
>>
>> Can you please point me in the right direction to modify the script so
>> to reflect this?
>
> Seb,
>
> I've only just started hacking RT, but I believe you'll need to iterate over the transactions for a given ticket to find a transaction type of 'Status' and check it's OldValue for 'new' and a NewValue of 'open', then read the Created field (I'm assuming it returns an RT::Date object).
>
> Interestingly, I am looking to report on the same service level so I may have something written for this as well. I'll race you to the finish...
>
> Ryan
>
>
>>
>> Many Thanks,
>> Seb.-
>>
>> On Fri, Jul 1, 2011 at 4:09 PM, John Alberts
>> <John.Alberts at exlibrisgroup.com> wrote:
>> > Very nice. Thank you.
>> >
>> > ----------
>> >
>> > John Alberts
>> >
>> > Cloud Optimization Engineer
>> >
>> > Ex Libris (USA) Inc.
>> > 1350 E. Touhy Ave. Suite 200 East
>> > Des Plaines, IL 60018
>> > Phone: 1-219-979-6560
>> >
>> >
>> >
>> > Follow Ex Libris on Twitter: @exlibrisgroup
>> >
>> > From: Ryan Frantz <ryanfrantz at informed-llc.com>
>> > Date: Fri, 1 Jul 2011 12:28:56 -0400
>> > To: <rt-users at lists.bestpractical.com>
>> > Subject: [rt-users] Ticket Lifetime Report
>> >
>> > Fellow Users,
>> > I've written a short script that will generate a simple report
>> > illustrating
>> > the lifetime of resolved tickets. It's useful for determining if
>> > your
>> > support desk is meeting service levels (i.e. tickets resolved in <=
>> > 7 days).
>> > I plan on taking it further in the near future including adding
>> > email
>> > support and integrating it into the web interface. For now, you can
>> > see/get
>> > the code at
>> > http://requesttracker.wikia.com/wiki/Ticket_Lifetime_Report.
>> > Ryan Frantz
>> > Technical Services Director
>> > InforMed, LLC
>> > 410-972-2025 x2131
>> > ryanfrantz at informed-llc.com
>> > -------- 2011 Training:
>> > http://bestpractical.com/services/training.html
>> >
>> > --------
>> > 2011 Training: http://bestpractical.com/services/training.html
>> >
>
> --------
> 2011 Training: http://bestpractical.com/services/training.html
More information about the rt-users
mailing list