Ivan,

I have a script, based on perl which was developed by a ex-colleague which I modified a little.
It creates an Excelsheet. It requires a System-group, a system-group admin user and password as parameters.

Usage:

patch_satellite.pl -g system-group -u username -g password

more patch_satellite.pl

########################################################################################################################
#!/usr/bin/perl -w

use strict;
use Frontier::Client;
use Spreadsheet::WriteExcel;
use Getopt::Long;
use Mail::Sender;
use Term::ReadKey;
use Date::Calc qw/Today Delta_Days/;

use Data::Dumper;

# Unbuffer stdout..
$| = 1;

my $group;
my $system;
my $all;
my $user;
my $pass;
my $list_groups;
my $list_servers_in_group;
GetOptions (
"lg" => \$list_groups,
"lsg=s" => \$list_servers_in_group,
"g|group=s" => \$group,
"s|system=s" => \$system,
"a|all" => \$all,
"u|user=s" => \$user,
"p|pass=s" => \$pass,
);

print &usage() unless ($user && ($group || $all || $system || $list_groups || $list_servers_in_group));

my @advisory_types = ('Security Advisory', 'Product Enhancement Advisory', 'Bug Fix Advisory');

# print "Password for $user:";
# ReadMode(2);
# my $ch = '';
# while ( defined ( $ch = ReadKey() ) ) {
# last if $ch eq "\x0D" or $ch eq "\x0A";
# if ($ch eq "\x08") { # backspace
# print "\b \b" if $pass; # back up 1
# chop $pass;
# next;
# }
# $pass .= $ch;
#}
print "\n";
ReadMode(0);

my $HOST = 'localhost';
my $target_file = "/tmp/errata.$group.xls";
my $spacer = '';

my $client = new Frontier::Client(url => "http://$HOST/rpc/api");

my $session = $client->call('auth.login', $user, $pass);

# Get user e-mail
my $profile = $client->call('user.getDetails', $session, $user);

die "No e-mail address found for user $user. Please fix this in the satellite webinterface\n"
unless($profile->{'email'});

my $mail = $profile->{'email'};

sub send_mail;
sub usage;
my $now = time();
my $today = sprintf '%02d-%02d-%4d', (localtime($now))[3],
(localtime($now))[4] +1,
(localtime($now))[5]+1900;

# nieuw Excel workbook
my $workbook = Spreadsheet::WriteExcel->new($target_file) or die "Cannot create $target_file\n";

# Formats
my $header = $workbook->add_format();
$header->set_bold();
my $red = $workbook->add_format();
$red->set_bold();
$red->set_color('red');

my @systems;
if ( $list_groups ) {
my @g = map { $->{'name'} } @{$client->call('systemgroup.listAllGroups', $session)};
print join "\n", sort @g;
print "\n";
exit;
}
elsif ($list_servers_in_group) {
my @s = map { $->{'profile_name'} } @{$client->call('systemgroup.listSystems', $session, $list_servers_in_group)};
print join "\n", sort @s;
print "\n";
exit;
}
elsif ($group) {
foreach my $s ( @{$client->call('systemgroup.listSystems', $session, $group)} ) {
my $sys = $client->call('system.getName', $session, $s->{'id'});
push @systems, $sys->{'id'}.'|'.$sys->{'name'}.'|'.$sys->{'last_checkin'}->value."|".$group;
}
die "No systems found for group: $group\n" unless (@systems >= 1);
}
elsif ($system) {
@systems = map { $->{'id'}.'|'.$->{'name'}.'|'.$_->{'last_checkin'}->value } @{$client->call('system.getId', $session, $system)};
die "No system found with name: $system\n" unless (@systems >= 1);
}

my %data;
foreach my $s (@systems) {
my ($id, $name, $checkin, $group) = split(/|/, $s);
$name = $id if ($name eq 'unknown');
if ($all || $system) {
my @groups;
foreach my $grp ( @{ $client->call('system.listGroups', $session, $id) } ) {
next unless $grp->{'subscribed'};
push @groups, $grp->{'system_group_name'};
}
$group = join ',', @groups;
}
$data{$group}->{$name}->{'last_checkin'} = $checkin;
foreach my $type (@advisory_types) {

            $data{$group}->{$name}->{$type} = $client->call('system.getRelevantErrataByType', $session, $id, $type);
    }

}

my $page1 = $workbook->add_worksheet('total');
$page1->add_write_handler(qr[\w], \&store_string_widths);

my $r1 =0;
my $c1 =0;
$page1->write_row($r1++, $c1+1, [sort @advisory_types], $header);
foreach my $group ( sort keys %data ) {
$group = 'unknown' unless ($group);
$page1->write($r1++, $c1, $group, $header);

    foreach my $host ( sort keys %{$data{$group}} ) {
            my $checkin = $data{$group}->{$host}->{'last_checkin'};
            $checkin =~ s/T\d+:\d+:\d+\s*$//;
            my ($y, $m, $d) = ($checkin =~ /(\d{4})(\d{2})(\d{2})/);
            my $dd = Delta_Days($y, $m, $d, Today());
            my $format = $dd > 14 ? $red : undef;
            $page1->write_row($r1, $c1++, [$host], $format);
            foreach my $atype (sort @advisory_types) {
                    $page1->write_row($r1, $c1++, [ scalar @{$data{$group}->{$host}->{$atype}} ], $format);
            }
            $c1 = 0;
            $r1++;

            # sheetnames can only be 31 chars long...
            (my $sheetname = $host) =~ s/^(.{31}).*/$1/;

            my $worksheet = $workbook->add_worksheet($sheetname);
            $worksheet->add_write_handler(qr[\w], \&store_string_widths);
            my $r = 0;
            my $c = 0;
            $worksheet->write_row($r++, $c, [ '', 'advisory name', 'synopsis', 'release date' ], $header);

            foreach my $atype (@advisory_types) {
                    $worksheet->write_row($r++, $c, [ $atype ], $header );
                    my @errata = @{ $data{$group}->{$host}->{$atype} };
        # BB: Print message if No advisories are available
        if ($#errata lt 0){
                            $worksheet->write_row($r++, $c, [ "No advisories available"  ]);

        }
                    foreach my $e (@errata) {
                            $worksheet->write_row($r, $c, [ $atype, $e->{'advisory_name'}, $e->{'advisory_synopsis'}, $e->{'date'}  ]);
                            (my $adv_url = $e->{'advisory_name'}) =~ s/:/-/g;
                            $worksheet->write_url($r++, $c+1, "http://rhn.redhat.com/errata/$adv_url.html", $e->{'advisory_name'});
                    }
    # BB: Add empty line between Advisory Types
    $r++;
            }
            autofit_columns($worksheet);
    }

}

autofit_columns($page1);
$workbook->close();

system('gzip', $target_file);
send_mail("$target_file.gz", $mail);
unlink "$target_file.gz";

sub usage() {
print <<EOF;
usage: $0:
-u|user Log in as satellite user : This option is mandatory

    List options:
        -lg                 List all systemgroups that <USER> can see
        -lsg <GROUP>        List all systems in systemgroup <GROUP> (if visible by user <USER>)

    Errata report options:
        -g|group <GROUP>    Create an errata report for all systems in group <GROUP>
        -s|system <SYSTEM>  Create an errata report for systems <SYSTEM>
        -a|all              Create an errata report for ALL systems

EOF
exit 0;

}

sub send_mail($$) {
my $file = shift;
my $mailaddr = shift;

    my $sender = new Mail::Sender
    {
            smtp => '127.0.0.1',
            from => 'admin@satellite.internal.kootstra.co.uk'
    };

    $sender->MailFile(
    {
            to      => $mailaddr,
            subject => "errata overzicht $group\n",
            msg     => "errata overzicht $group\n",
            file    => $file
    });

}

###############################################################################
#
# Adjust the column widths to fit the longest string in the column.
#
###############################################################################
sub autofit_columns {

my $worksheet = shift;
my $col       = 0;

for my $width (@{$worksheet->{__col_widths}}) {

    $worksheet->set_column($col, $col, $width) if $width;
    $col++;
}

}
###############################################################################
# Very simple conversion between string length and string width for Arial 10.
#
###############################################################################
sub string_width {

return 0.99 * length $_[0];

}
###############################################################################
#
# The following function is a callback that was added via add_write_handler()
# above. It modifies the write() function so that it stores the maximum
# unwrapped width of a string in a column.
#
###############################################################################
sub store_string_widths {

my $worksheet = shift;
my $col       = $_[1];
my $token     = $_[2];

\# Ignore some tokens that we aren't interested in.
return if not defined $token;       # Ignore undefs.
return if $token eq '';             # Ignore blank cells.
return if ref $token eq 'ARRAY';    # Ignore array refs.
return if $token =~ /^=/;           # Ignore formula

\# Ignore numbers
return if $token =~ /^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/;

\# Ignore various internal and external hyperlinks. In a real scenario
\# you may wish to track the length of the optional strings used with
\# urls.

# return if $token =~ m{^[fh]tt?ps?://};
return if $token =~ m{^mailto:};
return if $token =~ m{^(?:in|ex)ternal:};

\# We store the string width as data in the Worksheet object. We use
\# a double underscore key name to avoid conflicts with future names.
\#
my $old_width    = $worksheet->{__col_widths}->[$col];
my $string_width = string_width($token);

if (not defined $old_width or $string_width > $old_width) {
    \# You may wish to set a minimum column width as follows.
    \#return undef if $string_width < 10;

    $worksheet->{__col_widths}->[$col] = $string_width;
}

\# Return control to write();
return undef;

}
###########################################################################################################

perl-Email-Date-1.103-6.el6.noarch is one the required rpms

Kind regards,

Jan Gerrit