RtUnifiedreminder

From Request Tracker Wiki
Jump to: navigation, search

#!/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; <code><pre> $date-&gt;Set(Format =&gt; "ISO", Value =&gt; $Ticket-&gt;LastUpdated); if ($now-&gt;Unix - $date-&gt;Unix &lt; $max_age) { next; } # skip it if too young $j++; if($j % 2) { $bgcolor = "#e8e8e8"; } else { $bgcolor = "#fff"; } $user-&gt;Load($Ticket-&gt;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 .= "&lt;table style='width: 900px; white-space: nowrap; border-collapse: collapse;'&gt; &lt;tr&gt; &lt;th&gt;Id&lt;/th&gt; &lt;th&gt;Status&lt;/th&gt; &lt;th&gt;Pri&lt;/th&gt; &lt;th&gt;Updated&lt;/th&gt; &lt;th&gt;Queue&lt;/th&gt; &lt;th&gt;Owner&lt;/th&gt; &lt;th&gt;Subject&lt;/th&gt; &lt;/tr&gt;\n"; $printmsg = 1; } if($Ticket-&gt;Status ne $last_status) { if($count_by_status &gt; $tickets_per_status) { ($htmlbody, $plainbody) = &amp;see_more_link($searchURL, $count_by_status, $htmlbody, $plainbody); } $last_status = $Ticket-&gt;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) &lt;= 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-&gt;SetToNow(); $date-&gt;AddSeconds( -($max_age + (3600 * $timezone_offset)) ); # add GMT hours offset cos timezone not implemented in RT date object $searchURL = RT-&gt;Config-&gt;Get('WebURL') . "Search/Results.html?Query=". URI::Escape::uri_escape("LastUpdated &lt; '". $date-&gt;ISO. "' AND Status = '$last_status'$searchqueue"). "&amp;amp;Order=". URI::Escape::uri_escape("ASC|ASC|DESC|ASC"). "&amp;amp;OrderBy=". URI::Escape::uri_escape("Status|queue|Priority|LastUpdated"); $htmlbody .= " &lt;tr style='background-color: #F5DEB3;'&gt; &lt;td colspan='7' style='padding: 5px; text-align: center;'&gt;&lt;b&gt;". "&lt;a href=\"$searchURL\"&gt;$status_title&lt;/a&gt;&lt;/b&gt;&lt;/td&gt;&lt;/tr&gt;\n"; $plainbody .= "\n$status_title since ". $date-&gt;ISO ."\n"; } $count_by_status++; if($count_by_status &gt; $tickets_per_status) { next; } # Use our own date formatting routine my($updated) = &amp;formatDate($Ticket-&gt;LastUpdatedObj-&gt;Unix); my($subject) = $Ticket-&gt;Subject ? $Ticket-&gt;Subject : "(No subject)"; my($queue) = substr($Ticket-&gt;QueueObj-&gt;Name, 0, 7); my($line) = sprintf "%5d %-7s %3d %-13s %-7s %-6s %-30s", $Ticket-&gt;Id, $Ticket-&gt;Status, $Ticket-&gt;Priority, $updated, $queue, $user-&gt;Name, $subject; # Truncate lines if required if($linelen) { $line = substr($line, 0, $linelen); } $plainbody .= $line ."\n"; $htmlbody .= " &lt;tr style='background-color: $bgcolor;'&gt; &lt;td style='text-align: right; padding: 2px;'&gt;&lt;a href='". RT-&gt;Config-&gt;Get('WebURL') . "Ticket/Display.html?id=". $Ticket-&gt;Id ."'&gt;". $Ticket-&gt;Id ."&lt;/a&gt;&lt;/td&gt; &lt;td style='text-align: center; padding: 2px;'&gt;". $Ticket-&gt;Status ."&lt;/td&gt; &lt;td style='text-align: right; padding: 2px;'&gt;". $Ticket-&gt;Priority ."&lt;/td&gt; &lt;td style='padding: 2px;'&gt;". $updated ."&lt;/td&gt; &lt;td style='padding: 2px;'&gt;". $queue ."&lt;/td&gt; &lt;td style='padding: 2px;'&gt;". $user-&gt;Name ."&lt;/td&gt; &lt;td style='padding: 2px;'&gt;&lt;div style='width: 550px; overflow: hidden;'&gt;". $subject ."&lt;/div&gt;&lt;/td&gt; &lt;/tr&gt;\n"; </pre></code> } # 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]); <code><pre> ### Alternative #2 is the HTML-with-content: my $fancy = $msg-&gt;attach(Type =&gt; 'multipart/related'); $fancy-&gt;attach(Type =&gt; 'text/html; charset=UTF-8', # utf8 charset to avoid MIME::Lite wide character error Data =&gt; [$htmlbody]); if ($debug) { # print "====== Would email this report:\n"; print "$plainbody\n\n"; # $msg-&gt;print; # print $msg-&gt;as_string; # print $msg-&gt;body_as_string; } else { $msg-&gt;send; } </pre></code> } 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; }