Data Change Auditing

Overview

In BBj 13.0 and higher, Data Change Auditing is the process of building an audit trail via Change Audit Jobs. Each job consists of the following:

A job name
A location for the audit log database(s)
A list of one or more directories and/or data files to be monitored
The frequency at which the log database should rollover

When an application changes a monitored data file using direct file access calls from a BBj program or via SQL, the auditing system logs the change and type of change to an “audit log database.” At any time, an administrator can query the log database using the interface built into the Enterprise Manager or query the log database tables directly using SQL.

When using change auditing, overhead is typically minimal since all auditing occurs asynchronously via a background process, so file operations don't need to wait for audit logging to complete before proceeding.

Create an Audit Logging Job

To create an audit logging:

In the Enterprise Manager, navigate to File System > Audit Logging Jobs.
On the Audit Logging Jobs panel, click the [+] button to open the new job wizard.

Basic Information

Audit Job Name	Description
DB Root Directory	The location used to store the audit logging databases. This must be an initially empty directory with write permissions for the user running BBjServices. Logged operations are written to the active audit database which is a BBj database containing a number of standard tables accessible via SQL. The name of each audit database contains the name of the job followed by the date the audit database was created and became active (see Viewing Audit Log Data below).
Job Type	Audit an entire configured database which includes the data dictionary files, data files, stored procedure definitions, etc. or audit a manually configured list of data files and directories.

Database To Audit

If a “Audit a Named Database Instance” was selected on the first page of the wizard, select the database to audit. On subsequent pages, the wizard provides the option to modify the list of files being audited by adding to the included files list or adding items to the excluded files list.

Other Job Settings

Rollover Type	How often the audit database should rollover to a new database. When rollover occurs, the existing audit database is left in tact and a new database is created and becomes active. Rollover frequency options are none, daily, weekly, monthly, or yearly.
Rollover Frequency	Number of units before rolling over to a new audit database. For example, for rollover type weekly, and a rollover frequency of 2, the audit database would rollover every 2 weeks.
Advisory Locking	Should the audit job use advisory locking.

Included Directories and Files

Provide the list of directories and/or files to be audited. Any file or directory in the list will be monitored for write operations (read operations are not monitored). When a WRITE or REMOVE BBj call occurs, or an INSERT, UPDATE, or DELETE SQL operation occurs on a monitored file, that information is written to the audit database. This list is updatable after the job has been created.

Excluded Subdirectories and Files

The Excluded Subdirectories and Files page provides a way to exclude certain files and directories from being audited. This list can be updated after the job has been created.

Audit Jobs and Current Status

The Audit Logging Jobs page displays a list of all currently configured audit jobs and their current status.

Name	Displays the unique name of the job.
Processed Through	Shows the last time the audit job was caught up in processing audited operations.
Status	Indicates whether the job is running or if an error has occurred that needs attention.

Editing an Existing Job

To edit an existing job, double-click on the job in the Audit Logging Jobs list. Note that not all settings are updatable after creating the job.

Please see the above section Create an Audit Logging Job for information regarding each item on this page. Once changes are saved to the existing audit job, the changes will take effect without the need to restart BBjServices.

Viewing Audit Log Data

Audit logging data resides in a BBj ESQL database so there are two ways to access the data: the Audit Log Viewer in the Enterprise Manager, or querying the database directly using SQL. To access the data via SQL, locate the audit database in the standard list of databases, making note of the date extension. The auditing system logs each type of operation to a different table in the audit database. However, the Audit Log Viewer makes it easier to quickly search and access this data without the need for SQL.

To access the audit log viewer for a job:

Select the job from the list of audit jobs.
Click the magnifier button in the toolbar to open the viewer.

Log Database	The specific audit log database to query. This provides the ability to view information from past audit databases still on the system.
File	Optionally limit the query to look at audit information for a particular file being audited. Leave it blank to include data from all files being audited.
User(s)	Optionally limit the query to one or more users. Use the [ ... ] button to open a list of all user accounts to choose from.
Operation Type	Optionally limit the query to look for specific types of operations. For example, select DELETE and REMOVE to only display SQL DELETE and BBj REMOVE operations. Leave this field blank to include all write operations. Use the [ ... ] button to open a list of operation types.
Start/End Date	Specify the range of dates to examine.
Timestamp	Displays the date and time the audited operation took place.
User	Displays the user who performed the audited operation.
File	Displays the file on which the operation occurred.
Operation Type	Displays the type of operation which occurred.
Program	In BBj 17.12 and higher, if the operation was performed from a BBj program (that is, not from a 3rd party ODBC or JDBC application), this indicates the name of the program.

Operation Type Examples

In addition to general information about each audited operation, the viewer provides drill-down support to further investigate the details of each change. Double-clicking on an operation in the viewer opens additional information displaying the record details.

INSERT_RECORD

The INSERT_RECORD operation displays the details of the operation including the string template that defines the data in the file (if derivable from a database definition) as well as the record data for each column in the INSERT/WRITE.

UPDATE_RECORD

The UPDATE_RECORD details display the same general information as the INSERT_RECORD. However, since an update of the data indicates existing data was changed, the details show the old values as well as the new values after the change occurred. The display makes it easy to locate any values that changed by highlighting them in green.

DELETE_RECORD

The DELETE_RECORD operation displays the primary key value. Note that it does not display the entirety of the deleted data.

Benefits of an SQL Log Database

Using a BBj ESQL database for the log gives the administrator the ability to configure user access permissions to the audit database in the same way one would configure user permissions on any other BBj database. In addition, using iReport or BBJasper enables administrators to create more robust, limited, and/or customized reporting for others to view in an external application without the need to grant them access to the Enterprise Manager.