RtUnifiedreminder
Jump to navigation
Jump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.
#!/usr/bin/perl -w # RT3 reminder script # One big reminder report about ALL tickets that are getting to be too long since LastUpdated # Sends multipart HTML + plaintext email, so HTML capable mail readers can click links # Many pieces cannibalized from the other reminder scripts in the wiki. # You will run this script from cron as a user with perms to read the RT_SiteConfig.pm config file # Crontab would look like this to run twice a day, at 10:15am and 3:15pm ## send unified remind email twice per day #15 10 * * * /home/crystalfontz/scripts/rt-unifiedreminders #15 15 * * * /home/crystalfontz/scripts/rt-unifiedreminders ### Configuration # Location of RT3's libs -- Change this to where yours are use lib ("/usr/share/request-tracker3.8/lib", "/usr/local/share/request-tracker3.8/lib"); # list of email addresses who will receive this report my(@sendto) = qw[you@yours.com someone@somewhere.com]; # Address emails should originate from my($from) = 'RT Reminder <your@returnaddress.com>'; # maximum number of seconds since LastUpdated, beyond which the ticket will be reported my %max_untouched_ages = ("new" => 60 * 60 * 21, # 21 hrs before warn about new "open" => 60 * 60 * 45, # 45 hrs before warn about open "stalled" => 60 * 60 * 24 * 10, # 10 days before warn about stalled ); my $tickets_per_status = 15; # how many tickets should be included for each status. bigger number = longer email my $timezone_offset = 7; # how many hours difference is your timezone from GMT? US-Pacific = 7 # Queues to operate on. Default is all # my @goodqueues = qw[ThisQueue ThatQueue]; # to look only in specific queues, uncomment this line and comment the next one my @goodqueues = (); # leave like this to look in all queues # Queues to skip. Default is none. Use either @goodqueues or @badqueues, not both. my @badqueues = (); # If you have no queues to exclude, uncomment this line and comment the next one #my @badqueues = qw[Ignoreme IgnoreAnother]; # To exclude certain queues, list them here and comment line above my($debug) = 0; # nonzero will print plaintext report to STOUT instead of emailing it # The length at which lines for plaintext mail will be truncated. 78, or thereabouts looks # best for most people. Setting to 0 will stop lines being truncated. my($linelen) = 78; # has no effect on HTML mail ### Code use strict; use Carp; use MIME::Lite; use URI::Escape; my $msg = MIME::Lite->new(From => $from, To => join(',', @sendto), Subject => 'Outstanding Tickets Report', Type => 'multipart/alternative'); # Pull in the RT stuff package RT; use RT::Interface::CLI qw(CleanEnv GetCurrentUser GetMessageContent loc); CleanEnv(); # Clean our the environment RT::LoadConfig(); # Load the RT configuration RT::Init(); # Initialise RT use RT::Date; use RT::Queue; use RT::Queues; use RT::Tickets; my $max_age = 0; my $plainbody = ""; my $htmlbody = "<body>"; my $user = new RT::User($RT::SystemUser); # Define an RT User variable my $tickets = new RT::Tickets($RT::SystemUser); # Used to store Ticket search results my $date = new RT::Date($RT::SystemUser); # Define a date variable (used for comparisions) my $now = new RT::Date($RT::SystemUser); # get current time $now->SetToNow(); # Limit the ticket search to new and open only. $tickets->LimitStatus(VALUE => 'new'); $tickets->LimitStatus(VALUE => 'open'); $tickets->LimitStatus(VALUE => 'stalled'); my $searchqueue = ''; if($#goodqueues != -1) { $tickets->_OpenParen(); foreach my $queue (@goodqueues) { $tickets->LimitQueue(VALUE => $queue, OPERATOR => '='); } $tickets->_CloseParen(); $searchqueue = " AND (Queue = '". join("' OR Queue = '", @goodqueues) ."')"; } elsif($#badqueues != -1) { foreach my $queue (@badqueues) { $tickets->LimitQueue(VALUE => $queue, OPERATOR => '!='); } $searchqueue = " AND Queue != '". join("' AND Queue != '", @badqueues) ."'"; } $tickets->OrderByCols( {FIELD => 'Status', ORDER => 'ASC'}, {FIELD => 'queue', ORDER => 'ASC'}, {FIELD => 'Priority', ORDER => 'DESC'}, {FIELD => 'LastUpdated', ORDER => 'ASC'}, ); # We might not want to print the message if there are no tickets my($printmsg) = 0; my $j = 0; my $bgcolor = ''; my $last_status = ''; my $status_title = ''; my $count_by_status = 0; my $searchURL = ''; # Loop through tickets while (my $Ticket = $tickets->Next) { # Compare Dates to see if LastUpdated date is old enough to report $max_age = $max_untouched_ages{$Ticket->Status} || 0; $date->Set(Format => "ISO", Value => $Ticket->LastUpdated); if ($now->Unix - $date->Unix < $max_age) { next; } # skip it if too young $j++; if($j % 2) { $bgcolor = "#e8e8e8"; } else { $bgcolor = "#fff"; } $user->Load($Ticket->Owner); if($printmsg == 0) { # Put heading on top $plainbody .= sprintf "%5s %-7s %3s %-13s %-7s %-6s %-30s\n", "Id", "Status", "Pri", "Updated", "Queue", "Owner", "Subject"; $htmlbody .= "<table style='width: 900px; white-space: nowrap; border-collapse: collapse;'> <tr> <th>Id</th> <th>Status</th> <th>Pri</th> <th>Updated</th> <th>Queue</th> <th>Owner</th> <th>Subject</th> </tr>\n"; $printmsg = 1; } if($Ticket->Status ne $last_status) { if($count_by_status > $tickets_per_status) { ($htmlbody, $plainbody) = &see_more_link($searchURL, $count_by_status, $htmlbody, $plainbody); } $last_status = $Ticket->Status; $count_by_status = 0; # reset on every difft status $status_title = uc($last_status). " and last updated more than "; if($max_age/(60 * 60) <= 48) { $status_title .= ($max_age/(60 * 60))." hours ago"; } else { $status_title .= ($max_age/(60 * 60 * 24))." days ago"; } # get a date object that can give us the datetime cutoff for this query $date->SetToNow(); $date->AddSeconds( -($max_age + (3600 * $timezone_offset)) ); # add GMT hours offset cos timezone not implemented in RT date object $searchURL = RT->Config->Get('WebURL') . "Search/Results.html?Query=". URI::Escape::uri_escape("LastUpdated < '". $date->ISO. "' AND Status = '$last_status'$searchqueue"). "&Order=". URI::Escape::uri_escape("ASC|ASC|DESC|ASC"). "&OrderBy=". URI::Escape::uri_escape("Status|queue|Priority|LastUpdated"); $htmlbody .= " <tr style='background-color: #F5DEB3;'> <td colspan='7' style='padding: 5px; text-align: center;'><b>". "<a href=\"$searchURL\">$status_title</a></b></td></tr>\n"; $plainbody .= "\n$status_title since ". $date->ISO ."\n"; } $count_by_status++; if($count_by_status > $tickets_per_status) { next; } # Use our own date formatting routine my($updated) = &formatDate($Ticket->LastUpdatedObj->Unix); my($subject) = $Ticket->Subject ? $Ticket->Subject : "(No subject)"; my($queue) = substr($Ticket->QueueObj->Name, 0, 7); my($line) = sprintf "%5d %-7s %3d %-13s %-7s %-6s %-30s", $Ticket->Id, $Ticket->Status, $Ticket->Priority, $updated, $queue, $user->Name, $subject; # Truncate lines if required if($linelen) { $line = substr($line, 0, $linelen); } $plainbody .= $line ."\n"; $htmlbody .= " <tr style='background-color: $bgcolor;'> <td style='text-align: right; padding: 2px;'><a href='". RT->Config->Get('WebURL') . "Ticket/Display.html?id=". $Ticket->Id ."'>". $Ticket->Id ."</a></td> <td style='text-align: center; padding: 2px;'>". $Ticket->Status ."</td> <td style='text-align: right; padding: 2px;'>". $Ticket->Priority ."</td> <td style='padding: 2px;'>". $updated ."</td> <td style='padding: 2px;'>". $queue ."</td> <td style='padding: 2px;'>". $user->Name ."</td> <td style='padding: 2px;'><div style='width: 550px; overflow: hidden;'>". $subject ."</div></td> </tr>\n"; } # finish up last status group if($count_by_status > $tickets_per_status) { ($htmlbody, $plainbody) = &see_more_link($searchURL, $count_by_status, $htmlbody, $plainbody); } $htmlbody .= "</table></body>\n"; # Send the message if($printmsg) { ### Alternative #1 is the plain text: my $plain = $msg->attach(Type => 'text/plain', Data => [$plainbody]); ### Alternative #2 is the HTML-with-content: my $fancy = $msg->attach(Type => 'multipart/related'); $fancy->attach(Type => 'text/html; charset=UTF-8', # utf8 charset to avoid MIME::Lite wide character error Data => [$htmlbody]); if ($debug) { # print "====== Would email this report:\n"; print "$plainbody\n\n"; # $msg->print; # print $msg->as_string; # print $msg->body_as_string; } else { $msg->send; } } else { print "No message to print.\n\n"; } # Disconnect before we finish off $RT::Handle->Disconnect(); exit 0; sub see_more_link() { my ($searchURL, $count_by_status, $htmlbody, $plainbody) = @_; $htmlbody .= " <tr> <td colspan='7' style='padding: 7px; text-align: center;'> <a href=\"$searchURL\">See all $count_by_status getting stale in ". uc($last_status) ." status</a></td> </tr>\n"; $plainbody .= "$searchURL\n"; return ($htmlbody, $plainbody); } # Formats a date like: Thu 10-07-03 # Designed to be consice yet useful sub formatDate() { my($unixtime) = @_; my(@days) = ( "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat" ); # Return an empty string if we haven't been given a time return "" if $unixtime <= 0; my($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime($unixtime); return sprintf "%s %02d-%02d-%02d", $days[$wday], $mon+1, $mday, $year%100; }