Audit Configuration Management

This document explains the audit configuration management functionality.

The audit functionality is centered around the concept of audit profiles. You define which tables and fields are audited, and when they are audited, in the context of an audit profile. To bundle profiles in the same functional area, you can relate them to audit categories. The audit trail is stored in sequence files, which are generated for each combination of company and table. Audit profiles can be exported and imported with various options to enable a quick configuration.

This document contains the following sections:

Audit Trail and Audit Host Settings These settings provide the information that is required to create the audit trails.
Audit Configuration Procedure This section provides an overview of the steps you must take to configure the audit settings. According to this configuration the audit trails are created.
Additional Functions This section contains some information about other functions, such as importing and exporting profiles, generating reports, and migrating from an earlier version of LN Tools.
How to determine the net result of the audit configuration This sections describes how you can determine the net result of the audit configuration, when the configuration is converted to run time.
General Remarks This section lists a few important facts and rules you must keep in mind.
To use audit file directories This section provides the complete online manual topic about auditing in LN Tools 7.3a. This information is provided as reference source for the migration to LN Tools 7.4a and later versions.

Audit Trail and Audit Host Settings

Before an audit trail can be created, the following information is required:

The size of the trail files ( sequence files) that are created. Define the trail file size in the Audit Trail File Sizes (ttaud3135m000) session. Because the maximum number of sequence files for each table/company combination is 999, and you cannot delete the currently active file, you must choose a file size that enables you to delete old files, while keeping at least the currently active file. The trail file size must be large enough to store the audit trail of a large transaction, else the transaction is aborted.
The path to the directory where the audit trail files are stored. Define the paths in the Audit Trail Paths (ttaud3136m000) session.
The security settings for reading, maintaining and deleting the sequence files. Define the security settings in the Audit Trail Security (ttaud3137m000) file.
The audit host settings. Define the audit hosts in the Audit Hosts (ttaud3130m000) session. Audit hosts are defined per company. Defining audit hosts is optional. If no host is defined for a company, the local system is used as host. If your system makes use of a master application server, and one or more other application servers, it is recommended to define one audit host for all audit servers. Else the transaction IDs in the audit trail may not be successive.

Note

To make these settings active, you must convert them to run time, using the Create Runtime Audit Definitions (ttaud3200s000) session, with the correct check boxes selected. Only the security settings are effective immediately, and must not be converted to run time.

Audit Configuration Procedure

This section provides an overview of the steps you must take to configure the audit settings, assumed that no audit settings are yet present.

To configure the audit settings, take the following steps, in the respective sessions:

Audit Categories (ttaud3100m000)
- Define one or more audit categories. Note that audit categories are optional. It is possible to define the audit configuration without using audit categories. You can also skip this step now, and define audit categories later.
Company Groups (ttaud3140m000)
- Define one or more company groups. Company groups enable you to configure audit settings for several companies at once. Company groups are required in several sessions. If you do not define company groups, you only have the option to specify the audit settings for all companies. Note that in the current session, you only define the names and descriptions of the company groups. To define which companies are related to the company groups, use the following session.
Companies by Company Group (ttaud3145m000)
- Define which companies are related to the company groups.
Audit Profiles (ttaud3110m000)
1. Define an audit profile, by entering a name and description.
2. Optionally, you can link the profile to a category.
3. Select whether the profile is valid for all companies or for a company group. You can only select one company group per profile.
4. Select the Active check box for the profiles you currently want to use. Only profiles with the Active check box selected, are converted to run time. Note that further on in the audit configuration process also records that contain audit settings for tables and fields can be Active, or Not Active.
5. Optionally you can attach a text that stores additional information to the profile.
Audit Tables by Profile (ttaud3120m000)
1. Define the tables that must be audited, by selecting the appropriate packages, modules and tables. Through the appropriate menu, you can also zoom to the Load Audit Tables for Profile (ttaud3220s000) session to load a list of tables, from which you can delete the tables you do not require.
2. Define whether all fields in the table must be audited, or only specified fields. Refer to the following main step if you only want to audit certain fields.
3. Define the audit type for tables you want to audit completely.
4. Select the Active check box for the records that must be converted to run time when the current profile is converted to run time.
Audit Fields by Table (ttaud3125m000)
Note that this step is optional and only required if you want to configure the audit settings for specific fields.
1. Select the fields that must be audited. Through the appropriate menu, you can also load a list of all (key) fields from which you can delete the fields you do not require. Note that the fields that are part of the primary key of the table are selected by default. Do not delete these fields, unless you are sure that a valid key is not necessary for your audit purposes.
2. Define the audit type for fields you want to audit.
3. Select the Active check box for the records that must be converted to run time when the current profile is converted to run time.
Create Runtime Audit Definitions (ttaud3200s000)
- Select the Audit Profiles check box, and click Create to convert the settings to run time.

Note

When you use auditing, make sure that enough disk space is available. This is necessary for both the Transaction Notifications (ttaud110) table and the sequence files. Additionally, give attention to the disk or disks to be used. Do not introduce performance bottlenecks, because they will impact all users that execute transactions on the database.

Additional Functions

The audit configuration management sessions provide the following additional functions:

You can export and import profiles through the following sessions:
- Export Audit Profiles (ttaud3201s000)
- Import Audit Profiles (ttaud3202s000)
- Import Audit Profile from Additional File (ttaud3203s000) This session is used for profiles that are delivered with the LN software.
You can analyze the audit profiles, and view where specific tables and fields are used, by means of the following sessions:
- Where Used Audit Tables (ttaud3521m000)
- Where Used Audit Table Fields (ttaud3526m000)
To help you to migrate to from LN Tools 7.3a to LN Tools 7.4a and later versions, you can use the Audit Configuration Migration (ttaud3204s000) session. Refer to the session Help of that session, and to the To use audit file directories section of the current document for more information.
To maintain the generated sequence files, the following sessions can be used:
- Display Audit Sequences (ttaad4560s000) Use this session to display information about the sequence files.
- Print Range of Audit Files (ttaad4461m000) and Print Range of Audit Files (Multi Lines) (ttaad4463m000) Use these sessions to print the content of sequence files.
- Transaction Notifications (ttaud1510m000) Use this session to view detailed information about all transactions in a specific company and table. Only transactions on audited tables are logged.
- Check Audit Files Integrity (ttaad4460m000) Use this session to check the integrity of the sequence files.
- Purge Audit Files (ttaad4261m000)

How to determine the net result of the audit configuration

There is no session that displays the net result of the configuration when you convert the configuration to run time. If you want to view the net result for a specific table or field, you can use the Where Used sessions, although these sessions do not specify for which companies the tables and fields are audited. However, you can also view the net result of the configuration as it is stored in the audit_cols file, which you can find in the \$BSE\lib directory. This file is easy to read. The information in this file has the following format:

<audit_cols> ::= <line>*
	<line> ::= <tables>:<companies>:<audit_type>
		<tables> ::= '*'|<package>[<module>[<table>]]
		<companies> ::= '*'|<company>[,<company>]*
			<company> ::= (0-9)+(0-9)+(0-9)
		<audit_type> ::= 'C'|'A':[<field_name>[,field_name>]*]

The code must be read as follows: The audit_cols file exists of one or more lines. Each line specifies one or more tables, one or more companies, and the audit type. The tables are specified as '*' or as a packagecode (mandatory), a module code (optional) and a table number (optional). If you specified '*', all tables are selected. If you only specifiy a packagecode, all tables in all modules are selected. If you also specify a module code, but no table number, all tables in the module are selected. The company must be specified by a '*', which specifies all companies, or by one or more company numbers. The audit type is either C or A. Audit type C only logs transactions if a field is changed. Audit type A logs transactions always, that is, if any field in the table is changed. Optionally you can specify one or more fields. If fields are specified, the audit type only applies to that fields. If no fields are specified, the audit type applies to all fields in the table. Note that fields can only be specified if the line did specify only one table.

Example

tdsls:*:C For all tables in this module (tdsls) in all companies (*), all fields are audited (no fields specified), but only if the field is changed (C). Note however, that the fields of the primary key are by default always audited.
tdsls400:500:A:col1,col2 For table tdsls400 in company 500, only col1 and col2 are audited, and they are audited always, that is, for any transaction in this table. The other fields of this table are not audited, unless another line, for example, tdsls:*:C, specifies that they must be audited.

Note that to audit a field means that the old and the new value of the tablecell are logged.

Because the relation between audit type (A or C) and field specification can be confusing, the following tables display the meaning of each combination. Each combination is illustrated by two examples.

Legend
C	Column (not specified)
S	Specified column
K	Keyfield (either mandatory, or optionally specified or not specified)
R	Record
A	Audited
(A)	Audited if specified (keyfield)
T	Transaction

Possible combinations of audit type and field specification
	All fields	Specified fields
Audit Type A	1	2
Audit Type C	3	4

Example 1

Every tablecell in the record concerned is audited when any transaction in the table occurs.

Audit type A, all fields
	K	C	C	C	C	C
R	A	A	A T	A	A	A
R	A T	A	A	A	A	A

Example 2

Only the tablecells in the record concerned in the specified columns are audited when any transaction in the table occurs.

Audit type A, specified fields
	K	C	C	S	C	S
R	(A)		T	A		A
R	(A)			A		A T

Example 3

Every tablecell in every column in the table can be audited, but only the tablecell in which a transaction occurs is actually audited.

Audit type C, all fields
	K	C	C	C	C	C
R	A		A T
R	A				A T

Example 4

Only tablecells in the specified columns can be audited. A tablecell is audited when a transaction occurs in a specified column.

Audit type C, specified fields
	K	C	C	S	C	S
R	(A)		T
R	(A)			A T

General Remarks

The commands that cause a table transaction to be audited, are only the commands that affect the table data, that is, Insert, Update, and Delete commands. Several table level commands that affect all rows in a table are also audited, such as Create Table, Drop Table, and Clear Table.

The audit configuration uses a positive approach, which means that you can only define the tables and fields that must be audited, but not the tables and fields that must not be audited. To compensate for this limitation, you can load lists of all packages, modules, tables or fields through the appropriate menu of the sessions concerned.

Because tables and fields can be used in several profiles, with conflicting settings, the following rules determine which setting take precedence over the conflicting setting:

A higher level takes precedence over a lower level. So, if you define in profile A that all tables in a module must be audited, but in profile B that only one table in this module must be audited, the result, when you convert these profiles to run time, is that all modules in the package concerned are audited.
The setting Always takes precedence over Changed. So, if profile A defines that a field must only be audited, when it is changed, and profile B defines that this field must always be audited, the field is audited always.

The following tables illustrate this behavior:

Package	Module	Table	Audit Type
tt	adv	*	Always
tt	adv	200	Changed
Result:
tt	adv	*	Always

Package	Module	Table	Audit Type
tt	adv	*	Changed
tt	adv	200	Always
Result:
tt	adv	*	Changed
tt	adv	200	Always

When also company groups are added to a profile, the result of the convert to run time action on these profiles is determined by these company groups also, as illustrated in the following table:

Package	Module	Table	Companies	Audit Type
tt	*	*	001, 002	Changed
tt	adv	*	002, 003	Always
tt	adv	200	002, 004	Changed
Result:
tt	*	*	001, 002	Changed
tt	adv	*	002, 003	Always
tt	adv	200	004	Changed

The following table displays the same information, but now per company, and only for table ttadv200:

Companies	Audit Type	Comment
001	Changed
002, 003	Always
004	Changed
005	-	For other companies, table ttadv200 is not audited.

It is possible to configure audit settings for tables in another package combination. However, you cannot zoom to these tables; you must enter them manually.

When you convert the audit configuration to run time, the result is stored in the following four files, which you can find in the \$BSE\lib directory:

audit_spec
audit_cols
audit_hosts
auditdef6.2

Because converting the configuration to run time can affect the audit trails that are logged, it is recommended that all users leave LN before you convert to run time.

Note

The files created by the Create Runtime Audit Definitions (ttaud3200s000) session must not be updated manually, because the manual changes will be overwritten when running the session again.

To use audit file directories

Important!

This section contains the online manual topic about auditing for LN Tools 7.3a. This information is only provided as a reference source for migration purposes. The information in this section (until the end of this document) is not valid for LN Tools 7.4a and later versions.

Audit files are log files that contain data of database actions on a table. Every delete, update, or insert action on records is logged when:

The Audit Trail check box in the Table Fields (ttadv4122s000) session is selected for the table fields of the record.
The Audit On check box in the Tables by Database (ttaad4111m000) session is selected for the database definition of the table which must be logged.

The data specified in the Audit File Directories (ttaad4116m000) session is stored in the auditdef6.X file in the $BSE/lib directory when it is converted to the run-time data dictionary.

This file has the following structure:

The table name indicates the table to which the audit trail refers. The Table Name field can be filled with a:

* (all modules of all packages).
Package and module code. For example, tccom, and tcibd.
Package and module code with a table number. For example, tcibd010, and tccom001. Table names are separated by commas.

The company number of the specified table(s) can be filled as a:

* (all possible company numbers)
Three-digit company number. Company numbers are separated by commas.

The directory contains the path name where the audit files must be stored.

For example:

tcibd,ttaad:100,200:/usr/adm/AUDIT

*:*:/usr1/AUDIT

All tcibd tables in the example are stored in the /usr/adm/AUDIT/atcibd directory, and all ttaad tables are stored in the /usr/adm/AUDIT/attaad directory. For all other tables, respective directories are created under /usr1/AUDIT.