Back to:
CA Spectrum
IM Community WIKI Front Page
Forgot the Password for Report Manager
On your OneClick machine open a terminal window or a dos prompt (windows = start -> Run -> cmd)
- change to your $SPECROOT/mysql/bin directory
- do a mysql -uroot -proot to connect to the mysql database system
- do a use reporting; don't forget the ; at the end!
- do a select aes_decrypt(BOPassword, 'spectrum') from registry; again don't forget the ; at the end!
If your field says NULL you do not have set a password therefor leave it blank.
How to optimize the Report Manager Database
Run the following command-line utility to perform a wide range of database optimization and cleanup operations such as removing orphaned/unnecessary database records and cleansing database values:
RpmgrOptimizeDatabase
Command-line options for this utility include:
- checkEntities Ensures that when multiple models map to a single entity, the entity record's current model is linked to a non-destroyed (active) model.
- checkOutages Removes any non-initial outage records that do not reflect a span of time. In addition, this option is used to eliminate any overlapping outages. This overlap used to occur as a result of duplicated models; however, this problem has since been resolved.
- checkOrphans Removes orphaned interface model, device model and entity database records.
- checkPcauses Removes any trailing spaces from existing pcause titles in the database.
- suppressedAlarms Removes suppressed alarms from the alarm tables if the customer preference is to not report on suppressed alarms.
- all Performs all of the aforementioned operations.
How to generate your own report in Report Manager
- Change rights of mysql to allow access from outside (see Installation Guide)
- Install CrystalReports Boxi on your PC to modify Reports or create new ones
- Download the ODBC mysql driver and install it
- Create a new connection to the ReportDB
- The reports are found on $SPECROOT/tomcat/webapps/spectrum/WEB-INF/ and has the extension .rpt
To see the reports in the SRM-menu you'll have to do this via Boxi Admin Cosole http://reportmanager/businessobjects/enterprise11/admin/de/admin.cwr. Here you can create new fields and place new reports. You will need the password of the BOXI Administrator you have set during the installation of your SRM.
Authentication always enterprise!!
Report Manager Schema
This section describes the database schema for the Spectrum Report Manager database.
A graphical representation of the SRM database schema can be found here: SRM Schema.
Please be aware that this schema is based on a previous version of Spectrum (7.1), so there may have been changes in recent releases. Feel free to update this section if you have more recent information.
Table Descriptions
This section takes a more detailed look into each table. This section identifies the fields of the table, the proposed type, uniqueness and referential integrity requirements. Each table definition is followed by a brief description of how that table gets it data and what is done to keep that table up to date.
alarmactivity
Column Name | Type |
alarm_id | varchar(50) |
activity | int(10) unsigned |
time | datetime |
user | varchar(50) |
The alarmactivity table stores all of the alarm activity monitored by SRM. The activity field denotes the type of alarm event generated. This field can be one of the following:
- Set alarm event
- Acknowledge alarm event
- Assign troubleshooter alarm event
- Clear alarm event
- User cleared alarm event
- Assign trouble ticket alarm event
alarminfo
Column Name | Type |
alarm_id | varchar(50) |
landscape_h | int(10) unsigned |
model_h | int(10) unsigned |
orig_event_type | int(11) |
condition | int(11) |
cause | int(11) |
troubleshooter | varchar(50) |
trouble_ticket | varchar(50) |
The alarminfo table stores relevant information for an alarm. There is one entry per unique alarm id, as opposed to the alarmactivity table which can have multiple entries for a single alarm id. An entry in this table is created when a “set alarm event” is received by SRM. It is updated through the life of the alarm as each of the other alarm events are received by SRM.
contentpkg
Column Name | Type |
package_name | varchar(255) |
folder_ID | int(10) unsigned |
The contentpkg table associates content packages with Crystal Enterprise folder IDs. This table is not meant to be reported against, but instead is actually used by the Report Manager to help identify installation and security issues. A content package may only be installed once and this table helps identify if that is the case.
devicedailysummary
Column Name | Type |
date | date |
entity_id | int(10) unsigned |
outage_count | int(10) unsigned |
secs_out | int(10) unsigned |
outage_type | int(10) unsigned |
This table is defined in 7.1 SP2 but is not used. This table, in all likelihood will be removed in 7.1 SP3.
devicemonthlysummary
Column Name | Type |
year | smallint(5) unsigned |
month | tinyint(3) unsigned |
entity_id | int(10) unsigned |
outage_count | int(10) unsigned |
secs_out | int(10) unsigned |
outage_type | int(10) unsigned |
This table is defined in 7.1 SP2 but is not used. This table, in all likelihood will be removed in 7.1 SP3.
devicemodel
Column Name | Type |
model_h | int(10) unsigned |
landscape_h | int(10) unsigned |
model_name | varchar(255) |
model_class | int(10) unsigned |
create_time | datetime |
mtype_h | int(10) unsigned |
security_string | varchar(255) |
destroy_time | datetime |
vendor int(10) | unsigned |
device_type | varchar(32) |
IP | varchar(32) |
MAC | varchar(32) |
serial_nbr | varchar(32) |
sys_desc | varchar(255) |
fw_rev | CHAR(32) |
sys_OID | varchar(255) |
location | varchar(255) |
contact_person | varchar(255) |
last_reboot | datetime |
last_successful_poll | datetime |
user_char1 | varchar(255) |
user_char2 | varchar(255) |
user_long1 | int(10) unsigned |
user_long2 | int(10) unsigned |
The devicemodel table is filled in initially as the Report Manager extracts model information from the respective SpectroSERVERs. New records are added by the Report Manager as it responds to model creation events for device models.
Certain attributes of this table can change and as such the Report Manager needs a way to keep up with these changes. To keep up with these changes, the Report Manager will periodically request current values for these attributes. These requests will be made to the appropriate models via the OneClick architecture. Initially this period for updating device data will be set to once every 24 hours.
The user defined fields are to be left blank, but provide the administrator an opportunity to extend the Report Manager database to include data that is applicable to their assets.
devicemodule
Column Name | Type |
model_h | int(10) unsigned |
model_index | int(10) unsigned |
module_name | varchar(255) |
serial_nbr | varchar(32) |
software_rev | varchar(32) |
The devicemodule table captures the relationship between Chassis device models and the board modules contained within. This table is dynamically kept up to date.
entity
Column Name | Type |
entity_ID | int(10) unsigned |
entity_name | CHAR(255) |
current_model | int(10) unsigned |
create_time | datetime |
destroy_time | datetime |
The entity table is used to uniquely identify all entities that can be reported on. As new unique entities are added to the database, new entity records will be created. Entity table record creation is closely tied to devicemodel and interfacemodel table record creation. The current_model, create_time, and destroy_time columns always correspond to the most recently created model.
entitygroup
Column Name | Type |
entity_group_ID | int(10) unsigned |
entity_group_name | varchar(32) |
entity_group_type | int(10) unsigned |
The EntityGroup table is initially filled in during the startup of the Report Manager application. Queries are made to the individual SpectroSERVERs to learn of the existing model collections (which are actually models themselves).
The EntityGroup table is then kept up to date by having the Report Manager watch for the creation (and destruction) events of the collection models (model type TBD). When a new event occurs indicating the creation of one of these collection models, the name for that collection model is immediately obtained. A search of the EntityGroup table for a record with that name is performed. If no such record exists, one is immediately added. If a record does exist, no further processing is necessary.
entitygroupentity
Column Name | Type |
entity_group_ID | int(10) unsigned |
entity_ID | int(10) unsigned |
The EntityGroupEntity table is initially filled in during the startup of the Report Manager application. As EntityGroups are added to the system, queries are made back to each of the servers to determine the membership of those groups. In determining membership, the SpectroSERVERs will identify a set of models. Each model can then be referenced in the either the devicemodel or interfacemodel table. From there an entity ID can be obtained and an appropriate entry can be made into this table.
The EntityGroupEntity table can then be kept up to date by monitoring the relationship changes associated with those collection models.
entitygrouptype
Column Name | Type |
entity_group_type | int(10) unsigned |
eg_type_desc | varchar(255) |
The EntityGroupType table is filled in at the time of table creation. EntityGroupTypes are pre-defined before any EntityGroups have been defined. Table records include:
entity_group_type | eg_type_name |
101 | Vendor group |
102 | Model Class group |
103 | Landscape group |
1000 | User Defined group |
1001 | User Defined group |
entitymodel
Column Name | Type |
entity_ID | int(10) unsigned |
model_h | int(10) unsigned |
timestamp | timestamp(14) |
The entitymodel is used to identify all model handles that an entity has had. This table gets filled in as part of the Entity table updating. When a record gets added to the either the devicemodel or interfacemodel table, a process is kicked off to identify if this “new” model is either a) an existing/known entity, or b) a new (previously unknown/un-modeled) entity.
A new record gets added to the entitymodel table every time a “new” model record gets added to either the devicemodel or devicemodel table. When a record is added to the entitymodel table, the record is recorded with a timestamp. This timestamp should enable the Report Manager to identify the most current model that represents an entity.
folderhierarchy
Column Name | Type |
folder_id | int(10) unsigned |
child_id | int(10) unsigned |
This table maps an entity group of type ‘folder’ to all entity groups contained by the folder. Comparison reports will require this table.
folderidmap
Column Name | Type |
cs_unique_id | varchar(64) |
folder_id | int(10) unsigned |
This table maps the SRM entitygroupid of the folder to the CsUniqueID that identifies the folder in One Click and Spectrum. This table is used for custom collection hierarchies.
groupentitygroups
Column Name | Type |
group_id | int(10) unsigned |
entity_group_id | int(10) unsigned |
Since entity groups of type ’folder’ can be made up of multiple entity groups (of type folder and/or collection) this table allows you to find all of the entity groups that make up the specified entity group.
NOTE: When finding all entities of a specified entity group, this is the table to use, not the entitygroup table.
interfacedailysummary
Column Name | Type |
date | date |
entity_id | int(10) unsigned |
outage_count | int(10) unsigned |
secs_out | int(10) unsigned |
outage_type | int(10) unsigned |
This table is defined in 7.1 SP2 but is not used. This table, in all likelihood will be removed in 7.1 SP3.
interfacemonthlysummary
Column Name | Type |
year | smallint(5) unsigned |
month | tinyint(3) unsigned |
entity_id | int(10) unsigned |
outage_count | int(10) unsigned |
secs_out | int(10) unsigned |
outage_type | int(10) unsigned |
This table is defined in 7.1 SP2 but is not used. This table, in all likelihood will be removed in 7.1 SP3.
ipls_names
Column Name | Type |
status | int(10) unsigned |
name | varchar(32) |
This table contains the different values for the port_link_status on an interface model found in the interfacemodel table. This table is filled when it is created with the following values:
Status | Name |
0 | Good |
1 | Bad |
2 | Unknown |
3 | Disabled |
4 | Unreacheable |
5 | Init |
6 | L inked Port Bad |
7 | Linked Device Bad |
8 | Dormant |
9 | Port In Maintenance |
10 | Bad Suppressed |
11 | WA Link Bad |
12 | LL In Maintenance |
13 | Always Down |
installedreports
Column Name | Type |
report_ID | int(10) unsigned |
parent_folder_ID | int(10) unsigned |
file_name | varchar(255) |
This table contains the file names of the reports that are loaded into the CMS database by SRM. The report ID of the report stored in the database along with its parent folder ID are also listed. This table is consulted when add or updating reports within SRM.
interfacemodel
Column Name | Type |
model_h | int(10) unsigned |
landscape_h | int(10) unsigned |
model_name | varchar(255) |
model_class | int(10) unsigned |
create_time | datetime |
mtype_h | int(10) unsigned |
security_string | varchar(255) |
destroy_time | datetime |
port_type | varchar(255) |
port_desc | varchar(255) |
if_speed | int(10) unsigned |
IP | varchar(32) |
MAC | varchar(32) |
port_link_status | int(10) unsigned |
ifLastChange | int(10) unsigned |
ifInOctets | bigint(20) unsigned |
dateLateSignificantTraffic | datetime |
ifAlias | varchar(64) |
connected_dev | int(10) unsigned |
component_OID | varchar(255) |
device_model_h | int(10) unsigned |
The interfacemodel table is filled in initially as the Report Manager extracts model information from the respective SpectroSERVERs. New records are added by the Report Manager as it responds to model creation events for interface models. The models that will be reported on within the interfacemodel table will initially be limited to those models that represent physical interfaces. Logical interfaces will not be recognized or reported on with this first phase.
interfaceoutage
Column Name | Type |
outage_ID | int(10) unsigned |
entity_ID | int(10) unsigned |
start_time | datetime |
end_time | datetime |
outage_type | int(10) unsigned |
notes | varchar(255) |
The interfaceoutage table is initially filled in during the startup of the Report Manager application. After the Report Manager has learned of all the current modeled interfaces from a single SpectroSERVER, it looks for combinations of events that indicate some type of outage has occurred on these entities. Each outage then becomes a single entry in the interfaceoutage table.
After this initial “draining” of events, the Report Manager will establish watches for specific events on modeled entities. When these events occur, records in the interfaceoutage table will be added and updated appropriately.
landscape
Column Name | Type |
landscape_h | int(10) unsigned |
domain_name | varchar(255) |
dev_sync_time | datetime |
int_sync_time | datetime |
The landscape table lists those landscapes that report manager has seen. The dev_sync_time is the time the last known device event was recorded for the given landscape. The int_sync_time time is the time the last known interface event was recorded.
managementoutage
Column Name | Type |
outage_ID | int(10) unsigned |
landscape_h | int(10) unsigned |
start_time | datetime |
end_time | datetime |
outage_type | int(10) unsigned |
This table stores the management outages for the monitored landscapes.
managementoutagetype
Column Name | Type |
outage_type | int(10) unsigned |
outage_desc | varchar(32) |
This table lists the different typs of management outages. This table is filled at table creation time with the following values:
- Expected
- Unexpected
- History
model
Column Name | Type |
model_h | int(10) unsigned |
model_name | varchar(255) |
mtype_h | int(10) unsigned |
model_class | int(10) unsigned |
network_address | varchar(32) |
This table stores general model information.
modelclass
Column Name | Type |
model_class | int(10) unsigned |
mclass_name | CHAR(32) |
The modelclass table is filled in at the time of table creation with these record values:
Model Class | mclass_name | model_class | mclass_name |
0 | unknown | 26 | NT |
1 | Other | 27 | Firewall |
2 | Switch | 28 | IDS |
3 | Router | 29 | Security Scanner |
4 | Switch-Router | 30 | Anti-virus Application |
5 | Hub | 31 | PKI System |
7 | Link | 32 | Packet Sniffer |
8 | Network | 33 | Syslog |
9 | Workstation-Server | 36 | Transport Service |
10 | Container | 37 | Generic TL1 Device |
11 | Chassis | 38 | VoIP |
12 | Pingable | 39 | CMTS |
13 | MAC | 40 | Wireless |
14 | SNMP | 41 | Cable Modem-MTA |
15 | Port | 42 | VPN |
16 | User | 43 | DSL |
17 | Application | 44 | Multiplexor |
18 | Component | 45 | SAN |
19 | Landscape | 46 | PBX |
20 | Router Application | 103 | Power Supply |
21 | Switch Application | 104 | Amplifier |
22 | Switch-Router Application | 105 | Line Monitor |
23 | MIB Application | 106 | Test Point |
24 | RMON Application | 107 | Fiber_node |
25 | UNIX | 108 | HE fiber |
modeltype
Column Name | Type |
mtype_h | int(10) unsigned |
mtype_name | CHAR(32) |
The modeltype table is filled in as the Report Manager is starting up. The Report Manager contacts one of the SpectroSERVERs and send a query requesting the handle and name for all device model types. Once this query is returned, the modeltype table is updated.
outage
Column Name | Type |
outage_ID | int(10) unsigned |
entity_ID | int(10) unsigned |
start_time | datetime |
end_time | datetime |
outage_type | int(10) unsigned |
notes | varchar(255) |
The outage table is initially filled in during the startup of the Report Manager application. After the Report Manager has learned of all the current modeled devices from a single SpectroSERVER, it looks for combinations of events that indicate some type of outage has occurred on these entities. Each outage then becomes a single entry in the outage table.
After this initial “draining” of events, the Report Manager will establish watches for specific events on modeled entities. When these events occur, records in the outage table will be added and updated appropriately.
outagetype
Column Name | Type |
outage_type | int(10) unsigned |
outage_desc | varchar(32) |
The outagetype table is filled in at the time of table creation. Outage types are pre-defined before any outages occur. Table records include:
outage_type | outage_desc |
0 | Initial |
1 | Unplanned |
2 | Planned |
3 | Exempt |
pcause
Column Name | Type |
cause_id | int (10) unsigned |
title | varchar(100) |
This table provides a mapping of cause codes to their titles. It is populated as each new cause is encountered by the alarm handler.
registry
Column Name | Type |
reg_user | varchar(20) |
reg_key | varchar(100) |
reg_value | varchar(255) |
reg_type | int(10) unsigned |
This table provides a storage area for SRM to maintain different properties and attributes of the SRM application. The table stores generic mappings using key/value pairs. Registry entries can have one of the following types:
type id | type name |
0 | Boolean |
1 | String |
2 | Hidden |
3 | List |
4 | List Entry |
For release 7.1 SP002, all registry entries have a reg_user value of ‘System’. The reg_user column allows us to expand the registry to provide storage space for individual users, as well as System entries.
MySQL Error - Can't open file: 'something.MYD'. (errno: 145)
The following error may occur with a MySQL database table:
Can't open file: 'TableName.MYD'. (errno: 145)
This error usually means the 'TableName' table has become corrupt. From our experience, it appears the error commonly affects a table used for storing sessions and may occur the webs site/database has used up all allocated disk space. Additional disk space will normally need to be allocated.
Also, run the following SQL query using phpMyAdmin:
REPAIR TABLE TableName
This will often fix the table.
Missing pictures in Report Manage after upgrade
This problem can be fixed:
- Shutdown SPECTRUM Tomcat Service
- In server.xml file, scroll to the bottom of the file and add the following line before < /host >.
- < Context path="/crystalreportviewers11" docBase="/sw/bobje/bobje/enterprise11/JavaSDK/crystalreportviewers11" / >
Please note that the docBase needs to be where Business Objects is installed and must path out to crystalreportviewers11 directory. - Start SPECTRUM Tomcat Service
Alarms have title 'Unknown' in SRM
The SRM reads the alarmtitles from the PCause files on the OneClick Server. If these files are missing, an alarm gets the title 'Unknown'.
To fix the problem, you have to truncate the table 'alarmtitle' in the mysql database 'reporting':
First: stop tomcat webserver
$spec/mysql> bin/mysql --defaults-file=my-spectrum.cnf -uroot -proot reporting (root:root is default value)
mysql> truncate alarmtitle;
Then you have to reinitialised the SRM database:
$spec/bin/RpmgrInitializeLandscape.bat root root -initHist 90 -all (with the events of the last 90 days)
Be sure, that alle necessary PCause and Event files are located in the custom/Events folder!!
Start the tomcat webserver and wait.
Schedule Backup of Report Manager
This Can be done using an application Spectrum has in its bin directory.
See below for an example I use on Windows servers, same thing works on Nix platforms:
cd [SPECTRUMHOME]/bin
backupreportingDBlandscape.bat <user> <pwd> <servername>
User and password in this case is the default mysql user - root pwd root.