Ticket Lifetime Report

Advanced Reporting

I've been piloting RT for a few months and have been very happy with the system's operation and reporting capabilities. Recently, though, I've had the need to get some advanced reports from the system such as how well my team has been performing with respect to internal service levels. Specifically, I need to determine how long tickets "lived" from creation to resolution so I can gauge how effective the team is at completing requests. To do this, I need to find the age of resolved tickets and generate a histogram. I rolled my own script using RT's modules so that, hopefully, it's portable across RT versions.

This and other scripts can be found [here, on github](https://github.com/RyanFrantz/Request-Tracker-Reports).

rtTicketLifetime.pl

   #!/usr/bin/perl

   #
   # rtTicketLifetime.pl - query RT and generate a report on the lifetime of resolved tickets
   # Author: Ryan Frantz ryanfrantz [at] informed-llc [dot] com
   #

   use strict;
   use warnings;

   use lib "/usr/local/rt/lib";

   use RT;
   use RT::User;
   use RT::Interface::CLI qw( CleanEnv GetCurrentUser );   # I guess these aren't exported?

   use Date::Calc qw( Delta_DHMS );

   # TODO:
   # 1. add a break out of ticket lifetime by owner
   # 2. add email support
   # 3. make this available via the web interface with graphing goodness

   ## start me up!

   # set the stage...
   CleanEnv();
   RT::LoadConfig;
   RT::Init;

   my $currentUser = GetCurrentUser();
   my $tickets = RT::Tickets->new( $currentUser );
   #my $query = qq[ Created > '2011-04-01' AND Queue = 'Support Desk' AND Status = 'resolved' ];
   my $query = qq[ Created > '3 months ago' AND Queue = 'Support Desk' AND Status = 'resolved' ];

   my $validQuery = $tickets->FromSQL( $query );
   #print "VALID QUERY!\n" if $validQuery;

   my $binThreshold = '7'; # 7 days, trust me...
   my @histogramData;      # prep dat
   # initialize the bins, in case there are any that don't get incremented later
   # 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)
   foreach my $day ( 0..$binThreshold ) {
           $histogramData[ $day ] = '0';
   }
   while ( my $ticket = $tickets->Next() ) {
           #my $owner = $ticket->OwnerObj; # we're not using this yet...

           # CreatedObj is available via RT::Record
           my $dateCreated = $ticket->CreatedObj->Get( Timezone => 'server' );
           my $dateResolved = $ticket->ResolvedObj->Get( Timezone => 'server' );
           my @dateCreated = split( /-|:| /, $dateCreated );
           my @dateResolved = split( /-|:| /, $dateResolved );
           my ( $deltaDays, $deltaHours, $deltaMinutes, $deltaSeconds ) = Delta_DHMS( @dateCreated, @dateResolved );

           # increment the bins; if the value is above the bin threshold, simply lump it into a "more" bin ( $binThreshold )
           if ( $deltaDays > $binThreshold ) {
                   #print "DEBUG: $deltaDays > $binThreshold\n";
                   $histogramData[ $binThreshold ]++;
           } else {
                   #print "DEBUG: $deltaDays <= $binThreshold\n";
                   $histogramData[ $deltaDays ]++;
           }
   }

   print "\n" . localtime() . "\n";
   print "\nQuery: $query\n";
   print "\nFound " . $tickets->CountAll . " tickets\n\n";

   my $day = '1';
   foreach my $ticketsResolved ( @histogramData ) {
           if ( $day <= $binThreshold ) {
                   print $day - 1 . " < " . $day . ": " . $ticketsResolved . "\n";
           } else {
                   print $day . "+ : " . $ticketsResolved . "\n";
           }
           $day++;
   }
   print "\n";

Output

When run, the script outputs its results to the screen as follows:

Fri Jul  1 09:18:06 2011

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

Found 72 tickets

0 < 1: 58
1 < 2: 2
2 < 3: 4
3 < 4: 2
4 < 5: 1
5 < 6: 1
6 < 7: 0
8+ : 4

The output is simple but it clearly illustrates that the majority of my resolved tickets lived less than 24 hours (a very good thing!). Modify the $binThreshold and $query values to suit your own reporting needs.

TODO

This work is far from complete. I've got a few items on my TODO list:

• Add a break out of ticket lifetime by owner.
• Add email support.
• Make this available via the web interface with graphing goodness.

Warranty

This script is provided as-is with no implicit or explicit gaurantee that it will work or won't break your implementation; caveat emptor. It's all in the spirit of giving back to the community. It's free as in beer. I'm a big fan of pale ales...