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@informed-llc.com

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: @exlibrisgrouphttp://twitter.com/ExLibrisGroupFrom: Ryan Frantz <ryanfrantz@informed-llc.commailto:ryanfrantz@informed-llc.com>
Date: Fri, 1 Jul 2011 12:28:56 -0400
To: <rt-users@lists.bestpractical.commailto:rt-users@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@informed-llc.commailto:ryanfrantz@informed-llc.com
-------- 2011 Training: http://bestpractical.com/services/training.html

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?

Many Thanks,
Seb.-On Fri, Jul 1, 2011 at 4:09 PM, John Alberts John.Alberts@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@informed-llc.com
Date: Fri, 1 Jul 2011 12:28:56 -0400
To: rt-users@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@informed-llc.com
-------- 2011 Training: http://bestpractical.com/services/training.html


2011 Training: http://bestpractical.com/services/training.html

From: sebsua@gmail.com
To: “Ryan Frantz” ryanfrantz@informed-llc.com, rt-users@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

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@informed-llc.com wrote:

----- Original Message -----

From: sebsua@gmail.com
To: “Ryan Frantz” ryanfrantz@informed-llc.com, rt-users@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@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@informed-llc.com
Date: Fri, 1 Jul 2011 12:28:56 -0400
To: rt-users@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@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

The RT guys also work on something similar.
Have a look at the 4.2/date-time-improvements-in-charts branch at github:
https://github.com/bestpractical/rt/commits/4.2%2Fdate-time-improvements-in-charts

-ChrisAm 01.07.2011 18:28, schrieb Ryan Frantz:

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@informed-llc.com


2011 Training: http://bestpractical.com/services/training.html

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.-