Data Schema Tables

There are 14 tables and two views that comprise the Netcool Reporter data schema within a database. The tables are created and populated by the Netcool Reporter gateway. These tables are outlined in this section.

Derived Audit Tables: Severity, Acknowledge, OwnerUID, OwnerGID

These tables track state changes to key data fields within the ObjectServer, namely, Severity, Acknowledge, OwnerUID, and OwnerGID. This data feeds into predefined reports on owner and group response times, high-severity events, and detailed event audit trails.

Whenever one of these fields is populated or updated within a data record in the REPORTER_STATUS table, a corresponding entry is made in the relevant audit table. This entry includes information about the value of the old state, the new state, and the time at which the state change occurred. Some of the audit tables (for example, Severity) are designed to close an existing record and write a new one simultaneously, to assign end and start times to particular values (for example, Severity level).

All four tables are populated via a store procedure fired from the status table. When a state change occurs to one of these fields for a record located in status, a new entry is immediately recorded in the relevant audit table.

Like the three main data tables, these four tables now include ServerName and ServerSerial as fields as well as part of the unique key.

Static Data Tables: Members, Classes, Conversions, Groups, Names, Types

The static data tables hold data that has been transferred across from the ObjectServer into the reporting RDBMS. These tables hold information about the names associated with various codes, ID numbers, and other identification strings within the ObjectServer. For example, REPORTER_GROUPS identifies and associates group names with group ID numbers. REP_SEVERITY_TYPES stores the severity names associated with the six severity numbers (0-5).

These six tables are populated infrequently, so you must run the script called nco_update.sh to update the tables in the database.

Other Supporting Data Tables and Views

The db.reporter.sql script supports the predefined reports and forms that are included with Netcool Reporter 2.0, where db is either oracle, sybase, or mssql. This new script needs to be run as the user who was created to access the event data tables (default is reporter).

The tables REP_REFERENCE_DATE and REP_TIME_PERIODS support parameterization on time periods, such as 1 day, 14 days, 28 days, and so on.

In the time-parameterized reports that look back over a period of elapsed days, the reference_date has a relative starting point. Therefore, these reports rely on a variable generated by the RDBMS, as follows:

CREATE TABLE REP_REFERENCE_DATE

(REF_DATE DATE NOT NULL,

CHECK (REF_DATE IS NOT NULL));

The REP_REFERENCE_DATE table is populated with the latest date out of the REPORTER_STATUS table, as follows:

INSERT INTO REP_REFERENCE_DATE(REF_DATE) SELECT MAX(LASTOCCURRENCE) from REPORTER_STATUS ;

The selection displayed to the user needs to be placed in the following table. This table can be populated by any amount of days. The table is already populated with a number of time periods against which users can report on Netcool event data.

CREATE TABLE REP_TIME_PERIODS

(ELAPSED_PERIOD NUMBER NOT NULL,

PERIOD_NAME VARCHAR2(64) NOT NULL,

CHECK (ELAPSED_PERIOD IS NOT NULL),

CHECK (PERIOD_NAME IS NOT NULL));

The following shows population of the rep_time_periods table with values:

INSERT INTO REP_TIME_PERIODS(ELAPSED_PERIOD, PERIOD_NAME) VALUES(1,'1 Day');

INSERT INTO REP_TIME_PERIODS(ELAPSED_PERIOD, PERIOD_NAME) VALUES(7,'7 Days');

INSERT INTO REP_TIME_PERIODS(ELAPSED_PERIOD, PERIOD_NAME) VALUES(14,'14 Days');

INSERT INTO REP_TIME_PERIODS(ELAPSED_PERIOD, PERIOD_NAME) VALUES(28,'28 Days');

The parameter within the report definition is as follows:

round(REP_REFERENCE_DATE.REF_DATE) - round(REPORTER_STATUS.LASTOCCURRENCE) <= &ELAPSED_PERIOD

When the last occurrence date is within the reference date minus the parameter, then the row is included in the calculation.

The reporting view STATUS_VW supports the predefined reports that provide an audit trail of state changes to events, such as Quick Check by Serial, Quick Check by Node, and High Tally Events. This view allows the audit changes that are tracked against four separate tables (REP_AUDIT_ACK, REP_AUDIT_SEVERITY, REP_AUDIT_OWNERUID, and REP_AUDIT_OWNERGID) to be merged into a single, displayed, tabular report.

The reporting view allows null values to be assigned to the fields that are not experiencing a state change at a particular point in time, while populating the changed field(s) with the appropriate value(s).

Dynamic Tables

This section lists all the fields in each of the Netcool Reporter tables. Fields that are marked with an asterisk (*) are primary key fields.

REPORTER_STATUS Table

The fields in the REPORTER_STATUS table are described in the following table.

Table A1: Fields in the REPORTER_STATUS table

Field Name

Field Type

Field Size

Mandatory

Description

ServerName *

varchar2

64

Yes

Name of the originating server. This is used by the gateways to control propagation of alerts between ObjectServers.

ServerSerial *

number

16

Yes

Serial number of the alert on the originating server (if it has not originated on this server). This is used to control propagation of alerts between ObjectServers.

Serial

number

16

Yes

Netcool/OMNIbus serial number for item.

Identifier

varchar2

255

No

Internal hash key/table identifier.

Node

varchar2

64

No

Hostname, IP address or other name identifying where the alert originated.

NodeAlias

varchar2

64

No

Alias for node (for example, hosts which have multiple IP addresses).

Manager

varchar2

64

No

Descriptive name of manager which generated the alert.

Agent

varchar2

64

No

Descriptive name of sub manager which generated the alert.

AlertGroup

varchar2

64

No

Descriptive name of the group within the manager which generated the alert.

AlertKey

varchar2

64

No

Descriptive key with reference to the alert (for example, disk partition where a threshold was exceeded or particular port on a hub).

Severity

number

1

No

Severity of problem: 0 Clear, 1 Indeterminate, 2 Warning, 3 Minor, 4 Major, 5 Critical.

Summary

varchar2

255

No

Summary of alert/problem as human readable text to appear on the administration console.

FirstOccurrence

date

--

No

Time (in seconds from Jan. 1, 1970) when this alert was created or when polling started at probe.

LastOccurrence

date

--

No

Time when this alert was last updated by the probe.

Poll

number

16

No

Frequency of polling for this alert in seconds.

Type

number

16

No

Type of alert (4 means a Trap, 12 means a polled event).

Tally

number

16

No

Automatically maintained count of the number of inserts/updates of the alert from any source.

Class

number

16

No

Alert Class.

Location

varchar2

64

No

Not used: reserved for location identification.

OwnerUID

number

16

No

User ID of the user who is assigned to handle this alert.

OwnerGID

number

16

No

Group ID of the group that is assigned to handle this alert.

Acknowledged

number

16

No

Whether the assigned user has acknowledged the assignment.; 0 = no, 1= yes.

DeletedAt

date

--

No

The time that the alert was deleted.

OriginalSeverity

 

16

No

The original severity of the alert.

LastModified

date

--

No

The last time the event was updated.

REPORTER_DETAILS Table

The fields in the REPORTER_DETAILS table are described in the following table.

Table A2: Fields in the REPORTER_DETAILS table

Field Name

Field Type

Field Size

Mandatory

Description

ServerName *

varchar2

64

Yes

Name of the originating server. This is used by the gateways to control propagation of alerts between ObjectServers.

ServerSerial *

number

16

Yes

Serial number of the alert on the originating server (if it has not originated on this server). This is used to control propagation of alerts between ObjectServers.

Identifier

varchar2

255

No

Same as alerts.status.

AttrVal

number

16

No

Same as alerts.status.

Sequence *

number

16

No

Same as alerts.status.

Name

varchar2

255

No

Same as alerts.status.

Detail

varchar2

255

No

Same as alerts.status.

REPORTER_JOURNAL Table

The fields in the REPORTER_JOURNAL table are described in the following table.

Table A3: Fields in the REPORTER_JOURNAL table

Field Name

Field Type

Field Size

Mandatory

Description

ServerName *

varchar2

64

Yes

Name of the originating server. This is used by the gateways to control propagation of alerts between ObjectServers.

ServerSerial *

number

16

Yes

Serial number of the alert on the originating server (if it has not originated on this server). This is used to control propagation of alerts between ObjectServers.

Serial

number

16

Yes

The serial number of the item.

UserID *

number

16

Yes

User identifier to show which user made the journal entry.

Chrono *

date

--

Yes

Date of journal entry.

Text1

varchar2

255

Yes

Text for each journal entry. If an entry exceeds 255 characters, it is placed in the next field.

Text2

varchar2

255

No

See description for previous field.

Text3

varchar2

255

No

See description for previous field.

Text4

varchar2

255

No

See description for previous field.

Text5

varchar2

255

No

See description for previous field.

Text6

varchar2

255

No

See description for previous field.

Text7

varchar2

255

No

See description for previous field.

Text8

varchar2

255

No

See description for previous field.

Text9

varchar2

255

No

See description for previous field.

Text10

varchar2

255

No

See description for previous field.

Text11

varchar2

255

No

See description for previous field.

Text12

varchar2

255

No

See description for previous field.

Text13

varchar2

255

No

See description for previous field.

Text14

varchar2

255

No

See description for previous field.

Text15

varchar2

255

Yes

See description for previous field.

Text16

varchar2

255

Yes

See description for previous field.

Audit Tables

REP_AUDIT_SEVERITY Table

The fields in the REP_AUDIT_SEVERITY table are described in the following table.

Table A4: Fields in the REP_AUDIT_SEVERITY table

Field Name

Field Type

Field Size

Mandatory

Description

ServerName *

varchar2

64

Yes

Name of the originating server. This is used by the gateways to control propagation of alerts between ObjectServers.

ServerSerial *

number

16

Yes

Serial number of the alert on the originating server (if it has not originated on this server). This is used to control propagation of alerts between ObjectServers.

Serial

number

16

Yes

Serial number of item.

StartDate *

date

--

Yes

Date the alert was created.

EndDate

date

--

No

Date alert was changed.

Severity

number

16

No

Severity of problem.

State

number

16

No

Indicates whether event has been deleted; 1 = yes, 0 = no.

REP_AUDIT_ACK Table

The fields in the REP_AUDIT_ACK table are described in the following table.

Table A5: Fields in the REP_AUDIT_ACK table

Field Name

Field Type

Field Size

Mandatory

Description

ServerName *

varchar2

64

Yes

Name of the originating server. This is used by the gateways to control propagation of alerts between ObjectServers.

ServerSerial *

number

16

Yes

Serial number of the alert on the originating server (if it has not originated on this server). This is used to control propagation of alerts between ObjectServers.

Serial

number

16

Yes

Serial number of item.

StartDate *

date

--

Yes

Time when acknowledgment started.

EndDate

date

--

No

Time when acknowledgment changed.

OwnerUID

number

16

No

User ID of the user who is assigned to handle this alert.

State

number

16

No

Acknowledgment state; 1 = yes, 0 = no.

REP_AUDIT_OWNERGID Table

The fields in the REP_AUDIT_OWNERGID table are described in the following table.

Table A6: Fields in the REP_AUDIT_OWNERGID table

Field Name

 

Field Type

Field Size

Mandatory

Description

ServerName *

varchar2

64

Yes

Name of the originating server. This is used by the gateways to control propagation of alerts between ObjectServers.

ServerSerial *

number

16

Yes

Serial number of the alert on the originating server (if it has not originated on this server). This is used to control propagation of alerts between ObjectServers.

Serial

number

16

Yes

Serial number for item.

StateChange *

date

--

Yes

Time that group data was changed.

OldOwnerGID

number

16

Yes

Previous group ID.

OwnerGID

number

16

Yes

Current group ID.

REP_AUDIT_OWNERUID Table

The fields in the REP_AUDIT_OWNERUID table are described in the following table.

Table A7: Fields in the REP_AUDIT_OWNERUID table

Field Name

Field Type

Field Size

Mandatory

Description

ServerName *

varchar2

64

Yes

Name of the originating server. This is used by the gateways to control propagation of alerts between ObjectServers.

ServerSerial *

number

16

Yes

Serial number of the alert on the originating server (if it has not originated on this server). This is used to control propagation of alerts between ObjectServers.

Serial

number

16

Yes

Serial number of item.

StateChange *

date

--

Yes

Time that owner data was changed.

OldOwnerUID

number

16

Yes

Who used to own the alert.

OwnerUID

number

16

Yes

Who now owns the alert.

Static Tables

REPORTER_NAMES Table

The fields in the REPORTER_NAMES table are described in the following table.

Table A8: Fields in the REPORTER_NAMES table

Field Name

Field Type

Field Size

Mandatory

Description

Name *

varchar

64

Yes

Name of the user.

OwnerUID

number

16

Yes

User ID.

OwnerGID

number

16

Yes

Maps to OwnerGID field in REPORTER_GROUPS table.

Password

varchar

64

No

Password for the user.

Type

number

16

Yes

Type of user.

REPORTER_GROUPS Table

The fields in the REPORTER_GROUPS table are described in the following table.

Table A9: Fields in the REPORTER_GROUPS table

Field Name

Field Type

Field Size

Mandatory

Description

Name *

varchar2

64

Yes

Name of group.

OwnerGID

number

16

Yes

Identifier for group.

REPORTER_MEMBERS Table

The fields in the REPORTER_MEMBERS table are described in the following table.

Table A10: Fields in the REPORTER_MEMBERS table

Field Name

Field Type

Field Size

Mandatory

Description

OwnerKey *

char

18

Yes

The unique key for the table.

OwnerUID

char

18

Yes

Maps to OwnerUID field in REP_AUDIT_OWNERUID table.

OwnerGID

char

18

Yes

Maps to OwnerGID field in REPORTER_GROUPS table.

REPORTER_CLASSES Table

The fields in the REPORTER_CLASSES table are described in the following table.

Table A11: Fields in the REPORTER_CLASSES table

Field Name

Field Type

Field Size

Mandatory

Description

Class *

number

16

Yes

Unique key field for the table; Class's numeric value.

Name

varchar2

64

Yes

Name of class.

Icon

varchar2

64

No

Path and file name of default icon for tools.

Menu

varchar2

64

No

Name of tools menu (in objmenus table) for the menu associated with this class for tools to be able to display the appropriate menu.

REPORTER_CONVERSIONS Table

The fields in the REPORTER_CONVERSIONS table are described in the following table.

Table A12: Fields in the REPORTER_CONVERSIONS table

Field Name

Field Type

Field Size

Mandatory

Description

Conversion_Key *

varchar2

64

Yes

Unique key for the table. Internal sequencing string (comprised of Colname and Value).

Column_Name

varchar2

64

Yes

Name of column this conversion is appropriate for.

Value

number

16

Yes

Numeric value for conversion.

Conversion

varchar2

64

Yes

String value for conversion.

REP_SEVERITY_TYPES Table

The fields in the REP_SEVERITY_TYPES table are described in the following table.

Table A13: Fields in the REP_SEVERITY_TYPES table

Field Name

Field Type

Field Size

Mandatory

Description

Severity *

number

16

Yes

Severity number.

Name

varchar

64

Yes

Severity name, for example “Clear”.

REP_TIME_PERIODS Table

The fields in the REP_TIME_PERIODS table are described in the following table.

Table A14: Fields in the REP_TIME_PERIODS table

Field Name

Field Type

Field Size

Mandatory

Description

Elapsed_Period *

number

16

Yes

Days elapsed values from 1 to 365.

Period_Name

varchar2

64

Yes

Elapsed period text in the format n days.

Database Triggers

The following database triggers are provided for use with the tables. These record an audit trail of event modifications. The following table shows the names of the triggers and the valid database types.

Table A15: Database Triggers

Trigger

Database Types

REP_AUDIT_INS

MSSQL and Sybase.

REP_AUDIT_UPD

MSSQL and Sybase.

REP_AUDIT_ACKNOWLEDGE

MSSQL, Sybase and Oracle.

REP_AUDIT

Oracle only.

DBMONITOR_TRIGGER

Oracle only.

Stored Procedures

Stored procedures are activated by a trigger. The following table shows the names and descriptions of the stored procedures. These are valid for all database types unless specified in the description.

Table A16: Stored Procedures

Stored Procedure

Description

ACKNOWLEDGED

This is used to record each acknowledgement.

DELETEDAT

This is used to record the last status after a delete.

OWNERGID

This is used to record the user group ID details.

OWNERUID

This is used to record the user ID details.

SEVERITY

This is used to record the changes in severity.

STARTMONITOR

This is used to submit the database monitoring job (Oracle only).

RUNMONITOR

This is used to start the database monitoring job. (Oracle only).