Bug 1479466 - Add Security Bugs Report

Adds the security bugs report with open count and median age open of
sec-critical and sec-high bugs.

6 files changed, 709 insertions, 0 deletions

diff --git a/Bugzilla/Config/Reports.pm b/Bugzilla/Config/Reports.pm
new file mode 100644
index 000000000..26c5aad57
--- /dev/null
+++ b/Bugzilla/Config/Reports.pm
@@ -0,0 +1,37 @@
+# This Source Code Form is subject to the terms of the Mozilla Public
+# License, v. 2.0. If a copy of the MPL was not distributed with this
+# file, You can obtain one at http://mozilla.org/MPL/2.0/.
+#
+# This Source Code Form is "Incompatible With Secondary Licenses", as
+# defined by the Mozilla Public License, v. 2.0.
+
+package Bugzilla::Config::Reports;
+
+use 5.10.1;
+use strict;
+use warnings;
+
+use Bugzilla::Config::Common;
+
+our $sortkey = 1100;
+
+sub get_param_list {
+    my $class      = shift;
+    my @param_list = (
+        {
+            name    => 'report_secbugs_active',
+            type    => 'b',
+            default => 1,
+        },
+        {
+            name    => 'report_secbugs_emails',
+            type    => 't',
+            default => 'bugzilla-admin@mozilla.org'
+        },
+        {
+            name    => 'report_secbugs_products',
+            type    => 'l',
+            default => '[]'
+        },
+     );
+}
diff --git a/Bugzilla/Report/SecurityRisk.pm b/Bugzilla/Report/SecurityRisk.pm
new file mode 100644
index 000000000..1b62d476c
--- /dev/null
+++ b/Bugzilla/Report/SecurityRisk.pm
@@ -0,0 +1,318 @@
+# This Source Code Form is subject to the terms of the Mozilla Public
+# License, v. 2.0. If a copy of the MPL was not distributed with this
+# file, You can obtain one at http://mozilla.org/MPL/2.0/.
+#
+# This Source Code Form is "Incompatible With Secondary Licenses", as
+# defined by the Mozilla Public License, v. 2.0.
+
+package Bugzilla::Report::SecurityRisk;
+
+use 5.10.1;
+
+use Bugzilla;
+use Bugzilla::Error;
+use Bugzilla::Status qw(is_open_state);
+use Bugzilla::Util qw(datetime_from);
+
+use DateTime;
+use List::Util qw(any first sum);
+use Moo;
+use MooX::StrictConstructor;
+use POSIX qw(ceil);
+use Types::Standard qw(Num Int Bool Str HashRef ArrayRef CodeRef Map Dict Enum);
+use Type::Utils;
+
+my $DateTime = class_type { class => 'DateTime' };
+
+has 'start_date' => (
+    is       => 'ro',
+    required => 1,
+    isa      => $DateTime,
+);
+
+has 'end_date' => (
+    is       => 'ro',
+    required => 1,
+    isa      => $DateTime,
+);
+
+has 'products' => (
+    is       => 'ro',
+    required => 1,
+    isa      => ArrayRef [Str],
+);
+
+has 'sec_keywords' => (
+    is       => 'ro',
+    required => 1,
+    isa      => ArrayRef [Str],
+);
+
+has 'initial_bug_ids' => (
+    is  => 'lazy',
+    isa => ArrayRef [Int],
+);
+
+has 'initial_bugs' => (
+    is  => 'lazy',
+    isa => HashRef [
+        Dict [
+            id         => Int,
+            product    => Str,
+            sec_level  => Str,
+            is_open    => Bool,
+            created_at => $DateTime,
+        ],
+    ],
+);
+
+has 'check_open_state' => (
+    is => 'ro',
+    isa => CodeRef,
+    default => sub { return \&is_open_state; },
+);
+
+has 'events' => (
+    is  => 'lazy',
+    isa => ArrayRef [
+        Dict [
+            bug_id     => Int,
+            bug_when   => $DateTime,
+            field_name => Enum [qw(bug_status keywords)],
+            removed    => Str,
+            added      => Str,
+        ],
+    ],
+);
+
+has 'results' => (
+    is  => 'lazy',
+    isa => ArrayRef [
+        Dict [
+            date            => $DateTime,
+            bugs_by_product => HashRef [
+                Dict [
+                    open   => ArrayRef [Int],
+                    closed => ArrayRef [Int],
+                    median_age_open => Num
+                ]
+            ],
+            bugs_by_sec_keyword => HashRef [
+                Dict [
+                    open   => ArrayRef [Int],
+                    closed => ArrayRef [Int],
+                    median_age_open => Num
+                ]
+            ],
+        ],
+    ],
+);
+
+sub _build_initial_bug_ids {
+    # TODO: Handle changes in product (e.g. gravyarding) by searching the events table
+    # for changes to the 'product' field where one of $self->products is found in
+    # the 'removed' field, add the related bug id to the list of initial bugs.
+    my ($self) = @_;
+    my $dbh = Bugzilla->dbh;
+    my $products     = join ', ', map { $dbh->quote($_) } @{ $self->products };
+    my $sec_keywords = join ', ', map { $dbh->quote($_) } @{ $self->sec_keywords };
+    my $query        = qq{
+        SELECT
+            bug_id
+        FROM
+            bugs AS bug
+            JOIN products AS product ON bug.product_id = product.id
+            JOIN components AS component ON bug.component_id = component.id
+            JOIN keywords USING (bug_id)
+            JOIN keyworddefs AS keyword ON keyword.id = keywords.keywordid
+         WHERE
+            keyword.name IN ($sec_keywords)
+            AND product.name IN ($products)
+    };
+    return Bugzilla->dbh->selectcol_arrayref($query);
+}
+
+sub _build_initial_bugs {
+    my ($self)    = @_;
+    my $bugs      = {};
+    my $bugs_list = Bugzilla::Bug->new_from_list( $self->initial_bug_ids );
+    for my $bug (@$bugs_list) {
+        $bugs->{ $bug->id } = {
+            id        => $bug->id,
+            product   => $bug->product,
+            sec_level => (
+                # Select the first keyword matching one of the target keywords
+                # (of which there _should_ only be one found anyway).
+                first {
+                    my $x = $_;
+                    grep { lc($_) eq lc( $x->name ) } @{ $self->sec_keywords }
+                }
+                @{ $bug->keyword_objects }
+            )->name,
+            is_open    => $self->check_open_state->( $bug->status->name ),
+            created_at => datetime_from( $bug->creation_ts ),
+        };
+    }
+    return $bugs;
+}
+
+sub _build_events {
+    my ($self) = @_;
+    return [] if !(@{$self->initial_bug_ids});
+    my $bug_ids    = join ', ', @{ $self->initial_bug_ids };
+    my $start_date = $self->start_date->ymd('-');
+    my $query      = qq{
+        SELECT
+            bug_id,
+            bug_when,
+            field.name AS field_name,
+            CONCAT(removed) AS removed,
+            CONCAT(added) AS added
+        FROM
+            bugs_activity
+            JOIN fielddefs AS field ON fieldid = field.id
+            JOIN bugs AS bug USING (bug_id)
+        WHERE
+            bug_id IN ($bug_ids)
+            AND field.name IN ('keywords' , 'bug_status')
+            AND bug_when >= '$start_date 00:00:00'
+        GROUP BY bug_id , bug_when , field.name
+    };
+    my $result = Bugzilla->dbh->selectall_hashref( $query, 'bug_id' );
+    my @events = values %$result;
+    foreach my $event (@events) {
+        $event->{bug_when} = datetime_from( $event->{bug_when} );
+    }
+
+    # We sort by reverse chronological order instead of ORDER BY
+    # since values %hash doesn't guareentee any order.
+    @events = sort { $b->{bug_when} cmp $a->{bug_when} } @events;
+    return \@events;
+}
+
+sub _build_results {
+    my ($self)  = @_;
+    my $e       = 0;
+    my $bugs    = $self->initial_bugs;
+    my @results = ();
+
+    # We must generate a report for each week in the target time interval, regardless of
+    # whether anything changed. The for loop here ensures that we do so.
+    for ( my $report_date = $self->end_date; $report_date >= $self->start_date; $report_date->subtract( weeks => 1 ) ) {
+        # We rewind events while there are still events existing which occured after the start
+        # of the report week. The bugs will reflect a snapshot of how they were at the start of the week.
+        # $self->events is ordered reverse chronologically, so the end of the array is the earliest event.
+        while ( $e < scalar @{ $self->events }
+            && ( @{ $self->events }[$e] )->{bug_when} > $report_date )
+        {
+            my $event = @{ $self->events }[$e];
+            my $bug   = $bugs->{ $event->{bug_id} };
+
+            # Undo bug status changes
+            if ( $event->{field_name} eq 'bug_status' ) {
+                $bug->{is_open} = $self->check_open_state->( $event->{removed} );
+            }
+
+            # Undo keyword changes
+            if ( $event->{field_name} eq 'keywords' ) {
+                my $bug_sec_level = $bug->{sec_level};
+                if ( $event->{added} =~ /\b\Q$bug_sec_level\E\b/ ) {
+                    # If the currently set sec level was added in this event, remove it.
+                    $bug->{sec_level} = undef;
+                }
+                if ( $event->{removed} ) {
+                    # If a target sec keyword was removed, add the first one back.
+                    my $removed_sec = first {
+                        $event->{removed} =~ /\b\Q$_\E\b/
+                    }
+                    @{ $self->sec_keywords };
+                    $bug->{sec_level} = $removed_sec if ($removed_sec);
+                }
+            }
+
+            $e++;
+        }
+
+        # Remove uncreated bugs
+        foreach my $bug_key ( keys %$bugs ) {
+            if ( $bugs->{$bug_key}->{created_at} > $report_date ) {
+                delete $bugs->{$bug_key};
+            }
+        }
+
+        # Report!
+        my $date_snapshot = $report_date->clone();
+        my @bugs_snapshot = values %$bugs;
+        unshift @results,
+            {
+            date                => $date_snapshot,
+            bugs_by_product     => $self->_bugs_by_product( $date_snapshot, @bugs_snapshot ),
+            bugs_by_sec_keyword => $self->_bugs_by_sec_keyword( $date_snapshot, @bugs_snapshot ),
+            };
+    }
+
+    return \@results;
+}
+
+sub _bugs_by_product {
+    my ( $self, $report_date, @bugs ) = @_;
+    my $result = {};
+    my $groups = {};
+    foreach my $product ( @{ $self->products } ) {
+        $groups->{$product} = [];
+    }
+    foreach my $bug (@bugs) {
+        # We skip over bugs with no sec level which can happen during event rewinding.
+        if ( $bug->{sec_level} ) {
+            push @{ $groups->{ $bug->{product} } }, $bug;
+        }
+    }
+    foreach my $product ( @{ $self->products } ) {
+        my @open   = map { $_->{id} } grep { ( $_->{is_open} ) } @{ $groups->{$product} };
+        my @closed = map { $_->{id} } grep { !( $_->{is_open} ) } @{ $groups->{$product} };
+        my @ages = map { $_->{created_at}->subtract_datetime_absolute($report_date)->seconds / 86_400; }
+            grep { ( $_->{is_open} ) } @{ $groups->{$product} };
+        $result->{$product} = {
+            open            => \@open,
+            closed          => \@closed,
+            median_age_open => @ages ? _median(@ages) : 0,
+        };
+    }
+
+    return $result;
+}
+
+sub _bugs_by_sec_keyword {
+    my ( $self, $report_date, @bugs ) = @_;
+    my $result = {};
+    my $groups = {};
+    foreach my $sec_keyword ( @{ $self->sec_keywords } ) {
+        $groups->{$sec_keyword} = [];
+    }
+    foreach my $bug (@bugs) {
+        # We skip over bugs with no sec level which can happen during event rewinding.
+        if ( $bug->{sec_level} ) {
+            push @{ $groups->{ $bug->{sec_level} } }, $bug;
+        }
+    }
+    foreach my $sec_keyword ( @{ $self->sec_keywords } ) {
+        my @open   = map { $_->{id} } grep { ( $_->{is_open} ) } @{ $groups->{$sec_keyword} };
+        my @closed = map { $_->{id} } grep { !( $_->{is_open} ) } @{ $groups->{$sec_keyword} };
+        my @ages = map { $_->{created_at}->subtract_datetime_absolute($report_date)->seconds / 86_400 }
+            grep { ( $_->{is_open} ) } @{ $groups->{$sec_keyword} };
+        $result->{$sec_keyword} = {
+            open            => \@open,
+            closed          => \@closed,
+            median_age_open => @ages ? _median(@ages) : 0,
+        };
+    }
+
+    return $result;
+}
+
+sub _median {
+    # From tlm @ https://www.perlmonks.org/?node_id=474564. Jul 14, 2005
+    return sum( ( sort { $a <=> $b } @_ )[ int( $#_ / 2 ), ceil( $#_ / 2 ) ] ) / 2;
+}
+
+1;
diff --git a/scripts/secbugsreport.pl b/scripts/secbugsreport.pl
new file mode 100644
index 000000000..ae0639e20
--- /dev/null
+++ b/scripts/secbugsreport.pl
@@ -0,0 +1,83 @@
+#!/usr/bin/perl
+
+# This Source Code Form is subject to the terms of the Mozilla Public
+# License, v. 2.0. If a copy of the MPL was not distributed with this
+# file, You can obtain one at http://mozilla.org/MPL/2.0/.
+#
+# This Source Code Form is "Incompatible With Secondary Licenses", as
+# defined by the Mozilla Public License, v. 2.0.
+#
+# Usage secbugsreport.pl YYYY MM DD, e.g. secbugsreport.pl $(date +'%Y %m %d')
+
+use 5.10.1;
+use strict;
+use warnings;
+
+use lib qw(. lib local/lib/perl5);
+
+use Bugzilla;
+use Bugzilla::Component;
+use Bugzilla::Constants;
+use Bugzilla::Error;
+use Bugzilla::Mailer;
+use Bugzilla::Report::SecurityRisk;
+
+use DateTime;
+use URI;
+use JSON::MaybeXS;
+
+BEGIN { Bugzilla->extensions }
+Bugzilla->usage_mode(USAGE_MODE_CMDLINE);
+
+exit 0 unless Bugzilla->params->{report_secbugs_active};
+exit 0 unless defined $ARGV[0] && defined $ARGV[1] && defined $ARGV[2];
+
+my $html;
+my $template     = Bugzilla->template();
+my $end_date     = DateTime->new( year => $ARGV[0], month => $ARGV[1], day => $ARGV[2] );
+my $start_date   = $end_date->clone()->subtract( months => 6 );
+my $report_week  = $end_date->ymd('-');
+my $products     = decode_json( Bugzilla->params->{report_secbugs_products} );
+my $sec_keywords = [ 'sec-critical', 'sec-high' ];
+my $report       = Bugzilla::Report::SecurityRisk->new(
+    start_date   => $start_date,
+    end_date     => $end_date,
+    products     => $products,
+    sec_keywords => $sec_keywords
+);
+my $vars = {
+    urlbase         => Bugzilla->localconfig->{urlbase},
+    report_week     => $report_week,
+    products        => $products,
+    sec_keywords    => $sec_keywords,
+    results         => $report->results,
+    build_bugs_link => \&build_bugs_link,
+};
+
+$template->process( 'reports/email/security-risk.html.tmpl', $vars, \$html )
+    or ThrowTemplateError( $template->error() );
+
+# For now, only send HTML email.
+my $email = Email::MIME->create(
+    header_str => [
+        From    => Bugzilla->params->{'mailfrom'},
+        To      => Bugzilla->params->{report_secbugs_emails},
+        Subject => "Security Bugs Report for $report_week"
+    ],
+    attributes => {
+        content_type => 'text/html',
+        charset      => 'UTF-8',
+        encoding     => 'quoted-printable',
+    },
+    body_str => $html,
+);
+
+MessageToMTA($email);
+
+sub build_bugs_link {
+    my ( $arr, $product ) = @_;
+    my $uri = URI->new( Bugzilla->localconfig->{urlbase} . 'buglist.cgi' );
+    $uri->query_param( bug_id => ( join ',', @$arr ) );
+    $uri->query_param( product => $product ) if $product;
+    return $uri->as_string;
+}
diff --git a/t/security-risk.t b/t/security-risk.t
new file mode 100644
index 000000000..520953bc0
--- /dev/null
+++ b/t/security-risk.t
@@ -0,0 +1,156 @@
+#!/usr/bin/perl
+# This Source Code Form is subject to the terms of the Mozilla Public
+# License, v. 2.0. If a copy of the MPL was not distributed with this
+# file, You can obtain one at http://mozilla.org/MPL/2.0/.
+#
+# This Source Code Form is "Incompatible With Secondary Licenses", as
+# defined by the Mozilla Public License, v. 2.0.
+use strict;
+use warnings;
+use 5.10.1;
+use lib qw( . lib local/lib/perl5 );
+use Bugzilla;
+
+BEGIN { Bugzilla->extensions };
+
+use Test::More;
+use Test2::Tools::Mock;
+use Try::Tiny;
+
+use ok 'Bugzilla::Report::SecurityRisk';
+can_ok('Bugzilla::Report::SecurityRisk', qw(new results));
+
+sub check_open_state_mock {
+    my ($state) = @_;
+    return grep { /^$state$/ } qw(UNCOMFIRMED NEW ASSIGNED REOPENED);
+}
+
+try {
+    use Bugzilla::Report::SecurityRisk;
+    my $report = Bugzilla::Report::SecurityRisk->new(
+        start_date      => DateTime->new( year => 2000, month => 1, day => 9 ),
+        end_date        => DateTime->new( year => 2000, month => 1, day => 16 ),
+        products        => [ 'Firefox', 'Core' ],
+        sec_keywords    => [ 'sec-critical', 'sec-high' ],
+        check_open_state  => \&check_open_state_mock,
+        initial_bug_ids => [ 1, 2, 3, 4 ],
+        initial_bugs    => {
+            1 => {
+                id         => 1,
+                product    => 'Firefox',
+                sec_level  => 'sec-high',
+                is_open    => 0,
+                created_at => DateTime->new( year => 2000, month => 1, day => 1 ),
+            },
+            2 => {
+                id         => 2,
+                product    => 'Core',
+                sec_level  => 'sec-critical',
+                is_open    => 0,
+                created_at => DateTime->new( year => 2000, month => 1, day => 1 ),
+            },
+            3 => {
+                id         => 3,
+                product    => 'Core',
+                sec_level  => 'sec-high',
+                is_open    => 1,
+                created_at => DateTime->new( year => 2000, month => 1, day => 5 ),
+            },
+            4 => {
+                id         => 4,
+                product    => 'Firefox',
+                sec_level  => 'sec-critical',
+                is_open    => 1,
+                created_at => DateTime->new( year => 2000, month => 1, day => 10 ),
+            },
+        },
+        events           => [
+            # Canned event's should be in reverse chronological order.
+            {
+                bug_id     => 2,
+                bug_when   => DateTime->new( year => 2000, month => 1, day => 14 ),
+                field_name => 'keywords',
+                removed    => '',
+                added      => 'sec-critical',
+
+            },
+            {
+                bug_id     => 1,
+                bug_when   => DateTime->new( year => 2000, month => 1, day => 12 ),
+                field_name => 'bug_status',
+                removed    => 'ASSIGNED',
+                added      => 'RESOLVED',
+            },
+        ],
+    );
+    my $actual_results = $report->results;
+    my $expected_results = [
+        {
+            date => DateTime->new( year => 2000, month => 1, day => 9 ),
+            bugs_by_product => {
+                'Firefox' => {
+                    # Rewind the event that caused 1 to close.
+                    open            => [1],
+                    closed          => [],
+                    median_age_open => 8
+                },
+                'Core' => {
+                    # 2 wasn't a sec-critical bug on the report date.
+                    open            => [3],
+                    closed          => [],
+                    median_age_open => 4
+                }
+            },
+            bugs_by_sec_keyword => {
+                'sec-critical' => {
+                    # 2 wasn't a sec-crtical bug and 4 wasn't created yet on the report date.
+                    open            => [],
+                    closed          => [],
+                    median_age_open => 0
+                },
+                'sec-high' => {
+                    # Rewind the event that caused 1 to close.
+                    open            => [ 1, 3 ],
+                    closed          => [],
+                    median_age_open => 6
+                }
+            },
+        },
+        {   # The report on 2000-01-16 matches the state of initial_bugs.
+            date => DateTime->new( year => 2000, month => 1, day => 16 ),
+            bugs_by_product => {
+                'Firefox' => {
+                    open            => [4],
+                    closed          => [1],
+                    median_age_open => 6
+                },
+                'Core' => {
+                    open            => [3],
+                    closed          => [2],
+                    median_age_open => 11
+                }
+            },
+            bugs_by_sec_keyword => {
+                'sec-critical' => {
+                    open            => [4],
+                    closed          => [2],
+                    median_age_open => 6
+                },
+                'sec-high' => {
+                    open            => [3],
+                    closed          => [1],
+                    median_age_open => 11
+                }
+            },
+        },
+    ];
+
+    is_deeply($actual_results, $expected_results, 'Report results are accurate');
+
+}
+catch {
+    fail('got an exception during main part of test');
+    diag($_);
+};
+
+done_testing;
diff --git a/template/en/default/admin/params/reports.html.tmpl b/template/en/default/admin/params/reports.html.tmpl
new file mode 100644
index 000000000..79b6af35d
--- /dev/null
+++ b/template/en/default/admin/params/reports.html.tmpl
@@ -0,0 +1,20 @@
+[%# This Source Code Form is subject to the terms of the Mozilla Public
+  # License, v. 2.0. If a copy of the MPL was not distributed with this
+  # file, You can obtain one at http://mozilla.org/MPL/2.0/.
+  #
+  # This Source Code Form is "Incompatible With Secondary Licenses", as
+  # defined by the Mozilla Public License, v. 2.0.
+  #%]
+[%
+   title = "Reports"
+   desc = "Configure reporting parameters"
+%]
+
+[% param_descs = {
+    report_secbugs_active => "Enable or disable the security $terms.bugs report feature."
+    report_secbugs_emails =>
+      "Comma delimited list of the email addresses that the security $terms.bugs report will be sent to.",
+    report_secbugs_products =>
+      "JSON array of the products the security $terms.bugs report will report on. e.g [\"Prod1\", \"Prod2\"]",
+  }
+%]
diff --git a/template/en/default/reports/email/security-risk.html.tmpl b/template/en/default/reports/email/security-risk.html.tmpl
new file mode 100644
index 000000000..0fca42e05
--- /dev/null
+++ b/template/en/default/reports/email/security-risk.html.tmpl
@@ -0,0 +1,95 @@
+[%# This Source Code Form is subject to the terms of the Mozilla Public
+  # License, v. 2.0. If a copy of the MPL was not distributed with this
+  # file, You can obtain one at http://mozilla.org/MPL/2.0/.
+  #
+  # This Source Code Form is "Incompatible With Secondary Licenses", as
+  # defined by the Mozilla Public License, v. 2.0.
+  #%]
+
+[% PROCESS global/variables.none.tmpl %]
+
+<!doctype html>
+<html>
+<head>
+  <title>Security [% terms.Bugs %] Report for the week of [% report_week FILTER html %]</title>
+  <base href="[% urlbase FILTER txt %]">
+</head>
+<body>
+<p>Security [% terms.Bugs %] Report for the week of [% report_week FILTER html %]</p>
+<p>To narrow down open [% terms.bugs %] click on the link and at the bottom of the search results use the 'Edit Search' functionality to filter by component and so on.
+This will filter only the open [% terms.bugs %] counted in the report (as long as you do not modify the '[% terms.Bugs %] numbered' section of the search).
+</p>
+
+<h3>[% terms.Bugs %] By Severity</h3>
+<table style="border: 1px solid grey">
+  <tr>
+    <th style="border: 1px solid grey"></th>
+    [% FOREACH keyword IN sec_keywords %]
+      <th style="border: 1px solid grey; text-align: center" colspan="2"><b>[% keyword FILTER html %]</b></th>
+    [% END %]
+  </tr>
+  <tr>
+    <td style="border: 1px solid grey"></td>
+    [% FOREACH keyword IN sec_keywords %]
+      <td style="border: 1px solid grey; text-align: right">Open Count</td>
+      <td style="border: 1px solid grey; text-align: right">Median Days Open</td>
+    [% END %]
+  </tr>
+  [% FOREACH result IN results.reverse %]
+  <tr>
+    <td style="border: 1px solid grey">[% result.date.ymd('-') FILTER html %]</td>
+    [% FOREACH keyword IN sec_keywords %]
+      <td style="border: 1px solid grey; text-align: right">
+        [% IF result.bugs_by_sec_keyword.$keyword.open.size %]
+          <a href="[% build_bugs_link(result.bugs_by_sec_keyword.$keyword.open) FILTER html %]">
+            [% result.bugs_by_sec_keyword.$keyword.open.size FILTER html %]
+          </a>
+        [% ELSE %]
+          [% result.bugs_by_sec_keyword.$keyword.open.size FILTER html %]
+        [% END %]
+      </td>
+      <td style="border: 1px solid grey; text-align: right">
+        [% result.bugs_by_sec_keyword.$keyword.median_age_open FILTER format("%.2f") FILTER html %]
+      </td>
+    [% END %]
+  </tr>
+  [% END %]
+</table>
+
+<h3>Sec-Critical + Sec-High [% terms.Bugs %] by Product</h3>
+<table style="border: 1px solid grey">
+  <tr>
+    <th style="border: 1px solid grey"></th>
+    [% FOREACH product IN products %]
+      <th style="border: 1px solid grey; text-align: center" colspan="2"><b>[% product FILTER html %]</b></th>
+    [% END %]
+  </tr>
+  <tr>
+    <td style="border: 1px solid grey"></td>
+    [% FOREACH product IN products %]
+      <td style="border: 1px solid grey; text-align: right">Open Count</td>
+      <td style="border: 1px solid grey; text-align: right">Median Days Open</td>
+    [% END %]
+  </tr>
+  [% FOREACH result IN results.reverse %]
+  <tr>
+    <td style="border: 1px solid grey">[% result.date.ymd('-') FILTER html %]</td>
+    [% FOREACH product IN products %]
+      <td style="border: 1px solid grey; text-align: right">
+        [% IF result.bugs_by_product.$product.open.size %]
+          <a href="[% build_bugs_link(result.bugs_by_product.$product.open, product) FILTER html %]">
+            [% result.bugs_by_product.$product.open.size FILTER html %]
+          </a>
+        [% ELSE %]
+          [% result.bugs_by_product.$product.open.size FILTER html %]
+        [% END %]
+      </td>
+      <td style="border: 1px solid grey; text-align: right">
+        [% result.bugs_by_product.$product.median_age_open FILTER format("%.2f") FILTER html %]
+      </td>
+    [% END %]
+  </tr>
+  [% END %]
+</table>
+</body>
+</html>