#!/opt/local/bin/perl -w ####################### RT Email Notification Script #################### #### #### Original author: Daniely Yoav / Qball Technologies Ltd. #### Email: yoavd@qballtech.net #### Date: 05/06/05 #### #### Heavily modified by: Gene LeDuc, San Diego State University #### Email: gleduc {_at_} mail(dot)sdsu(dot)edu(endofaddress)zzz #### Date: 2007/05/24 #### #### Purpose: Send email tickler on all open/new/stalled tickets in RT that #### are within NagDays of (or are past) their due date. Ticklers are #### only sent if: #### 1) There is a due date #### 2) There is a custom field called NagDays that has a value #### 3) NagDays + now >= due date #### If a ticket meets the criteria, an e-mail reminder is sent to #### the ticket owner and the AdminCcs. The message for each person #### is generated separately and contains information on the tickets #### across all queues that meet the criteria and that the user should #### get. No one should get a tickler for a ticket for which they are #### are not the owner or an AdminCc. #### #### Here's how we use this: #### If we want ticklers generated for a queue, we add the NagDays CF #### to the queue. If this field is not present and populated (we use #### an On Create scrip to do this), the ticket is ignored. #### There must be a value in the ticket's Due field, so we have a #### default value in the queue's configuration. #### Since we only work Mon-Fri, we use business days instead of #### calendar days. We use Jesse's Business::Hours package from CPAN #### to eliminate non-work days from our calculations. The original script #### used "Created" and then counted forward from there to determine #### whether to "tickle" the users. I chose instead to start with "Due" #### and then count backwards from there to decide whether I should nag #### the users. This lets the Due date get modified (extended 2 weeks #### for example) without having to change anything else in the ticket, #### and still send a tickler NagDays before it's due. Also, we #### decided that it was more useful for us to ping the users as a #### ticket gets closer to its Due date rather than simply as a #### function of aging. To do the backwards counting using business #### days, we needed to add a sub_seconds() method to the #### Business::Hours package (the distributed package only counts #### forward). The code for this new method is at the end of the #### script. The new code has been submitted to the Business::Hours #### maintainers and authors twice but I haven't heard anything back, #### so I assume that it has not been included in the CPAN distribution. #### #### Note - You can set $debug = 1 (below) to invoke this script without #### actually sending any e-mail. This is a nice way to determine what the #### resulting e-mails will look like and which tickets will be included #### without filling up mailboxes with test messages. #### #### Note - The user running this script must have write permissions on #### the rt.log file because we log that the ticklers are being sent. #### Even if we don't log anything, RT::Init initializes the RT::Logger #### class which needs to be able to write to the log file. #### #### Usage: Invoke via cron every working morning at 7:45 #### 45 7 * * 1-5 /path/to/script/rt-tickler.pl #### Setting $debug to 1 causes any output that would have been sent to #### sendmail or the log to get sent to STDOUT instead. It also generates #### some other intermdiate informational messages as well. Set $debug to #### 0 for production use. #my $debug = 0; # production use, generates e-mail my $debug = 1; # use for testing, doesn't send any e-mail ### External libraries ### use strict; use DateTime; use Business::Hours; ### Modify the next line to fit your installation use lib ("/opt/local/software/rt-3.6.3/lib"); package RT; use RT::Interface::CLI qw(CleanEnv GetCurrentUser GetMessageContent loc); use RT::Date; use RT::Queue; use RT::Queues; use RT::Tickets; ################## Init ################## # Clean our environment CleanEnv(); # Load the RT configuration RT::LoadConfig(); RT::Init(); # Set config variables my $from_address = $RT::CorrespondAddress; #From: address used in reports my $rt_url = $RT::WebURL; my $sendmail = "$RT::SendmailPath $RT::SendmailArguments"; ################## Variables Init ################## my $User = new RT::User($RT::SystemUser); # Define an RT User variable my $date = new RT::Date($RT::SystemUser); # Date variable used for conversions my $tickets = new RT::Tickets($RT::SystemUser); # Used to store Ticket search results my $DueDate = new RT::Date($RT::SystemUser); my $NagStart = new RT::Date($RT::SystemUser); my $now = new RT::Date($RT::SystemUser); # get current time $now->SetToNow(); my %staff; # Who gets pinged my $Ticket; my %TicketPile; # Stack of tickets that get included in ticklers my $address; my $report; # Used for output my $subject; # Used as subject line my $YELL; # Used for OVERDUE tickets my $hours = Business::Hours->new(); ### Specify our normal working hours, 08:00 to 16:30 Mon-Fri my $start = '08:00'; # Our normal business hours are from 08:00 to 16:30 my $end = '16:30'; $hours->business_hours( 0 => { Name => 'Sunday', Start => undef, End => undef }, 1 => { Name => 'Monday', Start => $start, End => $end }, 2 => { Name => 'Tuesday', Start => $start, End => $end }, 3 => { Name => 'Wednesday', Start => $start, End => $end }, 4 => { Name => 'Thursday', Start => $start, End => $end }, 5 => { Name => 'Friday', Start => $start, End => $end }, 6 => { Name => 'Saturday', Start => undef, End => undef } ); ### compute seconds per day from start and end times my ($hr_s, $min_s) = split /:/,$start; my ($hr_e, $min_e) = split /:/,$end; my $secondsperday = (($hr_e - $hr_s) * 60 + $min_e - $min_s) * 60; ################## Main Program ################## # Limit the ticket search to new, open, and stalled tickets. $tickets->LimitStatus(VALUE => 'new'); $tickets->LimitStatus(VALUE => 'open'); $tickets->LimitStatus(VALUE => 'stalled'); # Loop through new/open/stalled tickets while ($Ticket = $tickets->Next) { # Do we do this ticket? $User->Load($Ticket->Owner); my $NagDays = $Ticket->FirstCustomFieldValue('NagDays'); next unless defined $NagDays; # Only nag if NagDays is set in ticket my $NagSeconds = $NagDays * $secondsperday; ### Get due date and subtratct NagSeconds to determine when we start ### nagging. next unless defined $Ticket->Due; # Can't nag if there's no due date $DueDate->Set(Format => "ISO", Value => $Ticket->Due); $NagStart->Set(Format => "Unix", Value => $hours->sub_seconds($DueDate->Unix, $NagSeconds)); if ($debug) { print $Ticket->Id, ": "; print "Due ", $DueDate->AsString, "\n"; print " NagDays: $NagDays\n"; print " NagStart: " , $NagStart->AsString, "\n"; print " Nag? ", ($now->Unix >= $NagStart->Unix ? "Yes" : "No"), "\n\n"; } next if $now->Unix < $NagStart->Unix; # Don't nag if it's too soon ### Send to ticket owner, ticket & queue adminccs my @emails = ($User->EmailAddress, split(/,/, $Ticket->AdminCcAddresses), split(/,/, $Ticket->QueueObj->AdminCcAddresses)); foreach my $address ( @emails ) { $address =~ s/(^\s+|\s+$)//g; $staff{$address}{$Ticket->Id} = 'x'; $staff{$address}{'late'}++ if $DueDate->Unix < $now->Unix; print STDERR sprintf("Ticket: %u, To: %s\n", $Ticket->Id, $address) if $debug; } $TicketPile{$Ticket->Id} = $Ticket; my $logentry = ''; foreach my $address ( @emails ) { $logentry .= $logentry ? ", " : ""; $logentry .= $address; } $logentry = sprintf("Tickler for ticket #%u (%s) sent to %s", $Ticket->Id, $Ticket->Subject, $logentry); $RT::Logger->info($logentry) unless $debug; print STDERR sprintf("Log entry: %s\n", $logentry) if $debug; } foreach $address (keys %staff) { # next if ($address !~ /gleduc\@mail.sdsu.edu/i); # Testing only $report = "This is a reminder that the following ticket"; if (scalar keys %{$staff{$address}} == 1) { $report .= " is approaching its due date.\n\n"; } elsif ((scalar keys %{$staff{$address}} == 2) and $staff{$address}{'late'}) { $report .= " is overdue.\n\n"; } elsif ($staff{$address}{'late'}) { $report .= "s are approaching or past their due dates.\n\n"; } else { $report .= "s are approaching their due dates.\n\n"; } foreach my $number (sort keys %{$staff{$address}}) { next if $number eq 'late'; # not really a ticket number $Ticket = $TicketPile{$number}; $date->Set(Format => 'ISO', Value => $Ticket->Due); $YELL = $date->Unix < $now->Unix ? ">>> OVERDUE: " : " Due: "; my $line = $Ticket->QueueObj->Name . " / " . $Ticket->Id; $line .= ": " . $Ticket->Subject . "\n"; $line .= " Requester: " . $Ticket->CreatorObj->RealName . "\n"; $line .= $YELL . $date->AsString . "\n"; $line .= " URL: " . $rt_url . "Ticket/Display.html?id=" . $Ticket->id . "\n\n"; $report .= $line; } # Generate a report $from_address = 'RT-Tickler '; send_report(($address)); } # Close RT Handle $RT::Handle->Disconnect(); exit 0; # This procedure sends report by mail to the appropriate people # parameter 1 - array of email addresses # Global variables refered to: # $subject - Subject line # $report - Message content # $from_address - address to send from sub send_report { my @tos = @_; my $addr; my $subject = "RT Reminder for " . substr($now->AsString, 0, 10) . "," . substr($now->AsString, 19); foreach $addr (@tos) { next if (length($addr) == 0); my $msg = "From: $from_address\n"; $msg .= "To: $addr\n"; $msg .= "Subject: $subject\n\n"; $msg .= $report; if ($debug) { print "====== Would call '$sendmail' with this input:\n"; print "$msg\n\n"; } else { open(SENDMAIL, "|$sendmail") || die "Error sending mail: $!"; print SENDMAIL $msg; close(SENDMAIL); } } } ### Need to add this to Business::Hours so I can count backwards from due date { package Business::Hours; sub sub_seconds { ### This method is hacked version of add_seconds(), written by Gene LeDuc my $self = shift; my $start = shift; my $seconds = shift; # the maximum time after which we stop searching for business hours my $MAXTIME = (30 * 24 * 60 * 60); # 30 days my $first; my $period = (24 * 60 * 60); my $begin = $start - $period; my $hours = new Set::IntSpan; while ($hours->empty or $self->between($hours->first, $start) <= $seconds) { if ($begin <= $start - $MAXTIME) { return -1; } $hours = $self->for_timespan(Start => $begin, End => $start); $begin -= $period; } my @elements = reverse elements $hours; $first = $elements[$seconds]; return $first; } 1; #this line is important and will help the module return a true value }