[% META title = "Ticket SLA Reporting" %]

Email the stats and reminders

We want to pick up on tickets that aren't moving, and know what our SLA is for the current month thus far, what it is for our currently open tickets (so we can work to ensure the current month is met), and a comparison to last month. 


#!/usr/bin/perl
use strict;
use warnings;
use MIME::Lite;
my $options = { newidlehours => 4 * 24, openidlehours => 5 * 24, stalledidlehours => 20 * 24, debug => 0, emailfromaddr => 'Request Tracker <rt@example.com>', emailtoaddr => ['James Bromberger <james.bromberger@example.com>']};

##########################################################################
sub debug {
  my ($level, $message) = @_;
  return unless $level <= $options->{debug};
  printf "%s: %s\n", (caller(1))[3]||"main", $message;
}

use strict;
use lib '/usr/share/request-tracker3.8/lib/', '/usr/local/share/request-tracker3.8/plugins/RT-Authen-ExternalAuth/lib/', '/usr/local/share/request-tracker3.8/plugins/RT-Extension-ActivityReports/lib/', '/usr/local/share/request-tracker3.8/plugins/RTx-Calendar/lib/';
use RT::Interface::CLI qw(CleanEnv GetCurrentUser GetMessageContent loc);
use Getopt::Long;
GetOptions($options, "debug=i", "newidlehours=i", "openidlehours=i", "stalledidlehours=i", "queue=s@", "emailfromaddr=s", "emailtoaddr=s@", "help");
help() if defined $options->{help};
die "No queue specified" unless defined $options->{queue};
die "No destination for the email" unless defined $options->{emailtoaddr};
# Clean our the environment
CleanEnv();
# Load the RT configuration
RT::LoadConfig();
# Initialise RT
RT::Init();
# Load rest of the RT stuff (seems to go after initialization)
use RT::Date;
use RT::Queue;
use RT::Tickets;
#use RT::Action::SendEmail;



sub help {
print <<FOO;
$0 [ --queue <name> [ --queue <name2> ... ]]
     [ --newidlehours <i> ] [ --openidlehours <i> ] [ --stalledidlehours <i> ]
     [ --emailfromaddr <address> ] [ --emailtoaddr <address> ]
     [ --help ]
FOO
  exit;
}


sub find_idle_tickets_for_queue {
  my $queue = shift;
  my $state = shift;
  my $idle_results;

  debug(3, "Running idle search for queue $queue");
  my $date_ticket_rt = new RT::Date($RT::SystemUser);
  my $date_now_rt= new RT::Date($RT::SystemUser);
  $date_now_rt->SetToNow();

  foreach my $state qw(new open stalled) {
    debug(5, "  Running idle search for queue $queue for state $state");
    my $tickets = RT::Tickets->new($RT::SystemUser);
    $tickets->FromSQL(
      'Type = "ticket" AND '.
      'Status = "'. $state.'" AND '.
      'Queue = "'. $queue . '" AND '.
      'Updated < "' . $options->{$state . "idlehours"} . ' hours ago"'
      );
    $tickets->OrderBy(FIELD => 'Due', ORDER => 'ASC');
    $idle_results->{$state}{count} = 0;
    while (my $ticket = $tickets->Next) {
      $idle_results->{$state}{count}++;
      my $Requestor = $ticket->Requestors->UserMembersObj(Recursively => 1)->Next;
      debug(5, sprintf "Name: %s. Telephone: %s. Mobile: %s", $Requestor->RealName, $Requestor->MobilePhone, $Requestor->WorkPhone || "");
      $date_ticket_rt->Set(Format => "ISO",Value => $ticket->Created);
      my $created_relative_days = ($date_now_rt->Unix - $date_ticket_rt->Unix) / (3600 * 24);
      debug(2, sprintf "Ticket was created %d days ago", $created_relative_days);
      $idle_results->{$state}{summary}.= sprintf "<TR><TD><SPAN CLASS=\"%s\">$state</SPAN></TD><TD ALIGN=\"CENTER\"><A HREF=\"http://rt.example.com/Ticket/Display.html?id=%d\">%d</A></TD><TD>%s</TD><TD ALIGN=\"center\"><SMALL>%.1f</SMALL></TD><TD><SMALL>%s %s</SMALL></TD></TR>\n",
       $ticket->FirstCustomFieldValue("Severity") || "",
       $ticket->Id,
       $ticket->Id,
       length($ticket->Subject) > 30 ? substr($ticket->Subject, 0, 27) . "...": $ticket->Subject,
       $created_relative_days,
       defined($Requestor->RealName) ? (length($Requestor->RealName) > 25 ?  substr($ticket->RequestorRealName, 0, 22) . "..." : $Requestor->RealName) : (length($Requestor->RealName) > 25 ? substr($ticket->RequestorAddresses, 0, 22) . "..." : $ticket->RequestorAddresses),
       $Requestor->WorkPhone || $Requestor->MobilePhone  || "";
    }
    $idle_results->{total}{count} += $idle_results->{$state}{count};
  }

  debug(4, sprintf " ... Found %s idle tickets in %s", $idle_results->{total}{count}, $queue);
  return $idle_results;
}

sub count_total_current_and_unassigned_in_queue {
  my $queue = shift;

  debug(3, "Running current and unowned report for queue $queue");
  my $current_ticket_count = 0;
  my $unowned_ticket_count = 0;
  my $tickets_current = RT::Tickets->new($RT::SystemUser);
  my $tickets_unowned = RT::Tickets->new($RT::SystemUser);

  $tickets_current->FromSQL(
    'Type = "ticket" AND '.
    '(Status != "deleted" AND Status != "resolved" AND Status != "rejected") AND '.
    'Queue = "'. $queue  . '"'
    );
  $tickets_unowned->FromSQL(
    'Type = "ticket" AND '.
    '(Status != "deleted" AND Status != "resolved" AND Status != "rejected") AND '.
    'Queue = "'. $queue  . '" AND (Owner = "Nobody" OR Owner = "NULL")'
    );
  debug(4, sprintf " ... Found %d currently in queue, and %d unowned", $tickets_current->Count, $tickets_unowned->Count);
  my $tickets_current_severity_count;
  while (my $ticket = $tickets_current->Next) {
    $tickets_current_severity_count->{$ticket->FirstCustomFieldValue("Severity")||"Not set"}++;
  }
  return ($tickets_current->Count, $tickets_unowned->Count, $tickets_current_severity_count);
}

sub find_sla_for_currently_open_tickets_in_queue {
  my $queue = shift;
  debug(3, "Running open ticket SLA report for queue $queue");
  my $tickets_in_sla = RT::Tickets->new($RT::SystemUser);
  my $tickets_out_sla = RT::Tickets->new($RT::SystemUser);
  $tickets_in_sla->FromSQL(
    'Type = "ticket" AND '.
    '(Status != "deleted" AND Status != "resolved" AND Status != "rejected") AND '.
    'Queue = "'. $queue  . '" AND Due <= "now"'
    );
  $tickets_out_sla->FromSQL(
    'Type = "ticket" AND '.
    '(Status != "deleted" AND Status != "resolved" AND Status != "rejected") AND '.
    'Queue = "'. $queue  . '" AND Due > "now"'
    );
  my $ticket_sum = $tickets_in_sla->Count + $tickets_out_sla->Count;
  return unless $ticket_sum > 0;
  my $ticket_sla_percent = 100 * ($tickets_out_sla->Count / $ticket_sum);
  debug(4, sprintf " ... Found %.2f%% outside SLA in queue %s" , $ticket_sla_percent, $queue);
  return ($tickets_in_sla->Count, $tickets_out_sla->Count, $ticket_sla_percent);
}

sub find_sla_for_all_tickets_this_month_in_queue {
  my $queue = shift;
  my $tickets = RT::Tickets->new($RT::SystemUser);
  my $start_of_month = sprintf "%4d-%02d-01",
    (localtime())[5] + 1900,
    (localtime())[4] + 1;
  my $now = sprintf "%4d-%02d-%02d %02d:%02d:%02d",
    (localtime())[5] + 1900,
    (localtime())[4] + 1,
    (localtime())[3],
    (localtime())[2],
    (localtime())[1],
    (localtime())[0];


  $tickets->FromSQL(
    'Type = "ticket" AND '.
    '(Status != "deleted" AND Status != "rejected") AND '.
    '(Resolved >= "'. $start_of_month . '" OR Status != "resolved") AND '.
    'Queue = "'. $queue  . '"'
    );

  my $stats;
  $stats->{withinSLA}=0;
  $stats->{outsideSLA}=0;
  while (my $ticket  = $tickets->Next) {
    debug(8, sprintf "ID: %s, Status: %s, Due: %s", $ticket->Id, $ticket->Status, $ticket->Due);

    if ($ticket->Status eq "resolved" && $ticket->Due gt $ticket->Resolved) {
      debug(6, sprintf "Ticket %d resolved in time", $ticket->Id);
      $stats->{withinSLA}++;
    } elsif ($ticket->Status ne "resolved" && $ticket->Status ne "stalled" && $ticket->Due gt $now) {
      debug(6, sprintf "Ticket %d not yet resolved but OK", $ticket->Id);
    } else {
      debug(6, sprintf "Ticket %d failed SLA", $ticket->Id);
      $stats->{outsideSLA}++;
    }
  }
  my $ticket_count = $stats->{outsideSLA} + $stats->{withinSLA};
  return unless $ticket_count > 0;
  my $ticket_sla_met_percent = $stats->{withinSLA} / $ticket_count * 100;
  debug(3, sprintf "So far this month (starting %s), Queue '%s' SLA was %.2f%%", $start_of_month, $queue, $ticket_sla_met_percent);
  return ($stats->{withinSLA}, $stats->{outsideSLA}, $ticket_sla_met_percent);
}

sub find_sla_for_all_tickets_last_month_in_queue {
  my $queue = shift;
  my $tickets = RT::Tickets->new($RT::SystemUser);

  my $current_year = (localtime())[5] + 1900;
  my $current_month = (localtime())[4] + 1;

  my $start_of_last_month = sprintf "%4d-%02d-01",
    $current_month == 1 ? $current_year - 1 : $current_year,
    $current_month == 1 ? $ 12 : $current_month-1;

  my $start_of_this_month = sprintf "%4d-%02d-01",
    $current_year, $current_month;


  $tickets->FromSQL(
    'Type = "ticket" AND '.
    '(Status != "deleted" AND Status != "rejected") AND '.
    '((Resolved >= "'. $start_of_last_month . '" AND Resolved < "'. $start_of_this_month .'") OR (Created >= "'. $start_of_this_month .'" AND Resolved > "'.$start_of_this_month.'") OR Resolved = "NULL") AND '.
    'Queue = "'. $queue  . '"'
    );

  my $stats;
  $stats->{withinSLA}=0;
  $stats->{outsideSLA}=0;
  while (my $ticket  = $tickets->Next) {
    debug(8, sprintf "ID: %s, Status: %s, Due: %s", $ticket->Id, $ticket->Status, $ticket->Due);

    if ($ticket->Status eq "resolved" && $ticket->Due gt $ticket->Resolved) {
      debug(6, sprintf "Ticket %d resolved in time", $ticket->Id);
      $stats->{withinSLA}++;
    } elsif ($ticket->Status ne "resolved" && $ticket->Status ne "stalled" && $ticket->Due gt $start_of_this_month) {
      debug(6, sprintf "Ticket %d not yet resolved but OK", $ticket->Id);
    } else {
      debug(6, sprintf "Ticket %d failed SLA", $ticket->Id);
      $stats->{outsideSLA}++;
    }
  }
  my $ticket_count = $stats->{outsideSLA} + $stats->{withinSLA};
  return unless $ticket_count > 0;
  my $ticket_sla_met_percent = $stats->{withinSLA} / $ticket_count * 100;
  debug(3, sprintf "Last month (starting %s), Queue '%s' SLA was %.2f%%", $start_of_last_month, $queue, $ticket_sla_met_percent);
  return ($stats->{withinSLA}, $stats->{outsideSLA}, $ticket_sla_met_percent);
}


my $message_subject;

my $message_headline = "<P STYLE=\"font-size: xx-small; margin-bottom: 0; padding-bottom: 0; border-bottom: 0\">Blackberry: <em>SPACE</em>=pg down, <em>ALT-SPACE</em>=pg up, <em>b</em>=bottom, <em>t</em>=top</P>\n";
my $message_body;
my $message_footer = sprintf "<p><small>Tickets are assumed idle if <em>NEW</em> is left for %.1f days , <em>OPEN</em> for %.1f days, and <em>STALLED</em> for %.1f days.</small></p>",
   $options->{newidlehours}/24, $options->{openidlehours}/24, $options->{stalledidlehours}/24;
my $total_tickets_idle_all_queues = 0;

foreach my $queue (@{$options->{queue}}) {
  my @subject_parts;

  my ($current_sla_in, $current_sla_out, $current_sla_pct) = find_sla_for_currently_open_tickets_in_queue($queue);
  my ($this_months_sla_in, $this_months_sla_out, $this_months_sla_pct) = find_sla_for_all_tickets_this_month_in_queue($queue);
  my ($last_month_sla_in, $last_months_sla_out, $last_months_sla_pct) = find_sla_for_all_tickets_last_month_in_queue($queue);
  my $idle_results = find_idle_tickets_for_queue($queue);
  my ($ticket_count_current_in_queue, $tickets_count_unowned, $tickets_current_severity_count) = count_total_current_and_unassigned_in_queue($queue);

  # Add the SLA review to the body of the message

  # Add the Idle ticket review to the body of the message and the subject of the message.
  if (defined($idle_results->{total}{count}) && $idle_results->{total}{count} > 0) {
    $message_headline.= sprintf "<p>$queue queue has %d idle of %d current tickets, with %d unassigned. ", $idle_results->{total}{count}, $ticket_count_current_in_queue, $tickets_count_unowned;
    $message_headline.= "Severities: " if defined($tickets_current_severity_count);

    foreach my $severity (sort { $a cmp $b} keys %{$tickets_current_severity_count}) {
      $message_headline.= sprintf "%d %s. ",
        $tickets_current_severity_count->{$severity},
        $severity;
    }

    if (defined($this_months_sla_pct) or defined($current_sla_pct)) {
      $message_headline.= "SLAs: ";
      $message_headline.= sprintf "Open now %.2f%%. ", $current_sla_pct if defined $current_sla_pct;
      $message_headline.= sprintf "This month %.2f%%. ", $this_months_sla_pct if defined $this_months_sla_pct;
      $message_headline.= sprintf "Last month %.2f%%. ", $last_months_sla_pct if defined $last_months_sla_pct;
    }

    $message_headline.= "</p>\n";

    $message_body.= "<TABLE><CAPTION>$queue Idle Tickets</CAPTION>\n";
    $message_body.= "<TR><TH>State</TH><TH>ID</TH><TH>Subject</TH><TH>Age <small>(Days)</small></TH><TH>Req.</TH></TR>";
    $message_body.= $idle_results->{new}{summary}  if $idle_results->{new}{count} > 0;
    $message_body.= $idle_results->{open}{summary}  if $idle_results->{open}{count} > 0;
    $message_body.= $idle_results->{stalled}{summary}  if $idle_results->{stalled}{count} > 0;
    $message_body.= "</TABLE>\n";

    $total_tickets_idle_all_queues+= $idle_results->{total}{count};
    push @subject_parts, sprintf  "%d/%d idle", $idle_results->{total}{count}, $ticket_count_current_in_queue;
  }

  push @subject_parts, sprintf "cur SLA %.2f%%",  $this_months_sla_pct if defined( $this_months_sla_pct);

  $message_subject.= ". " if defined($message_subject) && @subject_parts;
  $message_subject.= "$queue: ". join(', ', @subject_parts) if (@subject_parts);
}

my $style = <<FOO;
<style type="text/css">
BODY { margin: 2px; background-color: #E0E0FF; font-family: helvetica, arial, sans-serif; }
TABLE {
  border-width: 2px; border-spacing: 1px; border-style: outset; border-color: black; border-collapse: collapse; background-color: rgb(250, 240, 230);
  padding-bottom: 0; padding-top: 0; margin-top: 0;
}
TH { border-width: 1px; padding: 1px ; border-style: dashed ; border-color: black ; background-color: #CCCCCC; }
TD { border-width: 1px; padding: 1px; border-style: dashed; border-color: black; }
P { padding-top: 0; margin-top: 0; border-top: 0; }
H1 { font-size: medium;  padding-bottom: 0; margin-bottom: 0; border-bottom: 0;}
H2 { font-size: small;   padding-bottom: 0; margin-bottom: 0; border-bottom: 0;}
H3 { font-size: x-small; padding-bottom: 0; margin-bottom: 0; border-bottom: 0;}
.Critical { background-color: red; }
.Normal { background-color: orange; }
.Minor { background-color: blue; }
</style>
FOO
my $message_html = "<HTML><HEAD>$style</HEAD><BODY>" . $message_headline . "\n" . $message_body . "\n" . $message_footer . "</BODY></HTML>\n";
if (defined($message_headline)) {
  debug(1, $message_html);
  foreach my $rcpt (@{$options->{emailtoaddr}}) {
    my $msg = MIME::Lite->new(
      To => $rcpt,
      From => $options->{emailfromaddr},
      Subject => "RT Summary: $message_subject",
      Type => "multipart/related",
      );
    $msg->attach (
      Type =>'text/html',
      Data => $message_html);
    eval { $msg->send("smtp", "127.0.0.1", Debug => 0) };
    warn $@ if $@;
  }
}