Writing Audits to the Universal Audit

• Writing Audits to the Universal Audit
  • Overview
  • Process
    • Turning on Auditing to the CCLAS 6 Database
    • Configuring Operational Data Auditing
      • Depth of Job Auditing
      • Limitations
      • Job Audit Details
    • Configuring Table Audits

Overview

CCLAS 6 can be configured to write both operational and static data audits in an Elasticsearch repository known as the Universal Audit, which is supported by a robust and scalable search and analytics engine.

Process

Turning on Auditing to the CCLAS 6 Database

Refer to the Universal Audit documentation supplied in the S3 bucket on your CCLAS 6 server. Essentially:

• Universal Audit is designed to replace the existing audit components of CCLAS 6. With Universal Audit, all audit data is stored in Elasticsearch, a robust and scalable search and analytics engine. This makes audit data easily accessible and searchable through a newly built HTML5 Web front-end.

• In addition to replacing CCLAS 6 audit components, Universal Audit can also seamlessly migrate existing audit data from the CCLAS 6 Oracle database to Elasticsearch. This ensures that no audit data is lost during the transition to Universal Audit.

• By default, writing to the Universal Audit is disabled when a CCLAS 6 environment is deployed. To enable an environment for Universal Audit, the environment must be deployed with the uaudit.enabled=true setting in the cclas.yml file in the Vars folder of the playbook, as follows:

  -Duaudit.enabled=true

• The following tables in the CCLAS 6 database are used to facilitate Universal Audit:

  • CCJOBAUDIT2—Job Audit events

  • TX_META—Transaction meta details.

• Oracle events are captured and transmitted to the Universal Audit using connector, schema registry, event streaming, event transformer, return messaging and database clean up technologies.

• Job audit and table audit messages are ultimately archived in Avro format to Azure Blob storage. The platform employs two types of indexes that are differentiated by their prefixes consisting of the year and month. These are:

  • [yyyy_mm]_job_audit index e.g: 2023_01_job_audit

  • [yyyy_mm]_table_audit index e.g: 2023_02_table_audit.

• Both indexes serve to store specific types of audit events. The job audit index is used to track changes to job related activities, and the table audit index stores changes to table related activities.

• The backup process to a secure cloud platform is automatic and ensures that the audit information is not lost in the event of a system failure or outage.

• To optimise performance, the Universal Audit process automatically deletes audits from CCLAS 6 database after the audits have been backed up. For database cleaning, the system uses a logic that involves verifying the Azure blob backup folder for any new records since the last check. Whenever a backup record is identified, its ID is extracted and utilised to update the corresponding record's IS_PROCESSED flag in either the CCJOBAUDIT2 or TX_META table to 'Y', indicating that the record has been processed and backed up to Azure blob. This methodology ensures that the system maintains precise and current information without retaining unnecessary processed records in the database. This process results in the freeing up of valuable space in the CCLAS 6 database, leading to improved system performance over time.

Configuring Operational Data Auditing

Depth of Job Auditing

The depth of auditing for changes to jobs, job stakeholders, job invoices, job biofields, job schemes, job scheme analytes, job paperwork and labels, samples, sample biofields, sample schemes, sample scheme analytes, sample specifications, sample portions and linked samples associated with the job, and report requests and invoices containing the job, is driven by the JOB_AUDIT_LEVEL application preference:

• 0—No job audit records are written.

• 1—A basic set of job audit records are written.

• 2—A verbose set of job audit records are written, though excludes SSA and cascade changes.

• 3—The set of job audit records written include SSA and cascade changes.

Set the JOB_AUDIT_LEVEL application preference

Limitations

When considering actions undertaken by scripts, then only calls made to the CCLAS API services trigger job audits. Changes made to the system using direct SQL queries do NOT trigger job audits.

Job Audit Details

Audit event types are entered as System Table Codes with a Table Type of CC05—Audit Type, as configured in the MSETBL—Table Code Service application.

The job-related entity associated with an operational audit is identified with a code from the System Table Codes under the Table Type of CC08—Related Job Object Type, as configured in the MSETBL—Table Code Service application. For example "J", "SS", "JI" indicates a job, sample scheme or job invoice entity, respectively.

The events causing an operational audit is identified with a code from the System Table Codes under the Table Type of CC42—Audit Events, as configured in the MSETBL—Table Code Service application.

The application raising an operational audit event is identified with a code from the System Table Codes under the Table Type of CC43—Audit Application Definitions, as configured in the MSETBL—Table Code Service application.

Configuring Table Audits

The tables whose changes are sent to the Universal Audit are decided using server-level configuration, either by an inclusion or exclusion list in the Debezium connector JSON configuration file:

• table.include.list—The connector captures changes only from the specified tables.

• table.exclude.list—The connector captures change events from any table that is not specified in this exclude list.

Either table.include.list or table.exclude.list can be configured, but not both.

The table format is <schemaName>.<tableName>.

Example of a Debezium connector configuration snippet:

Debezium connector config snippet:

{

"name": "debezium-connect",

"config": {

"connector.class": "io.debezium.connector.oracle.OracleConnector",

"table.exclude.list": "cclas68.AUDIT_DATA,cclas68.CCJOBAUDIT"

}

}

The following tables should be typically excluded:

• QRTZ_SCHEDULER_STATE

• AUDIT_DATA

• CCJOBAUDIT

Refer to the Universal Audit documentation supplied in the S3 bucket on your CCLAS 6 server for more information on configuring the Oracle database for Universal Audit of tables.