The Reporter Database Schema 3 - The Audit Tables
Toni_Id 060001M7HX Visits (6685)
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 http
Reporter Database Schema 2 – The Static Tables http
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:
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.
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.
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 http
Subscribe and follow us for all the latest information directly on your social feeds: