Tech Blog

If you run Red Hat Enterprise Linux and can't yet justify the cost of the Red Hat Satellite product, this post might be of interest to you.  Many admins in this situation will set up a local Red Hat yum repository and point all their systems at it for updates but in this scenario, how do you know what systems need what updates?

Another option many admins will consider is Spacewalk which is the upstream community project from which the Red Hat Satellite product is derived.  I have used Spacewalk for many years and while I am a big fan of it, this too presents a few issues.  First off, there is no way within Spacewalk to synchronize your Red Hat software entitlements.  Second, Red Hat has updated their current Satellite product to v6 which is built using different technologies while Spacewalk is still at v5.  As of this writing, the FAQ on the Spacewalk site says that it will still be supported for some time to come but it remains to be seen exactly how long or in what regard.  Finally, if you point all your Red Hat products at a Spacewalk server instead of Red Hat's online licensing portal, you MAY well be violating Red Hat's terms of service!

Now might be a good time to point out a couple of things.  First, what I'm about to cover is by NO MEANS intended to be a functional replacement to either Spacewalk or Red Hat Satellite.  Both of those products have far more features than this will even begin to cover.  I also want to note that I am NOT a DBA so this solution may not be efficient.  In fact, like with most scripts, I can make NO guarantee that it will work for you at all.  

You may also note that I make no mention of CentOS in this article.  The reason is because if you run the command '# yum update list available' on CentOS you will notice that no packages are returned, even if you have updates that have not yet been installed.  That is because security information is provided by Red Hat and there is no equivalent for CentOS.  I am aware of scripts that can provide this information for Spacewalk and I have in fact used them.  If I were looking for a solution for CentOS, I would certainly use Spacewalk and therefore this is really not intended for CentOS.

With all the above in mind, suppose you keep a local Red Hat repository and one of your security guys asks if you can identify all the systems in your environment that are missing a certain update?  Or maybe you want to list all Red Hat updates that are missing within your environment?  Those are questions that this setup will hopefully be able to assist with.

I'm not going to cover how to set up a local Red Hat yum repository or how to point your systems to one in this post as neither is hard to do and are thoroughly covered in other places.  I'm just going to cover how to set up a database to query information such as identified above.

The following outline describes what this system can do and some basics on how it does it:

  • It is assumed that you have a local Red Hat yum repository set up that each of your Red Hat Enterprise Linux systems (RHEL) point to but that is not entirely necessary.
  • This system consists of 2 components, a script which is scheduled to run on each RHEL system and a MariaDB server and database where the script uploads information.
  • When a system is logged to the database via the script, a field is updated with the current date/time so that this can be used later in queries.
  • An interesting option this presents is the ability to query the database for any systems that have not "checked in" in "X" number of days and then take some action.
  • The database uses MariaDB triggers and foreign keys in an attempt to keep things clean and tidy.
  • Each time the script runs, a trigger within the database first removes all the updates that were needed when the script last ran on that host before inserting any updates currently missing.
  • When no more hosts need a particular package, the package is removed from the database by another trigger.
  • If you are using this on RHEL 5 or 6, you first need to install the appropriate yum security package.  For RHEL 5, the command is '# yum install yum-security'.  For RHEL6, you need '# yum install yum-plugin-security'.  For v2 and higher, of the script you will also need to install the yum-utils package with # yum install yum-utils if you plan to track systems that need to be restarted.

And now for what it doesn't do:

  • This system doesn't keep track of when a particular update was installed.
  • It doesn't actually install any updates.
  • It doesn't keep any history information other than the last time a system "checked in" by running the script.
  • It doesn't keep track of ALL available updates.  It only tracks those that are still needed by one or more systems.
  • Just because a system is checking into this database DOES NOT mean it is definitely applying updates.  There could be a problem with the yum update process.  You will need to routinely use a query as demonstrated below to list all systems that have over "X" number of updates missing to check for this.
  • Much more but you get the general idea . . .

OK, so here is the Perl script.  I tried to use only packages that are part of the base Perl installation.  The only changes you should need to make are within the config section.

#!/usr/bin/perl
use strict;
use warnings;
use Sys::Hostname;
use DBI;
my $version_and_name = "AddToUpdatesDB.pl, v.2.0.0, © By Kyle A. Harris, All Rights Reserved";

## THIS SCRIPT IS PROVIDED AS IS.  THIS SCRIPT COMES WITH ABSOLUTELY NO WARRANTIES
## OR GUARANTEES OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING ANY IMPLICATIONS
## OF SUITABILITY, MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE.  THE ENTIRE
## RISK OF USING THIS SCRIPT REMAINS WITH YOU.  IN NO EVENT SHALL IT’S AUTHOR(S) OR
## ANYONE ELSE INVOLVED WITH THIS SCRIPT BE HELD LIABLE FOR ANY DAMAGES WHATSOEVER
## INCLUDING WITHOUT LIMITATION, DAMAGES FOR LOSS OF BUSINESS PROFITS, BUSINESS
## INTERRUPTION, LOSS OF BUSINESS INFORMATION, OR OTHER PECUNIARY LOSS ARISING OUT
## THE USE OR INABILITY TO USE THIS SCRIPT.

## Change Log ##
# v2.0.0
# - Added column "needs_restarting" to systems table to show whether or not the system needs
#   to be rebooted.  This would occur after a kernel update for example.  The package yum-utils
#   must be installed on client for this to work.  Column values as shown:
#    0 - systems doesn't need to be restarted
#    1 - system does need to be restarted
#    2 - yum-utils is not installed, /usr/bin/needs-restarting is not present, not executable,
#        or there is some other issue with the output of the needs-restarting command.
# - If upgrading from v1.x.x of this script, log in to MySQL/MariaDB and issue the following to
#   create the additional "needs_restarting" column:
#   use UpdateStatus;ALTER TABLE systems ADD COLUMN needs_restarting TINYINT(1) NOT NULL DEFAULT 2 AFTER hostname;
# - Added the ability to use SSL keys for encrypted communication.
#
# v1.0.4
# - Changed the default update command from "yum updateinfo list avaliable" to
#  "yum updateinfo list updates".
#
# v1.0.3
# - I discovered that the "error" check would sometimes be tripped by an update package
#  that contains the word "error" in it.  I changed the check to include a leading space
#  as in " error" which will hopefully correct that while still finding general errors.
#
# v1.0.2
# - Added check for the following string and die if found:
#  "No more mirrors to try"
#
# v1.0.1
# - Check for the following text strings in the output of the update command and die if found:
#  "not receiving updates"
#  "no enabled repos"
#  "need to be root"
#  "error" {case insensitive}
#
# v1.0.0
# - Initial release
##

################################################################################
## CONFIG SECTION

my $update_cmd="yum updateinfo list updates";
my $db = 'UpdateStatus';
my $dbhost = '192.168.1.254';
my $user = 'UpdateStatusUser';
my $pass = 'MyReallyLongPassword';
my $use_ssl = 'yes'; # set to lowercase yes or no

## IMPORTANT ##
# If there is some issue with the certs/config, it will revert to an
# UNENCRYPTED connection!  Please verify encryption with a packet capture!

my $ssl_key='/etc/ssl/private/client-key.pem';
my $ssl_cert='/etc/ssl/private/client-cert.pem';
my $ssl_ca='/etc/ssl/private/ca-cert.pem';

## END CONFIG SECTION
################################################################################
my @updates=`$update_cmd`;
chomp @updates;

die "ERROR:  not receiving updates" if (grep (/not receiving updates/,@updates));
die "ERROR:  no enabled repos" if (grep (/no enabled repos/,@updates));
die "ERROR:  need to be root" if (grep (/need to be root/,@updates));
die "ERROR:  unknown error in update command" if (grep (/ error/i,@updates));
die "ERROR:  No more mirrors to try" if (grep (/No more mirrors to try/,@updates));

my $needs_restarting_cmd="/usr/bin/needs-restarting -r";
my $needs_restarting=2;
my @neeeds_restarting_cmd_output=`$needs_restarting_cmd`;
my $needs_restarting_cmd_exit_val = $?;
$needs_restarting=0 if ($needs_restarting_cmd_exit_val == 0);
$needs_restarting=1 if ($needs_restarting_cmd_exit_val == 1);

# Uncomment the following line for debuging
#DBI->trace(1);

my $dsn;
if ($use_ssl eq "yes") {$dsn = "DBI:mysql:database=$db;host=$dbhost;mysql_ssl=1;mysql_ssl_client_key=$ssl_key;mysql_ssl_client_cert=$ssl_cert;mysql_ssl_ca_file=$ssl_ca"
} else {
  $dsn = "DBI:mysql:database=$db;host=$dbhost";
}

my $dbh = DBI->connect( $dsn, $user, $pass, { RaiseError => 1 }) or die ( "Couldn't connect to database: " . DBI->errstr );

# set up a date that makes MySQL happy
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
$mon++;
$year += 1900;
my $date = "$year-$mon-$mday $hour:$min:$sec";

my $hostname=hostname;
# define our update types that were previously defined in the database
my %update_types = (
  "bugfix" => 1,
  "enhancement" => 2,
  "Low/Sec." => 3,
  "Moderate/Sec." => 4,
  "Important/Sec." => 5,
  "Critical/Sec." => 6
);

# insert/update hostname ,date, and needs_restarting
$dbh->do('INSERT INTO systems (hostname, date, needs_restarting) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE date=VALUES(date), needs_restarting=VALUES(needs_restarting)', undef, $hostname, $date, $needs_restarting);

foreach my $line (@updates){
  foreach my $type (keys %update_types) {
    next if ($line !~ $type);
    my ($rhba, $type, $package) = split(' ', $line);
    $dbh->do('INSERT IGNORE INTO updates (package, rhba, typeid) VALUES (?, ?, ?)', undef, $package, $rhba, $update_types{$type} );
    $dbh->do('INSERT IGNORE INTO systems2updates (2package, 2hostname) VALUES (?, ?)', undef, $package, $hostname );
  }
}

Next, you will need a working, accessible MariaDB server.  Here is a dump of the database which includes the triggers and a table pre-populated with the current Red Hat update types:

-- MySQL dump 10.14  Distrib 5.5.50-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: UpdateStatusDB
-- ------------------------------------------------------
-- Server version       5.5.50-MariaDB

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `systems`
--

DROP TABLE IF EXISTS `systems`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `systems` (
  `date` datetime NOT NULL,
  `hostname` varchar(100) NOT NULL,
  `needs_restarting` tinyint(1) NOT NULL DEFAULT '2',
  PRIMARY KEY (`hostname`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `systems`
--

LOCK TABLES `systems` WRITE;
/*!40000 ALTER TABLE `systems` DISABLE KEYS */;
/*!40000 ALTER TABLE `systems` ENABLE KEYS */;
UNLOCK TABLES;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'NO_AUTO_VALUE_ON_ZERO' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `Before_Insert` BEFORE INSERT ON `systems`
 FOR EACH ROW DELETE FROM systems2updates
WHERE NEW.hostname = systems2updates.2hostname */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'NO_AUTO_VALUE_ON_ZERO' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `Clean_Updates` AFTER DELETE ON `systems`
 FOR EACH ROW DELETE updates.* FROM
updates LEFT JOIN systems2updates
ON updates.package = systems2updates.2package
WHERE systems2updates.2package IS NULL */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;

--
-- Table structure for table `systems2updates`
--

DROP TABLE IF EXISTS `systems2updates`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `systems2updates` (
  `2package` varchar(100) NOT NULL,
  `2hostname` varchar(100) NOT NULL,
  PRIMARY KEY (`2package`,`2hostname`) USING BTREE,
  KEY `fk_hostname` (`2hostname`),
  CONSTRAINT `fk_hostname` FOREIGN KEY (`2hostname`) REFERENCES `systems` (`hostname`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_package` FOREIGN KEY (`2package`) REFERENCES `updates` (`package`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `systems2updates`
--

LOCK TABLES `systems2updates` WRITE;
/*!40000 ALTER TABLE `systems2updates` DISABLE KEYS */;
/*!40000 ALTER TABLE `systems2updates` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `types`
--

DROP TABLE IF EXISTS `types`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `types` (
  `type` varchar(25) NOT NULL,
  `typeid` smallint(12) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`typeid`),
  UNIQUE KEY `type` (`type`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `types`
--

LOCK TABLES `types` WRITE;
/*!40000 ALTER TABLE `types` DISABLE KEYS */;
INSERT INTO `types` VALUES ('bugfix',1),('Critical/Sec.',6),('enhancement',2),('Important/Sec.',5),('Low/Sec.',3),('Moderate/Sec.',4);
/*!40000 ALTER TABLE `types` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `updates`
--

DROP TABLE IF EXISTS `updates`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `updates` (
  `rhba` varchar(100) NOT NULL,
  `package` varchar(100) NOT NULL DEFAULT '',
  `typeid` smallint(12) NOT NULL,
  PRIMARY KEY (`package`),
  KEY `typeid` (`typeid`),
  CONSTRAINT `fk_typeid` FOREIGN KEY (`typeid`) REFERENCES `types` (`typeid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `updates`
--

LOCK TABLES `updates` WRITE;
/*!40000 ALTER TABLE `updates` DISABLE KEYS */;
/*!40000 ALTER TABLE `updates` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Dumping events for database 'UpdateStatusDB'
--

--
-- Dumping routines for database 'UpdateStatusDB'
--
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2017-11-02 17:52:40

The example script below named UpdatesAlert.pl is not needed but rather is an example you can use (modify the config section as needed) to send an email when it finds a system that last checked in over "X" number of days ago.   Also note that you may need to install the perl-Mail-Sendmail module to allow it to run successfully '# yum install perl-Mail-Sendmail'.

#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use Mail::Sendmail;
# Note that if you have a default Perl install, you may need to install Mail::Sendmail
# as follows:
# yum install perl-Mail-Sendmail
my $version_and_name = "UpdatesAlert.pl, v1.0.0, © By Kyle A. Harris, All Rights Reserved";

## THIS SCRIPT IS PROVIDED AS IS.  THIS SCRIPT COMES WITH ABSOLUTELY NO WARRANTIES
## OR GUARANTEES OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING ANY IMPLICATIONS
## OF SUITABILITY, MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE.  THE ENTIRE
## RISK OF USING THIS SCRIPT REMAINS WITH YOU.  IN NO EVENT SHALL IT’S AUTHOR(S) OR
## ANYONE ELSE INVOLVED WITH THIS SCRIPT BE HELD LIABLE FOR ANY DAMAGES WHATSOEVER
## INCLUDING WITHOUT LIMITATION, DAMAGES FOR LOSS OF BUSINESS PROFITS, BUSINESS
## INTERRUPTION, LOSS OF BUSINESS INFORMATION, OR OTHER PECUNIARY LOSS ARISING OUT OF
## THE USE OR INABILITY TO USE THIS SCRIPT.

################################################################################
## CONFIG SECTION

my $db = 'UpdateStatus';
my $dbhost = '192.168.1.254';
my $user = 'UpdateStatusUser';
my $pass = 'MyReallyLongPassword';
my $always_send_email='yes';  # Set to 'yes' to email even if no systems are over $max_days
my $smtp_server = 'localhost';
my $mail_from = This email address is being protected from spambots. You need JavaScript enabled to view it.';
my $mail_to = This email address is being protected from spambots. You need JavaScript enabled to view it.';
my $mail_subject = 'Subject of email';
my $max_days = 30; # Number of days over which to alert on since last check-in
my $verbose = 'yes';    # Set to 'yes' to output to console or 'no' to be silent

## END CONFIG SECTION
################################################################################

##DBI->trace(1);

my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
$mon++;
$year += 1900;
my $date = "$mon-$mday-$year $hour:$min:$sec";

my $dsn = "DBI:mysql:database=$db;host=$dbhost";
my $dbh = DBI->connect( $dsn, $user, $pass, { RaiseError => 1 }) or die ( "Couldn't connect to database: " . DBI->errstr );

my $sth = $dbh->prepare('SELECT systems.hostname AS Hostname,DATEDIFF(NOW(), systems.date) AS Days FROM systems WHERE DATEDIFF(NOW(), systems.date) > ' . $max_days) or die "Couldn't prepare statement: " . $dbh->errstr;

$sth->execute() or die "Couldn't execute statement: " . $sth->errstr;

my ($hostname,$days);
$sth->bind_columns(undef, \$hostname, \$days);

my @message;
if ($sth->rows == 0) {
  print "$date: No systems found over $max_days day threshold\n" if ($verbose =~ /yes/i);
  push @message, "$date: No systems found over $max_days day threshold\n" if ($always_send_email =~ /yes/i);;
}else {
  push @message, "Report date: $date\n";
  push @message, "Hostname\t->\tDays Since Last Check-In\n";
  while($sth->fetch()) {
    print "$hostname ->  $days \n" if ($verbose =~ /yes/i);
    push @message, "$hostname\t->\t$days \n";
  }
}
my %mail;
print "Preparing report for mailing . . .\n" if ($verbose =~ /yes/i);
$mail{From} = $mail_from;
$mail{To} = $mail_to;
$mail{SMTP} = $smtp_server;
$mail{Subject} = $mail_subject;
$mail{Message} = "@message";
sendmail(%mail) or die $Mail::Sendmail::error;
print "Report has been e-mailed to $mail_to \n" if ($verbose =~ /yes/i);

You can either copy/paste the text above or download them via the links at the bottom of this page.  Before moving forward, it would probably be wise to run this by your security/DBA group as well.

  • Copy the UpdateStatus.sql file containing the database dump to your MariaDB server and the AddToUpdateDB.pl to a system you wish to test.  They can be the same system.
  • Create the new database on your server with the command '# mysqladmin -u root -p create UpdateStatus'.  You should be prompted for a password.  Change the user name as needed.
  • Import the data into the newly created database with '# mysql -u root -p UpdateStatus < /path/to/file/UpdateStatusDB.sql' making sure to change the path accordingly.  Again, you should be prompted for a password.
  • Now launch the MariaDB client as follows making sure to change the user/password as needed '# mysql -u root -p'.
  • Create the MariaDB account that will be used to insert data into the database making sure to change the password to one that meets your password complexity requirements.  Note here that if we will be running this script on the local database server, we need to first allow the user from the local host and then from any additional hosts in the order shown (yes order matters) '> CREATE USER 'UpdateStatusUser'@'localhost' IDENTIFIED BY 'MyPassword';'.  Then to allow the user from any host we use '> CREATE USER 'UpdateStatusUser'@'%' IDENTIFIED BY 'MyPassword';'.  I know that is rather odd but many people have received "access denied" errors because of this.  If you will not be running the script on the local database system, you can leave out the command that covers 'localhost'.
  • Now grant the user access, again taking order into consideration with '> GRANT Select, Insert, Update ON UpdateStatus.* TO 'UpdateStatusUser'@'localhost';' and '> GRANT Select, Insert, Update ON UpdateStatus.* TO 'UpdateStatusUser'@'%';'.  Just as before, the 'localhost' line can be skipped if you won't be using the script on the local system.
  • And finally '> FLUSH PRIVILEGES;' and '> quit' to exit the MariaDB client.

Now edit the Perl script AddToUpdatesDB.pl and change the config section to suit your needs and save it.  Then you need to set it to be executable with something like '# chmod +x AddToUpdatesDB.pl' and if everything is set up correctly, you should then be able to run it.  If you run it and don't see any errors, it was likely successful.

Now you can fire up the MariaDB client from the MariaDB server, change to the UpdateStatus database '> use UpdateStatus;'  and begin issuing queries.  Here is a list of queries to get you started.

QUERIES:

Let's start by simply listing all the systems that are reporting in and the last time they did so:
> SELECT * FROM systems WHERE 1;

This will list each hostname along with the number of updates it needed when it last reported in:
> SELECT systems.hostname AS Hostname, COUNT(systems2updates.2package) AS "Needed Updates" FROM systems2updates INNER JOIN systems ON systems2updates.2hostname = systems.hostname GROUP BY systems.hostname;

The query above doesn't list hosts that do not need any updates.  The following query will include those in need of 0 updates as well:
> SELECT systems.hostname AS Hostname, COUNT(systems2updates.2package) AS "Needed Updates" FROM systems2updates RIGHT JOIN systems ON systems2updates.2hostname = systems.hostname GROUP BY systems.hostname;

I use this one quite a bit.  It shows each hostname, the date/time it last checked in, and the number of needed updates:
> SELECT systems.hostname AS Hostname, systems.date AS "Last Checked In", count(systems2updates.2package) AS "Needed Updates" FROM systems2updates RIGHT JOIN systems on systems2updates.2hostname=systems.hostname GROUP BY systems.hostname;

Show each hostname along with the name of all updates it needs:
> SELECT systems2updates.2hostname AS Hostname, systems2updates.2package AS Package FROM systems2updates ORDER BY Hostname;

List each hostname and the number of packages it needs (duplicate of above but with different syntax):
> SELECT systems2updates.2hostname AS Hostname, COUNT(updates.package) AS "Needed Packages" FROM systems2updates INNER JOIN updates ON systems2updates.2package = updates.package GROUP BY systems2updates.2hostname ORDER BY Hostname;

List each hostname and the number of packages it needs IF that number is over 30:
> SELECT systems.hostname AS Hostname, COUNT(systems2updates.2package) AS "Needed Updates" FROM systems2updates INNER JOIN systems ON systems2updates.2hostname = systems.hostname GROUP BY systems.hostname HAVING COUNT(systems2updates.2package) > 30;

List each hostname, the number of needed packages, and the type:
> SELECT systems2updates.2hostname AS Hostname,COUNT(updates.package) AS "Needed Packages",types.type AS Type FROM systems2updates INNER JOIN updates ON systems2updates.2package = updates.package INNER JOIN types ON updates.typeid = types.typeid GROUP BY systems2updates.2hostname, types.type ORDER BY Hostname;

List each hostname and the number of days since it last checked in:
> SELECT systems.hostname AS Hostname,DATEDIFF(NOW(), systems.date) AS "Days Since Last Check-In" FROM systems;

List each system that last checked in over 30 days ago:
> SELECT systems.hostname AS Hostname,DATEDIFF(NOW(), systems.date) AS "Days Since Last Check-In" FROM systems WHERE DATEDIFF(NOW(), systems.date) > 30;

List each package name by type:
> SELECT types.type AS Type,updates.package AS Package FROM updates INNER JOIN types ON updates.typeid = types.typeid ORDER BY Type;

List the hostname of all systems in need of a package update that starts with the text 'zlib':
> SELECT systems2updates.2hostname AS Hostname,systems2updates.2package AS Package FROM systems2updates WHERE  systems2updates.2package LIKE 'zlib%';

What about a list of all security related updates (those with the text "Sec" in the update type) for reporting machines:
> SELECT types.type AS Type,updates.package AS Package FROM updates INNER JOIN types ON updates.typeid = types.typeid WHERE Type LIKE '%Sec%' ORDER BY Type;

List all hosts that are in need of a patch for a specific Red Hat advisory (RHSA-2017:1265 for example):
> SELECT systems2updates.2hostname AS Hostname FROM types, systems2updates INNER JOIN updates ON systems2updates.2package = updates.package WHERE updates.rhba LIKE 'RHSA-2017:1265' GROUP BY systems2updates.2hostname;

List all hosts that have not checked in on the current date:
> SELECT systems.hostname AS Hostname, systems.date AS "Last Checked In", count(systems2updates.2package) AS "Needed Updates" FROM systems2updates RIGHT JOIN systems on systems2updates.2hostname=systems.hostname WHERE systems.date <= CURDATE() GROUP BY systems.hostname;

List only hosts that are in need of updates:
> SELECT systems.hostname AS Hostname, systems.date AS "Last Checked In", count(systems2updates.2package) AS "Needed Updates" FROM systems2updates RIGHT JOIN systems on systems2updates.2hostname=systems.hostname GROUP BY systems.hostname HAVING count(systems2updates.2package) > 0;

List only hosts that are in need of updates OR have not checked in on the current date:
> SELECT systems.hostname AS Hostname, systems.date AS "Last Checked In", count(systems2updates.2package) AS "Needed Updates" FROM systems2updates RIGHT JOIN systems on systems2updates.2hostname=systems.hostname GROUP BY systems.hostname HAVING systems.date <= CURDATE() OR count(systems2updates.2package) > 0;

I will add more examples as I think of them.  If you come up with a good one of your own, please let me know.

Link to compressed tarball containing both the script AddToUpdatesDB.pl and the database dump UpdateStatusDB.sql:
UpdateStatus.tar.gz

It wouldn't be too difficult to add a PHP based web front end to this but I'll probably wait and see how popular this turns out to be.  The same holds true for adding output to the script.  In the meantime, I hope you find it useful and please drop me a note if that turns out to be the case.  Thanks for reading!

- Kyle H.