Spectrum Report Manager (SRM)

Document created by Christian_Schneider on Jan 31, 2012Last modified by SamCreek on Dec 17, 2016
Version 8Show Document
  • View in full screen mode

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

  1. Change rights of mysql to allow access from outside (see Installation Guide)
  2. Install CrystalReports Boxi on your PC to modify Reports or create new ones
  3. Download the ODBC mysql driver and install it
  4. Create a new connection to the ReportDB
    Reportdbconn.jpg
  5. 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 NameType
alarm_idvarchar(50)
activityint(10) unsigned
timedatetime
uservarchar(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 NameType
alarm_idvarchar(50)
landscape_hint(10) unsigned
model_hint(10) unsigned
orig_event_typeint(11)
conditionint(11)
causeint(11)
troubleshootervarchar(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 NameType
package_namevarchar(255)
folder_IDint(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 NameType
datedate
entity_idint(10) unsigned
outage_countint(10) unsigned
secs_outint(10) unsigned
outage_typeint(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 NameType
yearsmallint(5) unsigned
monthtinyint(3) unsigned
entity_idint(10) unsigned
outage_countint(10) unsigned
secs_outint(10) unsigned
outage_typeint(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 NameType
model_hint(10) unsigned
landscape_hint(10) unsigned
model_namevarchar(255)
model_classint(10) unsigned
create_timedatetime
mtype_hint(10) unsigned
security_stringvarchar(255)
destroy_timedatetime
vendor int(10)unsigned
device_typevarchar(32)
IPvarchar(32)
MACvarchar(32)
serial_nbrvarchar(32)
sys_descvarchar(255)
fw_revCHAR(32)
sys_OIDvarchar(255)
locationvarchar(255)
contact_personvarchar(255)
last_rebootdatetime
last_successful_polldatetime
user_char1varchar(255)
user_char2varchar(255)
user_long1int(10) unsigned
user_long2int(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 NameType
model_hint(10) unsigned
model_indexint(10) unsigned
module_namevarchar(255)
serial_nbrvarchar(32)
software_revvarchar(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 NameType
entity_IDint(10) unsigned
entity_nameCHAR(255)
current_modelint(10) unsigned
create_timedatetime
destroy_timedatetime


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 NameType
entity_group_IDint(10) unsigned
entity_group_namevarchar(32)
entity_group_typeint(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 NameType
entity_group_IDint(10) unsigned
entity_IDint(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 NameType
entity_group_typeint(10) unsigned
eg_type_descvarchar(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_typeeg_type_name
101Vendor group
102Model Class group
103Landscape group
1000User Defined group
1001User Defined group

 

entitymodel

 

Column NameType
entity_IDint(10) unsigned
model_hint(10) unsigned
timestamptimestamp(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 NameType
folder_idint(10) unsigned
child_idint(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 NameType
cs_unique_idvarchar(64)
folder_idint(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 NameType
group_idint(10) unsigned
entity_group_idint(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 NameType
datedate
entity_idint(10) unsigned
outage_countint(10) unsigned
secs_outint(10) unsigned
outage_typeint(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 NameType
yearsmallint(5) unsigned
monthtinyint(3) unsigned
entity_idint(10) unsigned
outage_countint(10) unsigned
secs_outint(10) unsigned
outage_typeint(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 NameType
statusint(10) unsigned
namevarchar(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:


StatusName
0Good
1Bad
2Unknown
3Disabled
4Unreacheable
5Init
6L inked Port Bad
7Linked Device Bad
8Dormant
9Port In Maintenance
10Bad Suppressed
11WA Link Bad
12LL In Maintenance
13Always Down


installedreports

 

Column NameType
report_IDint(10) unsigned
parent_folder_IDint(10) unsigned
file_namevarchar(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 NameType
model_hint(10) unsigned
landscape_hint(10) unsigned
model_namevarchar(255)
model_classint(10) unsigned
create_timedatetime
mtype_hint(10) unsigned
security_stringvarchar(255)
destroy_timedatetime
port_typevarchar(255)
port_descvarchar(255)
if_speedint(10) unsigned
IPvarchar(32)
MACvarchar(32)
port_link_statusint(10) unsigned
ifLastChangeint(10) unsigned
ifInOctetsbigint(20) unsigned
dateLateSignificantTrafficdatetime
ifAliasvarchar(64)
connected_devint(10) unsigned
component_OIDvarchar(255)
device_model_hint(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 NameType
outage_IDint(10) unsigned
entity_IDint(10) unsigned
start_timedatetime
end_timedatetime
outage_typeint(10) unsigned
notesvarchar(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 NameType
landscape_hint(10) unsigned
domain_namevarchar(255)
dev_sync_timedatetime
int_sync_timedatetime

 

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 NameType
outage_IDint(10) unsigned
landscape_hint(10) unsigned
start_timedatetime
end_timedatetime
outage_typeint(10) unsigned

 

This table stores the management outages for the monitored landscapes.

 

managementoutagetype

 

Column NameType
outage_typeint(10) unsigned
outage_descvarchar(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 NameType
model_hint(10) unsigned
model_namevarchar(255)
mtype_hint(10) unsigned
model_classint(10) unsigned
network_addressvarchar(32)

 

This table stores general model information.

 

modelclass

 

Column NameType
model_classint(10) unsigned
mclass_nameCHAR(32)

 

The modelclass table is filled in at the time of table creation with these record values:

 

Model Classmclass_namemodel_classmclass_name
0unknown26NT
1Other27Firewall
2Switch28IDS
3Router29Security Scanner
4Switch-Router30Anti-virus Application
5Hub31PKI System
7Link32Packet Sniffer
8Network33Syslog
9Workstation-Server36Transport Service
10Container37Generic TL1 Device
11Chassis38VoIP
12Pingable39CMTS
13MAC40Wireless
14SNMP41Cable Modem-MTA
15Port42VPN
16User43DSL
17Application44Multiplexor
18Component45SAN
19Landscape46PBX
20Router Application103Power Supply
21Switch Application104Amplifier
22Switch-Router Application105Line Monitor
23MIB Application106Test Point
24RMON Application107Fiber_node
25UNIX108HE fiber

 

modeltype


Column NameType
mtype_hint(10) unsigned
mtype_nameCHAR(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 NameType
outage_IDint(10) unsigned
entity_IDint(10) unsigned
start_timedatetime
end_timedatetime
outage_typeint(10) unsigned
notesvarchar(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 NameType
outage_typeint(10) unsigned
outage_descvarchar(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_typeoutage_desc
0Initial
1Unplanned
2Planned
3Exempt

 

pcause

 

Column NameType
cause_idint (10) unsigned
titlevarchar(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 NameType
reg_uservarchar(20)
reg_keyvarchar(100)
reg_valuevarchar(255)
reg_typeint(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 idtype name
0Boolean
1String
2Hidden
3List
4List 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:

  1. Shutdown SPECTRUM Tomcat Service
  2. In server.xml file, scroll to the bottom of the file and add the following line before < /host >.
  3. < 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.
  4. 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.

Attachments

    Outcomes