1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
#!/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 );

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 > '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";
</code>
The output looks similar to the following:
<code>
Mon Aug 15 22:45:14 2011

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

Found 108 tickets

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

</code>

This shows my team resolves the majority of tickets in less than a day.  Very nice.

<strong>rtTicketFirstResponse.pl</strong>

The second report tells me how quickly my team is responding to new requests.  My service level for responses is 30 minutes or less (remember the old Domino's Pizza guarantee?).

<code>
#!/usr/bin/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 > '3 months 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 < $binThreshold + 1 ) {
		print "< ";
	} else {
		print "> ";
	}
	print $timeInterval->{'hours'} . 'h ' . $timeInterval->{'minutes'} . 'm: ' . $histogramData{ $key } . "\n";
}

print "\nTOTAL TICKETS: $totalTickets\n\n";