Infrastructure Databases and Information Technology

Information Technology and fundamental Infrastructure Databases

I’m sure a whole book can be written on subject Infrastructure Databases and but I want to keep this short and simple.

First some definitions:

Assets and Infrastructure databases

  1. An infrastructure asset is a component that has monetary value. Servers, workstations, switches, cables, and people are examples of infrastructure assets. An IP address is also an asset. Public IP addresses are valuable assets since they are all used up and cost money. Non-routable IP addresses (RFC 1918) are also assets even though, they are free. They still cost money in usage, planning and other processes. Not using the address space wisely will result in lost time which is another way of saying money.
  2. An infrastructure asset is necessary to be in place for the organization to start conducting business.
  3. An Infrastructure database contains fundamental information on infrastructure assets of the organization.
  4. Infrastructure databases are used to build other application and other databases upon which the applications depend.

An Information Technology (IT) infrastructure has many infrastructure databases necessary for it’s proper functioning.
Among these databases are the following:

  • DNS – Domain Name System
  • DHCP – Dynamic Host Communication Protocol
  • IPAM – IP (Internet protocol) Address Management, combination of DNS and DHCP with extras
  • Certificate Databases – keeps tracks of certificates and revocation lists.
  • Privilege Databases – determines what users can do on what machines. Many times this is incorporated into the company LDAP.
  • CMDB – Configuration Management Database – tracks hardware assets
  • Document repository – place where process and procedures and project artifacts are kept.

Note
LDAP – Lightweight Directory Access Protocol – A database engine of a sort. LDAP can contain almost anything, including DNS information. “Microsoft Active Directory” is an LDAP. This is not an infrastructure database even though it contains fundamental information on infrastructure assets. Rather, I would say that LDAP is a collection of fundamental Infrastructure databases. Some of these databases can overlap and in some organizations they do. It is possible for LDAP to provide all these services. But, I think that might be asking for trouble especially in very large organizations.

Examples of common infrastructure applications that can feed off of the infrastructure databases

Request Tracking database 
Change Control Database I
nventory applications 
Monitoring applications

Other databases can be created by mixing and matching data from the databases above. This does not make them infrastructure databases. Databases made from data found in the databases above are application databases. The inventory application will create a database of a sort consisting of a collection of text files but it’s main database upon which it relies for proper functioning is the CMDB.

A CMDB example
I believe the  CMDB should be small and sparse. I think the example provided above is a good example for a set of unix servers only. It can be extended to other devices but I don’t foresee many more fields being added.

1. The CMDB database should contain only information that cannot be obtained
   from running commands on the server and retrieving the output.
2. Host names – applies to hardware and virtual servers.
 a) The host name is unique across all DNS domains and cannot be reused even
    if the server is retired. The name is linked to historical records which
    will be used for research.
 b) The server always retains it's name. If it changes location or function 
    it retains it's original name.
 c) Names should  be independent of location,OS business or application 
    descriptions. The best names are cartoon names or human names this 
    allows the greatest flexibility and least confusion in the environment. 
    DNS/IPAM and the CMDB will answer all IP, business and location 
    questions.
3. The state definitions should be few and have very narrow and unambiguous 
   definitions. I use the following definitions for state in this example:
1 – Online – this means it is reachable via an ssh login
2 – Build – this means the name is reserved and the machine is being built
3 – Archive – the server is offline and
4 – Out-of-Bound – the server is up and running but is not on the network. 
    A certificate   issuer machine is an example.
5 - Offline – not reachable via ssh login and not on the network.

The rest of the fields for the database structure are illustrated here. That mysql code to create this structure is shown later in this blog post.

databaes schema

database schema for a minimal CMDB

The fields provided are, I believe, good suggestions. More can be added but caution should be used. The fundamental databases should be as limited in scope as possible.
The next piece of the infrastructure is to map out the existing processes or lay down new processes as appropriate.  For example the process for obtaining and building new Unix servers could be the following

1. set up a project or use an existing project or authority to identify 
   requirements for servers.
2. Obtain purchase approval
3. request names from the Unix team 
4. Unix team controls and assigns all Unix server names. The project does 
   not control the naming of the Unix servers.
5. Names are entered into the CMDB with a status of “Build”
6. Names are provided to the project.
7. Product is purchased and delivered.
8. Unix team, using the names in the CMDB can now build the servers on 
   delivery according to their build process.
9. Once the server is built the Unix team changes the status to “online” and
   automated scripts now start reaching out to the server and start 
   inventorying it and monitoring it. This dos not mean the server is ready 
   for delivery. It might take a few more days to lay down and harden the 
   machine. The definition of online only means it is reachable via ssh.

Some more details and steps could be added but on the whole the process outlined above captures the major steps. Each organization will modify it to suit their own needs.

The above example was limited to one group and one type of server. Things might get a little more complicated when you start considering multiple groups responsible for multiple parts of the infrastructure and providing access and  privileges to change information in the CMDB. But I consider these a matter of choosing the appropriate technological solution which probably already exist or can be easily integrated into the infrastructure. It does not change the fundamental rules laid out above.

The Inventory Script Application

The script relies on a database that contains the name of the server and some basic information regarding the server. Some people call this a Configuration Management Database (CMDB). The script can be easily be modified to read in a list of files from a text file if you don’t want to use the database, but a well designed database will make things easier in the long run.

The inventory script uses a simple CMDB as described above to obtain a list of servers. It reaches out to any “online” server via ssh with a designated user account and retrieves whatever information it is configured to retrieve via a configuration file. It stores the retrieved information on disk in a single file with a naming scheme based on date. This allows archiving of the information and aids in searches using the Unix grep utility.

  • This tool is based solely on ssh keys. It does not use password authentication. The inability of the script to login using ssh keys is considered an error in the environment.
  • This script allows one to run multiple commands on multiple servers in parallel.
  • In inventory mode it writes information for each server to a specific file with the name hostname-thedate.txt
  • In non-inventory mode it writes to an excel spreadsheet.

The excel spreadsheet mode should be stripped from the script and set up in a separate script. There is no need to complicate and clutter the script with excel worksheet code. Since this is a proof of concept script the excel spreadsheet code has been retained.

The script is a proof of concept tool designed by a Unix System Administrator for Unix System Administrators. It is not a tool that a novice can download click a few buttons and get a nice pretty gui or web page.

The  list of the components.

mysql database
Database interface
The script itself
An archive directory
the retrieved files.

The database
This is just  a simple example of a database that is dedicated to the concept  of servers. It has no foreign relationships

+--------------------+
| Tables_in_serverdb |
+--------------------+
| environments       |
| hosts              |
| ostype             |
| sites              |
| state              |
| zones              |
+--------------------+

There are three interfaces to the database.

  • Perl DBI – this is used by the script itself.
  • The mysql command line. There should be very little use for this except to update definitions or do some maintenance that the web interface cannot do. Of course your needs will vary.
  • A purpose built web interface. I have one built in php but I wont provide it here. These are easy enough to build and the one I have is to rudimentary to share. Also I am in the process of re-engineering it.

And here is the sql that will create the mysql  database:

-- MySQL dump 10.13  Distrib 5.5.32, for Linux (x86_64)
--
-- Host: localhost    Database: serverdb
-- ------------------------------------------------------
-- Server version    5.5.32
/*!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 `environments`
--
DROP TABLE IF EXISTS `environments`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `environments` (
`env_id` int(11) NOT NULL AUTO_INCREMENT,
`environment` varchar(32) NOT NULL DEFAULT '',
PRIMARY KEY (`env_id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `environments`
--
LOCK TABLES `environments` WRITE;
/*!40000 ALTER TABLE `environments` DISABLE KEYS */;
INSERT INTO `environments` VALUES (1,'development'),(2,'test'),(3,'QA'),(4,'recovery'),(5,'production'),(6,'na'),(7,'DIT');
/*!40000 ALTER TABLE `environments` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `hosts`
--
DROP TABLE IF EXISTS `hosts`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `hosts` (
`host_id` int(11) NOT NULL AUTO_INCREMENT,
`hostname` varchar(32) NOT NULL DEFAULT '',
`assettag` varchar(32) DEFAULT NULL,
`create_date` date DEFAULT NULL,
`mod_date` date DEFAULT NULL,
`purpose` text,
`businessowner` varchar(24) DEFAULT NULL,
`terminal` varchar(24) DEFAULT NULL,
`cablocation` varchar(24) DEFAULT NULL,
`zone_id` int(11) DEFAULT NULL,
`site_id` int(11) DEFAULT NULL,
`ostype_id` int(11) DEFAULT NULL,
`env_id` int(11) DEFAULT NULL,
`serialnumber` varchar(24) DEFAULT NULL,
`sid` int(11) DEFAULT NULL,
PRIMARY KEY (`host_id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `hosts`
--
--
-- Table structure for table `ostype`
--
DROP TABLE IF EXISTS `ostype`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ostype` (
`ostype_id` int(11) NOT NULL AUTO_INCREMENT,
`ostype` varchar(32) NOT NULL DEFAULT '',
PRIMARY KEY (`ostype_id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `ostype`
--
LOCK TABLES `ostype` WRITE;
/*!40000 ALTER TABLE `ostype` DISABLE KEYS */;
INSERT INTO `ostype` VALUES (1,'RedHat Linux'),(2,'Solaris'),(3,'AIX'),(4,'Cyclades'),(5,'na');
/*!40000 ALTER TABLE `ostype` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `sites`
--
DROP TABLE IF EXISTS `sites`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `sites` (
`site_id` int(11) NOT NULL AUTO_INCREMENT,
`site` varchar(32) NOT NULL DEFAULT '',
PRIMARY KEY (`site_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `sites`
--
LOCK TABLES `sites` WRITE;
/*!40000 ALTER TABLE `sites` DISABLE KEYS */;
INSERT INTO `sites` VALUES (1,'Gothum');
/*!40000 ALTER TABLE `sites` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `state`
--
DROP TABLE IF EXISTS `state`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `state` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`state` varchar(12) DEFAULT NULL,
PRIMARY KEY (`sid`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `state`
--
LOCK TABLES `state` WRITE;
/*!40000 ALTER TABLE `state` DISABLE KEYS */;
INSERT INTO `state` VALUES (1,'Online'),(2,'Build'),(3,'Archive'),(4,'Out-of-Bound'),(5,'Offline');
/*!40000 ALTER TABLE `state` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `zones`
--
DROP TABLE IF EXISTS `zones`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `zones` (
`zone_id` int(11) NOT NULL AUTO_INCREMENT,
`zone` varchar(20) NOT NULL DEFAULT '',
PRIMARY KEY (`zone_id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `zones`
--
LOCK TABLES `zones` WRITE;
/*!40000 ALTER TABLE `zones` DISABLE KEYS */;
INSERT INTO `zones` VALUES (1,'dmz'),(2,'internal');
/*!40000 ALTER TABLE `zones` ENABLE KEYS */;
UNLOCK TABLES;
/*!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 */;

The commands that the script will run on the remote host are listed in a config.ini file. Here is an example of commands for a Linux system. A separate config file for a different operating system, such as Solaris, can be created to run on multiple Solaris servers. The command that will be run is listed in the section name, the stuff between the brackets. The section name will also be used in titles. Use the parameter “fullcmd” when you want to shorten the section name. The parameter “fullcmd” will always override what is in the section name. It will also accept the multi-line attribute of the perl module Config::Inifiles so fairly simple scripts can be coded directly into the ini-file. I have not fully tested this facility so your mileage may vary.

linux_commands.ini
# host are obtained from the database
[uname -a]
enabled  = 0
privlige = sudo
comment = "uname comment"
[date]
enabled = 1
[who -r]
enabled = 1
[uptime]
enabled = 1
[/sbin/ifconfig -a]
enabled = 1
[sudo -n /usr/sbin/dmidecode]
enabled = 1
[sudo -n /usr/sbin/lsusb]
enabled = 1
[sudo -n /usr/bin/lsusb]
enabled = 1
[sudo -n /usr/bin/lsblk]
enabled = 1
[sudo -n /usr/bin/lscpu]
enabled = 1
[sudo -n /usr/bin/lsinitrd]
enabled = 1
[sudo -n /usr/sbin/lsmod]
enabled = 1
[sudo -n /usr/sbin/lspci]
enabled = 1
[sudo -n /usr/sbin/lspcmcia]
enabled = 1
[sudo -n /usr/bin/df -h]
enabled = 1
[sudo -n /usr/sbin/pvdisplay]
enabled = 1
[sudo -n /usr/sbin/lvdisplay]
enabled = 1
[sudo -n /usr/sbin/vgdisplay -v]
enabled = 1
[sudo -n mdadm --misc –detail]
#use the parameter “fullcmd” when the command you want to run is to large for the section name.
enabled = 1
fullcmd = for i in `sudo -n cat /proc/mdstat | grep " : " |  grep -v Personalities | awk -F: '{print $1}'`; do sudo -n mdadm --misc --detail /dev/$i; done
[/sbin/swapon -s ]
enabled = 1
[free -l -b]
enabled = 1
[iostat -N -x]
enabled = 1
[dmesg]
enabled = 1
[netstat -rnv]
enabled = 1
[netstat -an]
enabled = 1
[/usr/sbin/chkconfig --list]
enabled = 1
[/usr/bin/systemctl --all --full --no-pager]
enabled = 1
[cat /etc/hosts]
enabled = 1
[default]
enabled           = 0
comment           = "default comment"
#fullcmd = <<EOT
#date
#uname -a
#who -r
#uptime
#EOT

and here is the script:

To verify the files,

  • Import the gpg key from here
  •  remove the “_.txt” ending from the file names if you downloaded the files.

then

$ gpg --verify inventory.pl.asc inventory.pl

inventory.pl
inventory.pl.asc


Leave a Reply

Your email address will not be published. Required fields are marked *

*