This document is copyright 2001 by Richard Welty. Permission is hereby granted to duplicate this document in its entirety for educational and reference purposes. For all other uses, contact the author.
MySQL is an Open Source Relational Database System which might best be described as "mid-range"; it doesn't have the full capabilities of commercial systems such as Oracle or Informix, but is quite fast and does what it does very well. Beginning with Spectrum 6.0, the Spectrum Archive Manager database (or DDM) is based on MySQL.
This has a number of significant advantages over the old Raima VistaDB which was used in prior versions of Spectrum. Key among them is the fact that the MySQL version of the DDM can be queried with standard SQL tools, allowing direct access to the Event and Statistics data.
This document is a tutorial on how to perform basic SQL operations on the 6.0 DDM database. It is unfortunately incomplete; the project I was working on that yielded this document was terminated before completion. Perhaps it will be finished someday (hopefully soon.)
The following book may be of considerable help in
managing a MySQL database:
MySQL & mSQL, Randy Jay Yarger, George Reese
& Tim King, O'Reilly, July 1999, ISBN 1-56592-434-7
The folks at Remando AB in Sweden have published a map of the DDM table structure at http://www.remando.com/spectrum/mysql/.
The following conventions are used in the examples:
Text in Courier New is entered by the userYou do not need to shut down the Archive Manager to browse the DDM, but take care not to actually modify the tables (e.g., do not issue insert, update, delete, etc. operations on the tables.)
The username & password details for the DDMdb may be found in the file "$SPECROOT/SS/DDM/.configrc". This example assumes username spectrum, password spectrum.
To start the mysql client:
$ cd $SPECROOT/SS/DDM/mysql/bin $ ./mysql -uspectrum -p ddmdb Password: spectrum mysql>
This opens up the ddmdb. MySQL can support several dbs at once; user access control is in the mysql db. You should not fool with this db until you have spent some time with a reference such as MySQL & mSQL and understand what it does and how it works. The tables in the mysql db are exceedingly easy to screw up, and when screwed up, will break all kinds of things.
To get a list of the tables in the ddmdb, try the following command:
mysql> show tables;
To see all the contents of table model_type try the command:
mysql> select * from model_type;
This will show everything in the table. if you do this for the event table with a real DDMDB, this could take years and cost millions of lives. fortunately, it is safe to control-c one of these monster queries. it will simply punt you out of the mysql client back to the shell, with no harm done.
You can use describe to see the format for a given table, e.g.:
mysql> describe model_type; +------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------+------+-----+---------+-------+ | mtype_h | int(10) unsigned | | PRI | 0 | | | mtype_name | varchar(255) | | | | | +------------+------------------+------+-----+---------+-------+ 2 rows in set (0.01 sec)
The description shows the name of each field (column) in the model_type table, along with its type and whether or not it is a key, and whether there is a default value for the field.
You can pick out a specific row from the table with a where clause. The following example shows how to find the entry in the model_type table for a DLCI_Port:
mysql> select * from model_type where mtype_name = 'DLCI_Port'; +----------+------------+ | mtype_h | mtype_name | +----------+------------+ | 28835844 | DLCI_Port | +----------+------------+ 1 row in set (0.00 sec)
Select * means select all columns, from model_type means from the model_type table, and where provides a restriction on what is selected, in this case to a single row, since there is only one model type named DLCI_Port.
Now that we know the DLCI_Port model type handle, we can use it to select the actual DLCI_Port models.First, what are the fields in the model table?
mysql> describe model; +-------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+------------------+------+-----+---------+-------+ | model_h | int(10) unsigned | | PRI | 0 | | | landscape_h | int(10) unsigned | | | 0 | | | mtype_h | int(10) unsigned | | | 0 | | | model_name | varchar(255) | | | | | +-------------+------------------+------+-----+---------+-------+ 4 rows in set (0.01 sec)
Now, let's see all the DLCI_Ports in the model table:
mysql> select * from model where mtype_h = 28835844; +---------+-------------+----------+--------------------------+ | model_h | landscape_h | mtype_h | model_name | +---------+-------------+----------+--------------------------+ | 7340825 | 7340032 | 28835844 | FOO1-1-002_2.33 | | 7341081 | 7340032 | 28835844 | LKEN-8-001_5.30 | | 7341082 | 7340032 | 28835844 | LKEN-8-001_5.31 | | 7341083 | 7340032 | 28835844 | LKEN-8-001_5.32 | | 7341084 | 7340032 | 28835844 | LKEN-8-001_5.33 | | 7346966 | 7340032 | 28835844 | WTCH-75-001_3.16 | | 7347011 | 7340032 | 28835844 | WTCH-279-001_3.16 | | 7347012 | 7340032 | 28835844 | WTCH-279-001_3.100 | | 7347040 | 7340032 | 28835844 | WTCH-73-001_3.16 | | 7347157 | 7340032 | 28835844 | PSB1-265-001_2.16 | | 7347158 | 7340032 | 28835844 | PSB1-265-001_2.19 | ... | 7364440 | 7340032 | 28835844 | BY1B-20-001_1.19 | | 7364441 | 7340032 | 28835844 | BY1B-20-001_1.22 | +---------+-------------+----------+--------------------------+ 397 rows in set (0.09 sec)
Note that a systematic model naming convention can be a tremendous help. In this case, the first four letters of each model_name correspond to customer identification; the integer immediately following is site identification, and the integer after that corresponds to device@site. the text from _ on is interface identification generated automagically by Spectrum.
Thus PSB1-265-001_2.16 identifies a router interface for customer "PSB1" at site 265; it is device #1 at that site.
This one was designed interface cleanly with a Clarify based ticketing system. Other ticketing systems such as Remedy will likely have very different requirements. Your model naming conventions should suit your needs, but to get meaningful reports in complex environments, they need to be well conceived.
Now, let's see all the DLCI_Ports for customer "PSB1". In a where clause, the RLIKE operator can be used for regular expression matches.
mysql> select * from model where mtype_h = 28835844 and model_name RLIKE '^PSB1.*$'; +---------+-------------+----------+--------------------+ | model_h | landscape_h | mtype_h | model_name | +---------+-------------+----------+--------------------+ | 7347157 | 7340032 | 28835844 | PSB1-265-001_2.16 | | 7347158 | 7340032 | 28835844 | PSB1-265-001_2.19 | | 7347159 | 7340032 | 28835844 | PSB1-265-001_2.20 | | 7347160 | 7340032 | 28835844 | PSB1-265-001_2.22 | | 7347161 | 7340032 | 28835844 | PSB1-265-001_2.50 | | 7347506 | 7340032 | 28835844 | PSB1-262-001_2.16 | | 7347507 | 7340032 | 28835844 | PSB1-262-001_2.19 | | 7347508 | 7340032 | 28835844 | PSB1-262-001_2.20 | | 7347509 | 7340032 | 28835844 | PSB1-262-001_2.50 | | 7347965 | 7340032 | 28835844 | PSB1-264-001_2.16 | | 7347966 | 7340032 | 28835844 | PSB1-264-001_2.19 | | 7347967 | 7340032 | 28835844 | PSB1-264-001_2.20 | | 7347968 | 7340032 | 28835844 | PSB1-264-001_2.50 | ... | 7362102 | 7340032 | 28835844 | PSB1-273-001_17.23 | | 7362631 | 7340032 | 28835844 | PSB1-259-001_12.16 | | 7362632 | 7340032 | 28835844 | PSB1-259-001_12.17 | | 7362633 | 7340032 | 28835844 | PSB1-259-001_12.18 | | 7362634 | 7340032 | 28835844 | PSB1-259-001_12.19 | | 7362635 | 7340032 | 28835844 | PSB1-259-001_12.20 | | 7362636 | 7340032 | 28835844 | PSB1-259-001_12.21 | | 7362637 | 7340032 | 28835844 | PSB1-259-001_12.22 | | 7362638 | 7340032 | 28835844 | PSB1-259-001_12.23 | +---------+-------------+----------+--------------------+ 141 rows in set (0.10 sec)
Individual selects are tedious; fortunately, we can chain them together. Note that the query can be multiple lines -- Mysql (or any other sql RDBMS) won't take the query and run until you enter a ";" character. Note also that there is an implied "join" of model_type and model with this query; the effective result is a synthesized table containing info from both model_type and model.
mysql> select * from model_type, model -> where model_type.mtype_name = 'DLCI_Port' -> and model_type.mtype_h = model.mtype_h -> and model.model_name RLIKE '^PSB1.*$'; +----------+------------+---------+-----------+----------+--------------------+ | mtype_h | mtype_name | model_h |landscape_h| mtype_h | model_name | +----------+------------+---------+-----------+----------+--------------------+ | 28835844 | DLCI_Port | 7347157 | 7340032 | 28835844 | PSB1-265-001_2.16 | | 28835844 | DLCI_Port | 7347158 | 7340032 | 28835844 | PSB1-265-001_2.19 | | 28835844 | DLCI_Port | 7347159 | 7340032 | 28835844 | PSB1-265-001_2.20 | | 28835844 | DLCI_Port | 7347160 | 7340032 | 28835844 | PSB1-265-001_2.22 | | 28835844 | DLCI_Port | 7347161 | 7340032 | 28835844 | PSB1-265-001_2.50 | | 28835844 | DLCI_Port | 7347506 | 7340032 | 28835844 | PSB1-262-001_2.16 | | 28835844 | DLCI_Port | 7347507 | 7340032 | 28835844 | PSB1-262-001_2.19 | | 28835844 | DLCI_Port | 7347508 | 7340032 | 28835844 | PSB1-262-001_2.20 | | 28835844 | DLCI_Port | 7347509 | 7340032 | 28835844 | PSB1-262-001_2.50 | | 28835844 | DLCI_Port | 7347965 | 7340032 | 28835844 | PSB1-264-001_2.16 | | 28835844 | DLCI_Port | 7347966 | 7340032 | 28835844 | PSB1-264-001_2.19 | ... ... | 28835844 | DLCI_Port | 7362635 | 7340032 | 28835844 | PSB1-259-001_12.20 | | 28835844 | DLCI_Port | 7362636 | 7340032 | 28835844 | PSB1-259-001_12.21 | | 28835844 | DLCI_Port | 7362637 | 7340032 | 28835844 | PSB1-259-001_12.22 | | 28835844 | DLCI_Port | 7362638 | 7340032 | 28835844 | PSB1-259-001_12.23 | +----------+------------+---------+-----------+----------+--------------------+ 141 rows in set (0.38 sec)
While use of a commonly available and well understood database has its advantages, it also presents some security problems. Basically, MySQL listens for connections on port 3306, and if steps are not taken to properly defend this port, outside parties may obtain unauthorized access to data in the DDMdb.
While any of the steps listed below will help in controlling access, it is recommend that as many of them as are feasible be applied, in order to provide some layering of defenses (after all, you never know when some well meaning network administrator is going to screw up the ACLs in your router.)
Common sense should be used in selecting the password for the Spectrum DDMdb user. This password should NOT be the same as a logon password; it should be long, avoid dictionary words, and include digits and possibly special characters. Spectrum/spectrum as used above in the examples is a worst case example of bad password selection.
Additionally, it is always the case that passing clear text passwords through insecure network infrastruture is never a good idea. If for some reason, you need to connect to a MySQL database over a public IP network, some tunnelling mechanism such as IPSec, SSH, or TLS/SSLv3 is a must.
These tables are somewhat complex, but they can be used to limit external access to your database. They are not completely perfect; for example, the host controls are premised on DNS lookups which themselves can be compromised by an attacker, but every little bet helps.
See the O'Reilly book referenced at the beginning of this document for more details.
It is highly recommended that steps be taken at at the security perimeter of any organization using Spectrum to block port 3306 for access by unauthorized users, either with a filter or ACL in a router, or in a firewall ruleset.
With Spectrum versions prior to 6.0, it was standard practice to periodcally save, initialize, and restore the DDMdb in order to recover records that otherwise would be taking up disk space. Beginning with Spectrum 6.0, Aprisma advises that this practice is no longer necessary. This advice may not be entirely correct.
The specific problem is that in databases, it is difficult to reclaim space for deleted records. Most modern databases have optimizers that make attempts to reclaim space, but these are usually somewhat less than 100% successful, leaving gaps and unbalanced indicies in their wake. MySQL may be slightly less prone to this sort of problem, primarily because the MyISAM table type used by default doesn't support transactions, which are a major reason why optimizers are generally not optimum, but even so, space will eventually be fragmented and indicies will go out of balance.
For this reason, I strongly recommend that the old practice of occasionally, after a ddm_save, initializing the database and restoring with ddm_load be continued.