List,
Per a request from [email protected], 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 [email protected] ----- Original Message ----- From: [email protected] To: "Ryan Frantz" <[email protected]> Cc: [email protected] 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 <[email protected]> wrote: > ----- Original Message ----- >> From: [email protected] >> To: "Ryan Frantz" <[email protected]>, >> [email protected] >> 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 >> <[email protected]> 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 <[email protected]> >> > Date: Fri, 1 Jul 2011 12:28:56 -0400 >> > To: <[email protected]> >> > 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 >> > [email protected] >> > -------- 2011 Training: >> > http://bestpractical.com/services/training.html >> > >> > -------- >> > 2011 Training: http://bestpractical.com/services/training.html >> > > > -------- > 2011 Training: http://bestpractical.com/services/training.html -------- 2011 Training: http://bestpractical.com/services/training.html
