IBM Support

The Reporter Database Schema 3 - The Audit Tables

Technical Blog Post


Abstract

The Reporter Database Schema 3 - The Audit Tables

Body

This is the third post in a series of three, which look at the Reporter Database Schema and its tables and columns.

The other posts are:

Reporter Database Schema 1 – The Dynamic Tables https://ibm.biz/BdjgV8

Reporter Database Schema 2 – The Static Tables https://ibm.biz/Bdjh3Z

 

This section covers the Audit tables and the triggers and stored procedures in the schema.

 

The Derived Audit Tables: Severity, Acknowledge, OwnerUID, OwnerGID

 

The Audit tables act as second level tables for the gateway. They are not directly accessed by the gateway, but are populated off the Reporter_Status table.

 

These tables track state changes to key data fields within the ObjectServer, i.e, 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 old state, the new state, and the time at which the state change occurred. The State field in the audit tables tracks the state of the data fields and not the state of their value.

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 stored 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. The triggers and stored procedures are covered below also.

Like the three main data tables, the four Audit tables include ServerName and ServerSerial as fields.

They are listed below:

 

REP_AUDIT_SEVERITY

 

Field Name

Field Type

Field Size

Mandatory

Index Field

Description

ServerName

varchar2

64

Yes

Yes

Same as alerts.status

ServerSerial

number

16

Yes

Yes

Same as alerts.status

Serial

number

16

No

No

Same as alerts.status

StartDate

date

-

Yes

No

Date the Severity level started.

EndDate

date

-

No

No

Date the Severity level was changed.

Severity

number

4

No

No

Severity of the problem.

State

number

4

No

Yes

Indicates whether event has been deleted;

1= yes, 0 = no.

 

REP_AUDIT_ACK

Field Name

Field Type

Field Size

Mandatory

Index Field

Description

ServerName

varchar2

64

Yes

Yes

Same as alerts.status

ServerSerial

number

16

Yes

Yes

Same as alerts.status

Serial

number

16

No

No

Same as alerts.status

StartDate

date

-

Yes

No

Time when acknowledgment started.

EndDate

date

-

No

No

Time when acknowledgment changed.

OwnerUID

number

16

No

No

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

State

number

4

No

Yes

Indicates whether event has been acknowledged;

1= yes, 0 = no.

 

 

REP_AUDIT_OWNERGID

Field Name

Field Type

Field Size

Mandatory

Index Field

Description

ServerName

varchar2

64

Yes

Yes

Same as alerts.status

ServerSerial

number

16

Yes

Yes

Same as alerts.status

Serial

number

16

No

No

Same as alerts.status

StateChange

date

  •  

Yes

No

Time that group data was changed

OldOwnerGID

number

16

Yes

No

Previous group ID.

OwnerGID

number

16

Yes

No

Current group ID

 

REP_AUDIT_OWNDERUID

Field Name

Field Type

Field Size

Mandatory

Index Field

Description

ServerName *

varchar2

64

Yes

Yes

Same as alerts.status

ServerSerial *

number

16

Yes

Yes

Same as alerts.status

Serial

number

16

No

No

Same as alerts.status

StateChange *

date

  •  

Yes

No

Time that owner data was changed

OldOwnerUID

number

16

Yes

No

Who used to own the alert.

OwnerUID

number

16

Yes

No

Who now owns the alert.

 

 

 

 

The 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.

Trigger

Database Type

REP_AUDIT

Oracle

REP_AUDIT_ACKNOWLEDGE

Oracle

REP_AUDIT_INS

Sybase, MSSQL

REP_AUDIT_UPD

Sybase, MSSQL

REP_AUDIT_INSERT

Informix, DB2

REP_AUDIT_UPDATE_SEVERITY

Informix, DB2

REP_AUDIT_UPDATE_DELETEDAT

Informix, DB2

REP_AUDIT_UPDATE_OWNERUID

Informix, DB2

REP_AUDIT_UPDATE_OWNERGID

Informix, DB2

REP_AUDIT_UPDATE_ACK

Informix, DB2

REP_AUDIT_ACK

Informix, DB2

REP_AUDIT_INSERT

Informix, DB2

 

 

The Stored Procedures

The 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.

Procedure

Description

ACKNOWLEDGED

Used to record each acknowledgement made to a record in the reporter_status table.

DELETEDAT

Used to record the last status after a delete of a record in the reporter_status table.

OWNERGID

Used to record the user group ID details if the group id of a record is changed.

OWNERUID

Used to record the user ID details if the User id of a record is changed.

SEVERITY

Used to record the changes in severity of a record in the reporter_status table.

 

We hope this information will prove useful for users wishing to get an overview of how the Schema is put together in order to create report queries that are more specific to their organisation’s requirements.

 

Thank you for reading!

 

Other blogs in this series:

Historical Database Gateways - An Overview https://ibm.biz/BdjeyS
Reporter Database Schema 1 – The Dynamic Tables https://ibm.biz/BdjgV8
Reporter Database Schema 2 – The Static Tables https://ibm.biz/Bdjh3Z

 

 


image

 

Subscribe and follow us for all the latest information directly on your social feeds:

 

 

image

 

image

 

image

 

 

  

Check out all our other posts and updates:

Academy Blogs:https://goo.gl/eZjStB
Academy Videos:https://goo.gl/kJeFZE
Academy Google+:https://goo.gl/HnTs0w
Academy Twitter :https://goo.gl/DiJbvD
 


image
 

 

 

[{"Business Unit":{"code":"BU004","label":"Hybrid Cloud"},"Product":{"code":"","label":""},"Component":"","Platform":[{"code":"","label":""}],"Version":"","Edition":""}]

UID

ibm11081599