Redhat Satellite 服务器的自动报告

nikoo

　　Redhat RHN 网络及Satellite服务器主要是部署在企业内部，为企业内部的Redhat Linux提供安全补丁，bug fix，以及YUM源等功能，您所有的Redhat Linux服务器需要通过手工或者脚本来注册进入RHN。通过Redhat RHN Satellite,可以非常直观的了解到当前环境中部署的Linux server的安全状态，在Redhat发布新的安全升级或补丁后，您本地的satellite server会第一时间收到更新。您可以通过登录satellite server的web页面来直观的，全自动化的进行补丁升级。
　　在实际的使用中，我们发现每天登录satellite服务器来检查补丁是一件重复劳动，希望能够每天定期收到来自satellite服务器的报告，主要报告内容为：当前有多少服务器注册进入了RHN, 多少服务器没有及时报告状态，当前需要的critical security patch的数量及条目。
　　通过查询文档，satellite服务器的CLI提供了rhn-entitlement-report，rhn-satellite-exporter 等命令可以部分实现上述功能，但我们需要更为详细的报告。考虑到satellite 服务器内置了Oracle数据库，所有的记录都是登记在Oracle数据库中，于是我尝试通过查询oracle数据库来导出需要的报告。
　　oracle数据库默认的instance，user，password可以在/etc/rhn/rhn.conf 问查看，默认为rhnsat,rhnsat,rhnsat,默认端口1521. 脚本如下：
　　

　　#!/usr/bin/perl -w
# Ken Zhang, 2013-08-07
# This script is used for satellite daily reporting.


use strict;
use DBI;

my $oraclehost = "satellite_server_name";
my $oracleins = "rhnsat";
my $oracleuser = "rhnsat";
my $oraclepasswd = "rhnsat";
my $oracleport = "1521";

my $sql_last_checkin_list = qq {select server_name from rhnserveroverview WHERE last_checkin_days_ago > '1' order by server_id};
#my $sql_last_checkin_list = qq {select server_name from rhnserveroverview order by server_name};
my $sql_total_servers = qq {select count(*) as TOTAL_CLIENTS from rhnserveroverview};
my $sql_critical_list = qq {select * from (SELECT  E.advisory_name, E.advisory_type, E.update_date, E.synopsis as advisory_synopsis,(SELECT  COUNT(DISTINCT S.id) FROM  rhnServerNeededErrataCache SNEC, rhnServer S, rhnUserServerPerms USP WHERE  USP.user_id =1 AND  USP.server_id = S.ID AND  S.id = SNEC.server_id AND  EXISTS (SELECT 1     FROM rhnServerFeaturesView SFV WHERE SFV.server_id = SNEC.server_id AND SFV.label = 'ftr_errata_updates') AND  SNEC.errata_id = E.id) AS AFFECTED_SYSTEM_COUNT FROM  rhnErrata E where  E.synopsis LIKE 'Critical%') where AFFECTED_SYSTEM_COUNT>=1 order by update_date desc};


my $oracle_dbh = DBI->connect("dbi:Oracle:host=$oraclehost;sid=$oracleins",$oracleuser,$oraclepasswd,{RaiseError=>1, AutoCommit=>0}) or die "Cannot connect to database!$!\n";
my $sth1 = $oracle_dbh->prepare($sql_last_checkin_list);
$sth1->execute();
open OUTPUT, '>', 'temp.out';
print OUTPUT "These servers are not reporting in last one day.\n\n";
while (my @output = $sth1->fetchrow_array()){
print OUTPUT "@output\n";
}
my $sth2 = $oracle_dbh->prepare($sql_total_servers);
$sth2->execute();
my $totalservers = $sth2->fetchrow_array();
print OUTPUT "\n================================================\n";
print OUTPUT "There are $totalservers servers registered with satellite.\n\n";
print OUTPUT "\n================================================\n";
print OUTPUT "Advisory_Name |  Advisory_Type | Update_Date | Synopsis                | System_Counts\n" ;
my $sth3 = $oracle_dbh->prepare($sql_critical_list);
$sth3->execute();
my $critical_cnt  = 0;
my @criticallist;
while (@criticallist = $sth3->fetchrow_array()){
print OUTPUT "@criticallist.\n";
$critical_cnt++;
if ($criticallist[0] !~ /^RHSA.*/ ) {$critical_cnt--;}
}

print OUTPUT "\nThere is\/are total $critical_cnt critical patch(es) needed today.",`date +%F`;

$sth1->finish;
$sth2->finish;
$sth3->finish;
close OUTPUT;
$oracle_dbh->disconnect();


# Print out the file content.
open INPUT,'<','temp.out';
while(<INPUT>){
print;
}
close INPUT;

脚本运行结果如下：

　　These servers are not reporting in last one day.


================================================
There are 532 servers registered with satellite.


================================================
Advisory_Name |  Advisory_Type | Update_Date | Synopsis                | System_Counts

There is/are total 0 critical patch(es) needed today.2014-01-15


　　然后我们可以将temp.out的内容通过email和crontab，每天定时发送到邮箱，上班的第一时间就可以查看到当前的补丁及系统状态了