|→||1.4 IT administration|
Use a MySQL database to manage your IT
|Options to show help text, version, and license|
|Required/recommended other software|
|Configuration file itadmin.conf|
|Installing the initial database|
|Netgroup flattening in itadmin|
|Install the test database|
|The example explained|
|The users table|
|The buildings table|
|The ddspeed table|
|The VLANs table|
|The patches table|
|The computers table|
|The dhcppools table|
|Software requiring license purchase|
|The licenses table|
|The network documentation netdocu.pdf|
|The license report licenses.pdf|
|Required and recommended software FAQ|
|Where can I obtain MySQL?|
|[OpenOffice] Should I use the Java connector or the ODBC connector?|
|[Linux] How do I create an ODBC data source for use with oobase?|
|Where can I obtain the ISC DHCP server?|
|How do I run itadmin?|
|What's the "database credentials file"?|
|Netgroup related FAQ|
|Why should I use netgroups?|
|What name services are required to use netgroups?|
|How should I organize the netgroups?|
|DHCP related FAQ|
|Why should I run a DHCP server?|
|How can I grant access to DHCP address pools?|
|How many DHCP pools should I create?|
Using the itadmin program you can keep your IT information in a database. The following files can be generated from the database contents:
Building all these files from the same database ensures consistency.
You can run
pdflatex netdocu && pdflatex netdocu && pdflatex netdocu pdflatex licenses && pdflatex licenses && pdflatex licenses
to produce netdocu.pdf and licenses.pdf
Use ``pdflatex -interaction=batchmode'' instead of ``pdflatex'' to run the PDF creation less noisy.
The program produces configuration files and license report if none of the options above is specified.
0 on success, all other values indicate errors.
pdflatex netdocu pdflatex licensesthree times to produce the network documentation netdocu.pdf and the license report licenses.pdf from them.
The program uses configuration files named ``itadmin.conf''. Run
to see which files are attempted and processed in which order.
The following entries can occur in an ``itadmin.conf'' configuration file:
|none||Attempt to keep IP address for all hosts.|
|pool||Attempt to keep IP address for statically assigned IP addresses, release dynamically assigned addresses.|
|all||Release all IP addresses.|
SQL files to install the initial database are copied to /usr/share/dktools/itadmin or /usr/local/share/dktools/itadmin during installation.
You can use the it-latin1.sql file to create the initial
database structure. German users can use it-de.sql instead.
The it-utf8.sql file is experimental and untested. Someone from regions where UTF-8 encoding is needed because the latin1 character set ist not sufficient should make tests with it and give me feedback.
mysql -u root -p < /.../dktools/itadmin/it-latin1.sql
|us_s||The login name of the user (or a short
abbreviation of the name).
This field is the primary key of the table.
|us_t||Job title or academic degree, optional.|
|us_fn||Name (family name).|
|us_em||E-mail address, optional.|
|us_ko||Account number for costs controlling, optional.|
|us_se||Job ID number, optional.|
The buildings table lists buildings where computers can be placed.
|gb_s||Short name of the building (abbreviation). This field is the primary key in the table.|
|gb_l||Name of the building, optional.|
|gb_a1||First address line of the building, optional.|
|gb_a2||Second address line, optional.|
|gb_a3||Third address line, optional.|
|gb_a4||Fourth address line, optional.|
|gb_plz||ZIP code, optional.|
|gb_ort||Name of town/city, optional.|
The ddspeed table lists possible speed settings for ethernet connections.
|sp_s||Abbreviation for setting. This field is the primary key in the table.|
The vlans table lists the virtual LANS (VLANS) built on top of the physical network.
|vl_s||VLAN name. This field is the primary key in the table.|
|vl_l||Full name or description, optional.|
The patches table lists the ethernet sockets in the rooms.
|dd_n||Name of the ethernet socket. This field
is the primary key in the table.
Example: For an ethernet socket in House ``D'', connected to hub/switch/router ``A'' port number 64 one could use ``D/A/0064'' here.
|sp_s||Speed setting for the socket, optional. If this field is set, there must be an appropriate entry in the ddspeed table.|
|vl_s||VLAN the ethernet socket is assigned to, optional. If this field is set, there must be an appropriate entry in the vlans table.|
|dd_p||Hub port used by this ethernet socket, optional.|
|gb_s||Building in which the ethernet socket is placed, optional. If this field is set, there must be an appropriate entry in the buildings table.|
|dd_r||Room within the building where the ethernet socket is placed, optional.|
The networks table lists IP networks.
|nw_ip||IP class base address. This field is the primary key in the table.|
|nw_gw||Router of this net.|
|nw_bc||Broadcast address in this network, optional. The broadcast address is calculated automatically from base address and netmask if it is not specified.|
|vl_s||VLAN in which the IP network is placed, optional. If this field is used, there must be an appropriate entry in the vlans table.|
Netgroups are a mechanism to assign permissions to computers.
The same permissions can be applied to a group of computers.
If you specify computer permissions for - i.e. Samba, NFS and tcpd - directly in the configuration files you have to restart the services after making changes.
Using netgroups avoids these restarts, simply assign permissions to the netgroups in the configuration files. You can add/remove hosts to/from netgroups without having to restart daemons.
In NIS netgroups can be nested. If netgroup A is a member of netgroup B all member hosts of A are automatically members of netgroup B.
With itadmin you can use nested netgroups with LDAP too, the itadmin program resolves nested netgroups to plain host lists before writing the netgroups.ldap file.
In the IT database you can use a nested netgroup structure.
The itadmin resolves dependencies of nested netgroups before creating the netgroup.ldif file. So in netgroup.ldif each netgroup only contains hosts.
This flattening is done for two reasons:
The netgroups table lists the netgroups.
|ng_s||Netgroup name. This field is the primary key in the table.|
|ng_l||Description of the netgroup, i.e. purpose. Optional, but recommended.|
The ngdeps table configures the netgroup dependencies.
|nd_pk||Primary key in this table. Unique numeric value not of further interest.|
For both nd_p and nd_c there must be appropriate entries in the netgroups table. All member hosts of the nd_c netgroup are automatically members of the nd_p netgroup.
The computers table lists the computers.
|co_s||Short host name of the computer. This field is the primary key in the table.|
|us_s||The person responsible for the
This is not the IT people installing the software and probably not the person using the computer.
Enter the person "in charge" for the computer here. This person has budget permission to buy needed licenses for software and decides what purpose the computer is used for.
Do not enter persons just using the computer here. Do not enter persons just doing technical support.
If the field is used there must be an appropriate entry in the users table.
|co_co||Comment about the purpose of the computer, optional but recommended.|
|co_mc||MAC address (ethernet address) of the computer, optional.|
|co_ip||IP address, optional.|
|co_dd||DNS domain the computer belongs to, optional.|
|co_ff||Flag "full name first". If this flag is set, the FQDN is written to the hosts and hosts.ldif file first. Without this flag the normal (short) host name is used.|
|co_hi||Host ID, optional. Host IDs are typically found on Unix systems. Floating licenses are often bound to a host ID.|
|dd_n||Name of the ethernet socket the computer is connected to, optional. If this field is used, there must be a matching entry in the patches table.|
|gb_s||Name of the building in which the computer is placed, optional. If this field is used, there must be a matching entry in the buildings table.|
|co_r||Room number within the building, optional.|
|co_in||Inventor ID of the computer, optional.|
|co_sn||Serial number of the computer, if any. Optional.|
|ng_s||Name of the primary netgroup assigned to the computer, optional.|
This flag is 0 for all physical and virtual machines our organization is responsible for (in terms of correct licensing...).
Set it to 1 for ``bring your own device'' - computers which are not owned by the organizational unit but are allowed to use the network.
Set it to 2 for hosts owned by the organization but not within our organizational unit (i.e. the network router maintained by the IT department, hosts in neighboured departments our hosts have to communicate with...).
Set it to 3 for pure IP address/host name pairs dynamically assigned to hosts i.e. by DHCP servers, WLAN access points or VPN software.
Set it to 4 for additional network interfaces.
|co_ex||Expiration date for a host.
If a host is granted access for a limited time only, enter the end date here.
Note: The itadmin program does not force expiration, it just writes a notice that you should delete the entry from the table manually.
|dc_s||Name of the DHCP class for that computer, optional. See section DHCP for information.|
|dg_s||Name of a DHCP group for that computer, optional. See section DHCP for information.|
A DHCP service allows client computers to obtain an IP address.
When a DHCP client is booted it sends a broadcast request to obtain
IP configuration data.
The DHCP server processes the request. Depending on the data sent by the client (i.e. the clients ethernet address, host name...) it chooses an appropriate IP address and sends a response containing IP address, net mask, gateway and optionally other information back to the client.
The IP address can be either an address from an address pool or
a fixed address statically assigned to the client.
For addresses from a pool the client is allowed to use the address for a limited time (lease time). If the client doesn't renew the lease (request the IP address for yet another period) the server assumes the IP address is no longer used by the client.
In a network there might be different types of computers (i.e. in a university faculty there are staff members and students). You might decide to use different address pools for different types of computers, so you have to assign a DHCP class to computers. Use different DHCP class names for different types of computers obtaining IP addresses from different address pools.
DHCP options (additional information sent in the response, i.e. boot file and boot server) can be assigned to the server, a VLAN, an IP network, a DHCP address pool, a DHCP group or a host.
The dhcpclasses table lists the available DHCP classes:
|dc_s||DHCP class name (short name). This field is the primary key of the table.|
|nw_ip||Network the DHCP class belongs too.|
|dc_d||Long name / description, optional.|
The dhcpgroups table lists DHCP groups.
|dg_s||Name of the dhcp group. This field is the primary key of the table.|
|dg_l||Long name / description of the group, optional.|
The dhcppools table lists the available DHCP address pools.
|dp_pk||The primary key, a numeric value.|
|dp_st||Start IP address of the pool, required.|
|dp_en||End IP address of the pool, required.|
|nw_ip||Network the pool belongs to, optional. If this field is specified, there must be a matching entry in the networks table. If no entry is specified, the network is found automatically.|
|dp_al||Flag: Allow unknown clients to use the address pool.|
|dp_dn||Flag: Deny unknown clients.|
|dc_s||Name of the DHCP class which is allowed to use this address pool.|
If you want to allow only known hosts to use one pool and all other/unknown hosts to use another pool, use the following option combinations:
The dhcpoptions table can be used to assign DHCP options to the entire server, a VLAN an IP network, a DHCP address pool, a DHCP group, or a single host.
|do_pk||Primary key, numeric value, not used.|
|do_sc||The option scope type, one from ``server'', ``vlan'', ``network'', ``pool'', ``group'', or host.|
|do_sn||The option scope name: empty (NULL) for the server, a VLAN name for a vlan, an IP start address for an IP network, a pool start address for an address pool, a name for a group or host.|
|do_v||Option value, may be NULL for options not requiring a value.|
Several tables are involved in license management:
The swmanufacturers table lists software manufacturers:
|sm_s||Short name (abbreviation). This field is the primary key of the table.|
|sm_l||Full name of software manufacturer.|
The swproducts table lists products you can purchase from software manufactures: program licenses (full versions), upgrades, software insurances...:
|sw_s||Short name / abbreviation.|
Enter the full name of the software product including version number.
If the product is an upgrade license requiring a previous product from the same product line, include the word UPGRADE here.
If the product is an ``Upgrade Advantage'' allowing an upgrade from previous versions and lower product lines, include ``UPGRADE from ...'' here and list the products allowing the upgrade.
If the product is a software insurance, enter ``SA from ... until 2034-08-31'' here while in the software insurance period. Change it to ``SA from ... up to version x.y'' after the software insurance period.
``SA from Desktop 3.5 until 2034-08-31'' while the software insurance is running, ``SA from Desktop 3.5 up to Desktop 25.8'' after 2034-08-31.
Note: For all updates and insurances the full name must explain which product version(s) can be used as upgrade base and which version is the upgrade destination.
The swresellers table is currently not used by itadmin:
|sr_s||Short name / abbreviation.|
|sr_a1||Address line 1.|
|sr_a2||Address line 2.|
|sr_a3||Address line 3.|
|sr_a4||Address line 4.|
|sr_plz||ZIP code (postal code).|
|sr_ort||Town / city name.|
The licensestypes table lists the different license types:
|lt_s||Short license type name, abbreviation. This field is the primary key of the table.|
|lt_l||Full name, description.|
|lt_i||Number of licenses for floating license servers.|
The licenses table lists the licenses purchased by the organizational unit or purchased by the organization and assigned to the organizational unit:
|li_pk||A number automatically incremented. This field is the primary key of the table.|
|li_on||ID number of the purchase process within the organization or organizational unit (german: Beschaffungsauftrag, Auftragsnummer) combined with the name of the person keeping the paperwork for the purchase process (delivery note, invoice, copy of order sheet...).|
|sw_s||The software the license is for. There must be an appropriate entry in the swproducts table.|
|us_s||User the license is assigned to, or NULL
if the license was purchased bot not yet assigned to a specific
There must be an appropriate entry in the users table.
|co_s||Computer where the software is
If the field is used there must be an appropriate entry in the computers table.
|lt_s||License type. There must be an appropriate entry in the licensetypes table.|
|li_no||An optional note about the license and license usage.|
|sr_s||The software reseller short name.
If the field is used there must be an appropriate entry in the softwaresellers table.
|li_dd||Delivery date (optional).|
|li_nd||Delivery note number (optional).|
|li_di||Invoice date (optional).|
|li_ni||Invoice number (optional).|
The documentation archive contains an example database file it-example.sql and the output files produced by itadmin from this database:
mysql -u root -p < it-example.sqlAnother database named ``ittest'' is created containing the test data.
The example shows database entries for a university faculty.
We have four users in the faculty which can ``own'' computers or licenses:
The university owns several houses, the faculty has rooms in houses A and B.
Everything was left at the default values here.
Several virtual LANs are defined, the engineering faculty uses VLAN 200 only.
Here we have some sample entries for ethernet connection sockets in house A.
The swproducts table lists software we have to purchase licenses
for to use it. If the software manufacturer assigns product IDs to
the software products, you should use these IDs for the ``sw_s''
field as these IDs best describe what you purchased. If there are
no such IDs available or known to you, create an abbreviation
containing manufacturer, product abbreviation and version as good
as you can.
The softwaremanufacturers table lists the software manufacturers producing the software.
On behalf of Betty Blue (and from the money on her cost controlling account) the organization purchased licenses using the following order:
|1||SMS:001-005-28 SmartSoft Fine Desktop 2.8||4|
|2||SMS:001-018-15 SmartSoft Cool Text Editor 1.5||4|
The organizations internal purchase process ID for this purchase process is 005/2010.
So we obtained 8 licenses (4 for each product):
The first part of the license number is the purchase process ID combined with the name of the person responsible for these licenses and keeping the paper work (copy of order sheet, delivery note and invoice). The second part is the position on the order sheet. The third part is the number within the amount of licenses for the product purchased.
7 of the licenses are assigned to users (``us_s'' field is set),
the computers where the licenses are used are specified in the
Dr. Red plans to purchase a laptop later this year. He knows he will use the software products ``Fine Desktop'' and ``Cool Text Editor'' so he already reserves the licenses. The licenses are assigned to him, but the computer name is not yet known, so the ``co_s'' field is left empty. One license is not yet assigned to a user, so the next user who needs it can get it.
On behalf of Prof. Orange the organization obtained a 5 user floating license for ``Mathematic Formula 4.8''. As the order did not contain anything else we can omit the position and the counter in the ``li_on'' field. The license server is running on the file server ``fs''.
The software is installed on the computers ``pc-bl'', ``pc-or'',
and ``l-or''. For all these computers the ``us_s'' field is set to
``orange'' although ``pc-bl'' is a computer of Betty Blue.
For the client installations we do not need to fill the ``li_dd''... fields. The ``flexlm'' value in the ``lt_s'' field indicates that the number of concurrent user is always in compliance to the number of licenses purchased.
The network documentation consists of 5 sections:
The license report contains several chapters:
This program uses DK libraries version 3.
It is not available on the Windows platform.
MySQL is available at http://www.mysql.com.
The community edition server is available free of charge.
Depending on the office suite you use on your desktop you most likely need the Connector/ODBC. On Linux systems your package management should allow you to install server, client software, and connector.
I prefer the ODBC connector, it is significantly faster on my computer.
# Driver from the mysql-connector-odbc package # Setup from the unixODBC package [MySQL] Description = ODBC for MySQL Driver = /usr/lib/libmyodbc5.so Setup = /usr/lib/libodbcmyS.so Driver64 = /usr/lib64/libmyodbc5.so Setup64 = /usr/lib64/libodbcmyS.so FileUsage = 1If the section doesn't exist - add it. The file names of the shared libraries may change, i.e. if there is a new version of MySQL.
[it] Description = IT database Driver = /usr/lib/libmyodbc5.so Server = 127.0.0.1 Database = it Port = 3306 Socket = /var/lib/mysql/mysql.sock Option = 3 ReadOnly = No ServerType = MySQL charset = utf8It depends on your system whether or not you need the final ``charset'' line and which setting to choose.
Normally you do not run itadmin alone, you combine it with other
steps in a shell script.
cd /root/scripts/itadminDo not use the /etc directory.
cp hosts /etc/hosts cp ethers-by-mac /etc/ethersIt depends on your system whether to copy ethers-by-mac or ethers-by-ip to /etc/ethers. On Linux use ethers-by-mac, on Solaris use ethers-by-ip. Check your systems documentation to choose the correct file.
/etc/rc2.d/Sxxdhcp stop sleep 2 cp dhcpd.conf /etc/dhcpd.conf /etc/rc2.d/Sxxdhcp startYou need to customize the above commands.
itacldap.plYou can run the itacldap.pl script if you have Perl available on your system. Save the script as /usr/local/bin/itacldap.pl, the script must be customized before it can be used. It creates a file ``objects-to-delete''.
ldapdelete -f objects-to-deleteTypically further command line options are necessary to specify an LDAP bind DN, a password or password file, a host or URI.
ldapaddent -f hosts hosts ldapaddent -f ethers ethers ldapadd -f netgroup.ldifor
ldapadd -f hosts.ldif ldapadd -f ethers.ldif ldapadd -f netgroup.ldifThe ldapaddent command is not available on all platforms.
PDFLATEX="pdflatex -interaction=batchmode" $PDFLATEX netdocu && $PDFLATEX netdocu && $PDFLATEX netdocu $PDFLATEX licenses && $PDFLATEX licenses && $PDFLATEX licenses
When connecting to a database service, a user name and a
password is needed. This user name/password combination is not
written directly to the itadmin.conf file, a reference to a
credentials file containing user name and password is used instead.
Only the credentials file needs protection against illegal access -
set permissions to 600 so nobody else can even read it. For the
itadmin.conf file you can use relaxed permissions 644, other users
can read it as it does not contain confidential data.
A database credentials file looks like:
[client] user=... password=...
Netgroups are an elegant method to administer host-based
Without using netgroups you have to enter each host manually into access lists of daemons (i.e. Samba, ssh, tcpd...) and restart the service to apply the changes.
When using netgroups you configure the services to grant a group of hosts access to the service. The group name is written into the service configuration files. When adding a new computer to or removing a computer from a netgroup no service restart is necessary as the service configuration file was not changed and the group membership is checked at runtime when a client attempts to use a service.
You need an LDAP service to use netgroups.
If you are still using NIS, use the hostsadm script from dktools 2.x.x to produce an input file for NIS service. And plan to migrate to LDAP as soon as possible.
Netgroups can be nested, one netgroup A can be a member
of another netgroup B. This means all member hosts of
A are automatically member hosts of B too.
I suggest a two-level netgroup scheme: On the lower level hosts are grouped by type, i.e. desktop PCs, student PCs, bring-your-own-device, guest laptops... On the higher level there are groups with access to resources, the lower level groups are members of the higher level groups.
When purchasing a new PC you only have to add it to the appropriate lower level group, that's it.
When using DHCP pools in itadmin there are two variants to configure a DHCP pool:
When using itadmin, membership in DHCP classes is based on the
To allow a computer to obtain an IP address dynamically, leave the ``co_ip'' field in the computers table empty. Set the ``dc_s'' field to the name of DHCP class the computer belongs to. Set the ``co_mc'' field to the MAC address of the computer.
You can obtain the MAC address by running ``ifconfig -a'' (*x) or ``ipconfig /ALL'' (Windows).
I recommend to have at least two pools:
The purpose of these different pools is that every guest, laptop owner... must visit the network administrator to register the computer for DHCP use. Depending on your organizations policies the user has to sign a contract or is simply tought about usage policies...
This program replaces the hostsadm script from previous DK tools versions (2.x.x).