Writing Audits to the CCLAS 6 Database

Overview

CCLAS 6 can be configured to write both job and table audits to the CCLAS 6 database.

  • Job audits are records of changes made to an entity associated with a job, including the job itself, job schemes, job scheme analytes, samples, sample schemes, samples scheme analytes, samples scheme analyte readings, job invoices, job biofields, sample biofields, report requests and invoices.

  • Table audits are records of changes made to a CCLAS 6 database table.

Note: Where audits are kept in the CCLAS 6 database, over time this can drastically impact both the size of the database and the speed of accessing data from it, hence the ability to kept audits in an Elasticsearch repository known as the Universal Audit.

Process

Writing Auditing to the CCLAS 6 Database

Essentially, where writing audits to the Universal Audits is not configured, then both job and table audits can be written to the CCLAS 6 database.

Configuring Job Audits

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 and columns audited for changes are configured in the MSEADR—Audit Rule application.

Maintain audit rules

An audit rule specifies the Table Name, Description and operations for which audits are made:

  • Audit Creates

  • Audit Updates

  • Audit Deletes.

The field-level audit functionality assists laboratories in reviewing and validating that procedures are followed and allows auditors to apply reasons and comments for changes against the audits being reviewed.

Rules for a table audit can be simple or complex, such that, either:

  • All fields on the table are audited, for the indicated operations: Create, Update and/or Delete, or
  • Only fields specified on the Column Inclusions or Column Conditions tabs are audited for the indicated operations of Create, Update and/or Delete:

    • Column Inclusions—An audit record for the table only comprises data from each specified Column Name.

      Where no column inclusions are specified, then an audit contains data from all columns.

      Where a column inclusion is specified, then the column's name is included in the audit message.

    • Column Conditions—An audit record for the table is only written for an indicated operation where a specified Column Name contains data matching the Column Value.

      Where no column conditions are specified, then an audit record for the table is written regardless of the data within the record.

      Where a column condition is specified, then the column's name and value is included in the audit message.

      Where column conditions are specified, then all specified conditions must evaluate to true for auditing of the operation on the table to occur. For example, only audit changes where the Organisation Code is GLOBAL.

Limiting the audit trail to just those tables that require change tracking helps to reduce the number of audits to more manageable levels. Therefore, determine which columns, and the types of events on those columns, that require auditing. For example:

  • A Create event may or may not need auditing—one view may be that if a record is created in a table, then an audit of that creation event may not be considered important, whereas another view may be that the audit table should contain all history of the record. Also, consider whether all fields require auditing upon record creation.
  • An Update event should be audited—again, define which field changes should trigger an audit.
  • A Delete event should be audited, but given that complete reconstruction of a deleted record is difficult, then refine which columns in the record should be audited to provide a clue as to the delete record.

Whilst all tables in the system can be turned on for auditing, it is not recommended that any job-related table is audited using table auditing, as their volume of changes would definitely impact performance. For this reason, job-related actions are audited using the CCJAUD—Job Audit application.

The tables that can be enabled for auditing are:

  • CCACCREDITATIONBODY
  • CCADDRESS
  • CCBIOFIELD
  • CCCANNEDCOMMENT
  • CCCATEGORY
  • CCCATEGORYTYPE
  • CCCLIENT
  • CCCLIENTPROJECT
  • CCCLIENTQUOTE
  • CCCLIENTTAX
  • CCCONTACT
  • CCCONTACTREPORTMETHOD
  • CCCONTAINERTYPE
  • CCCOSTCENTRE
  • CCDEVICE
  • CCDEVICEREPORTTYPE
  • CCERS
  • CCERSITEM
  • CCHAZARD
  • CCHOLIDAY
  • CCINSTRUMENT
  • CCINSTRUMENTCONNECTION
  • CCINSTRUMENTSCHEMECAP
  • CCINSTRUMENTSCHEDULE
  • CCLINEOFBUSINESS
  • CCLOCATION
  • CCPRICEADJUSTMENT
  • CCPRICEBOOK
  • CCPRICECAT
  • CCPRICECATGP
  • CCPRICECATGPMEM
  • CCPRICECODE
  • CCPRICESCHEDULE
  • CCPRICESCHITEM
  • CCQUOTE
  • CCQUOTESCHEMEANALYTE
  • CCSAMPLEHANDLING
  • CCSAMPLEPOINT
  • CCSAMPLETYPE
  • CCSCRIPT
  • CCUSER
  • CCUSERGROUP
  • CCUSERGROUPMEMBER

Once a rule is defined, the system begins capturing audits for the rule. Audit rules changed do not take affect until you log out and back in again. Audits are written to the AUDIT_DATA table, and consist of before and after states of all changed fields on the record.

Limitations

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

Format of Audited Data

Audited data is written to the AUDIT_DATA table. The following are columns on this table:

Column name Comment

UUID

 

ACTION

CREATE, DELETE, UPDATE

AFTER

The value after operation.

BEFORE

The value before operation.

COLUMN_LENGTH

 

COLUMN_NAME

The name of column containing value.

COLUMN_PRECISION

 

COLUMN_SCALE

 

COLUMN_TYPE

The type of column data: integer, big_decimal, string, timestamp

CREATION_TIMESTAMP

Date-time when the operation occurred.

CREATION_USER

Code of the user performed the operation.

RECORD_KEY

The key of the record that the operation was performed upon.

TABLE_NAME

The table containing the record that the operation was performed upon.