The AUDIT_DATA table

After the audited events are logged into the AUDIT_LOG table, you can choose to transfer the logged data to another table, the AUDIT_DATA table, that makes it more consumable for the production of your custom reports.

Available from V5.2.6.2, the AUDIT_DATA table shows the logged data in a more consumable way, easing the task of creating Audit custom reports, and providing a richer filtering capability. The column names are also enhanced to make the data more readable.

A particular improvement is in the way that the changed data is shown for events that are caused by Modify actions. The AUDIT_LOG table logs the changed entity attributes in the DATA column, in the form:

{"attribute₁":"{\"old_value₁\":\"new_value₁\"}",
...,
"attribute_n":"{\"old_value_n\":\"new_value_n\"}"}

After the audit record is transferred to the AUDIT_DATA table, the attribute, the old value, and the new value are displayed in three separate columns:

Table 1. Changed attribute values in the AUDIT_DATA table.
ATTRIBUTE	OLD_VALUE	NEW_VALUE
attribute₁	old_value₁	new_value₁
...	...	...
attribute_n	old_value_n	new_value_n

Also, while the AUDIT_LOG table logs all the changed entity attributes in the same row, the AUDIT_DATA table shows each changed attribute in a separate row. The data that is logged in the AUDIT_LOG table is parsed and copied over to AUDIT_DATA, at predefined intervals, and each modified attribute is placed in its own new row. For example, if 5 attributes of an Account entity are modified, the AUDIT_LOG table logs one Modify Account record, with all the new and old values of the modified attributes in the same row of the DATA column. The AUDIT_DATA table shows instead 5 separate records, one for each modified account attribute.

Transferring the logged data from the AUDIT_LOG table to the AUDIT_DATA table

The AuditDeltaCreationTask Task Planner task is provided to transfer the data. Its job is designed to normalize data from the AUDIT_LOG table and to place it in the AUDIT_DATA table.

The task is installed with V5.2.6.2 and later. It is not activated by default. You must activate it if you decide to use the AUDIT_DATA table. By default, the task is scheduled to run at the end of each hour, but you can change this.

This is the flow of the process after you activate AuditDeltaCreationTask:

As part of a given audited operation, the audit record is logged into the AUDIT_LOG table.
When AuditDeltaCreationTask runs, it starts parsing and copying the data from AUDIT_LOG to AUDIT_DATA, in chunks that are configured by the virtual appliance administrator (with the audit.log.batch.process option of the config.properties file).

The audit.log.batch.process option is in the config.properties file of the virtual appliance.

The path to access config.properties in the virtual appliance local management interface is Configure > Custom File Management. In the Custom File Management pane, the administrator must click on the properties folder to list the files on the right, and scroll down the list to select and download the file.

Open the file with an editor and look for the following lines:

#determines the number of audit logs that are processed in a single batch, from AUDIT_LOG to AUDIT_DATA by the AuditDeltaCreationTask
#in the Task Planner module. 1000 is the default batch size. 
#NOTE: While increasing the batch size,it is recommended to try the new batch size in a staging environment before using it on production
audit.log.batch.process=1000

This parameter is used by the AuditDeltaCreationTask job to process the audit records in sizable chunks. You can customize it for your environment.

Performance considerations

The migration of data from AUDIT_LOG to AUDIT_DATA can be a time-and-resource-consuming process. You should plan its scheduling to minimize its impact on the daily activities. Follow these guidelines to run this process as smoothly as possible:

If your AUDIT_LOG table comprises a huge amount of records from past auditing, the first time that AuditDeltaCreationTask runs, it finds a lot of data to process and migrate. Plan this activity for a time when the server load is expected to be less. For the initial migration, set the size of the audit.log.batch.process parameter to a higher value. The value of the batch size depends on the existing load on the environment, and on the number of concurrently running tasks. Hence, you should try out with different batch sizes in a staging environment, before finalizing on one.
After the initial migration is completed, you can resize audit.log.batch.process to a lower value.
Ensure that enough disk space is available to accommodate AUDIT_DATA. The size of the AUDIT_DATA table is similar to that of AUDIT_LOG. Since there is no auto-purge of data from AUDIT_LOG, available disk space is an important question to evaluate for planning.
After AuditDeltaCreationTask is first activated, it populates the AUDIT_DATA table with all the data from AUDIT_LOG. The two tables are expected to be huge in size. You should purge the data from the AUDIT_LOG table, after it is copied over to AUDIT_DATA.
After the initial migration of the past auditing data is completed, time the regular schedule of AuditDeltaCreationTask at hours when the load on the system is known to be low.
Periodically purge from AUDIT_LOG the data that is already copied to AUDIT_DATA. Run
```
SELECT MAX(AUDIT_LOG) FROM #igaserviceschema#.AUDIT_DATA
```
to get the ID of the last AUDIT_LOG record that was copied to the AUDIT_DATA table. You can purge all the records prior to this ID.

AUDIT_DATA table reference

Table 2 describes the columns that are present in the AUDIT_DATA table.

Table 2. AUDIT_DATA table reference
Column name	Column description	Data type	Default value
ID	The ID of the log record	NUMERIC
AUDIT_LOG	The ID of the log record in the AUDIT_LOG table	NUMERIC
OPERATOR	The type of user whose action originates the event (for example, Admin)	CHARACTER	Not available
OPERATOR_ID	The ID of the operator as it is recorded in the database	CHARACTER	Not available
EVENT_ID	The ID of the event type. See Introduction to audit.	NUMERIC
EVENT	A brief definition or title of the event type. See Introduction to audit.	CHARACTER	Not available
OPERATION_CODE	The code of the operation that originates the event. An operation can originate more than one event. For example, the operation for deleting a user includes events for the removal of entitlements from the user before the user is deleted. All of these events are specified by the same operation code.	CHARACTER	Not available
EVENT_DATE	The date and time of the audited event	TIMESTAMP
BENEFICIARY_TYPE	The type of entity that is the beneficiary of the event. For example, USER. This column corresponds to the TARGET_TYPE column in the AUDIT_LOG table.	CHARACTER	Not available
BENEFICIARY_ID	The ID of the Beneficiary This column corresponds to the TARGET_KEY_ID column in the AUDIT_LOG table.	NUMERIC	0
BENEFICIARY	The name or code of the Beneficiary This column corresponds to the TARGET_KEY_NAME column in the AUDIT_LOG table.	CHARACTER	Not available
BENEFICIARY_ATTR1	Additional Beneficiary details. This column corresponds to the TARGET_ATTR1 column in the AUDIT_LOG table.	CHARACTER	Not available
BENEFICIARY_ATTR2	Additional Beneficiary details. This column corresponds to the TARGET_ATTR2 column in the AUDIT_LOG table.	CHARACTER	Not available
BENEFICIARY_ATTR3	Additional Beneficiary details. This column corresponds to the TARGET_ATTR3 column in the AUDIT_LOG table.	CHARACTER	Not available
BENEFICIARY_ATTR4	Additional Beneficiary details. This column corresponds to the TARGET_ATTR4 column in the AUDIT_LOG table.	CHARACTER	Not available
BENEFICIARY_ATTR5	Additional Beneficiary details. This column corresponds to the TARGET_ATTR5 column in the AUDIT_LOG table.	CHARACTER	Not available
METADATA1_TYPE	The type of the object that is affecting the beneficiary This column corresponds to the OBJ1_TYPE column in the AUDIT_LOG table.	CHARACTER	Not available
METADATA1_ID	The ID of the object This column corresponds to the OBJ1_KEY_ID column in the AUDIT_LOG table.	NUMERIC	0
METADATA1	The name of the object This column corresponds to the OBJ1_KEY_NAME column in the AUDIT_LOG table.	CHARACTER	Not available
METADATA1_ATTR1	Additional object details. This column corresponds to the OBJ1_ATTR1 column in the AUDIT_LOG table.	CHARACTER	Not available
METADATA1_ATTR2	Additional object details. This column corresponds to the OBJ1_ATTR2 column in the AUDIT_LOG table.	CHARACTER	Not available
METADATA1_ATTR3	Additional object details. This column corresponds to the OBJ1_ATTR3 column in the AUDIT_LOG table.	CHARACTER	Not available
METADATA1_ATTR4	Additional object details. This column corresponds to the OBJ1_ATTR4 column in the AUDIT_LOG table.	CHARACTER	Not available
METADATA1_ATTR5	Additional object details. This column corresponds to the OBJ1_ATTR5 column in the AUDIT_LOG table.	CHARACTER	Not available
METADATA2_TYPE	Any other object type that holds extra information about the event This column corresponds to the OBJ2_TYPE column in the AUDIT_LOG table.	CHARACTER	Not available
METADATA2_ID	The ID of the object This column corresponds to the OBJ2_KEY_ID column in the AUDIT_LOG table.	NUMERIC	0
METADATA2	The name of the object This column corresponds to the OBJ2_KEY_NAME column in the AUDIT_LOG table.	CHARACTER	Not available
METADATA2_ATTR1	Additional object details. This column corresponds to the OBJ2_ATTR1 column in the AUDIT_LOG table.	CHARACTER	Not available
METADATA2_ATTR2	Additional object details. This column corresponds to the OBJ2_ATTR2 column in the AUDIT_LOG table.	CHARACTER	Not available
METADATA2_ATTR3	Additional object details. This column corresponds to the OBJ2_ATTR3 column in the AUDIT_LOG table.	CHARACTER	Not available
METADATA2_ATTR4	Additional object details. This column corresponds to the OBJ2_ATTR4 column in the AUDIT_LOG table.	CHARACTER	Not available
METADATA2_ATTR5	Additional object details. This column corresponds to the OBJ2_ATTR5 column in the AUDIT_LOG table.	CHARACTER	Not available
ATTRIBUTE	The name of the added or modified entity attribute that leads to the generation of the audit record. For example, `Account Code`. The content of this column corresponds to the added, modified, or deleted entity attribute name in the DATA column of the AUDIT_LOG table.	CHARACTER	Not available
OLD_VALUE	The former value of the entity attribute. This is applicable only in the case of a modification. In the case of a created entity, the value is N/A. The content of this column corresponds to the former value of the modified entity attribute name in the DATA column of the AUDIT_LOG table.	CLOB	Not available
NEW_VALUE	The new value of the created or modified entity attribute. The content of this column corresponds to the new value of the created or modified entity attribute name in the DATA column of the AUDIT_LOG table.	CLOB	Not available
SOURCE_TYPE	The entity, or process, that caused the audit log.	CHARACTER	Not available
SOURCE_ID	The unique ID of the entity, or process, that caused the audit log.	NUMERIC	0
SOURCE_NAME	The name of the entity, or process, that caused the audit log.	CHARACTER	Not available
STATUS	The status of the operation: `SUCCESS`, `ERROR`, `PARTIALLY FAILED`, or `N/A`.	CHARACTER	Not available
STATUS_REASON	The reason for failure, in a JSON format, in cases where STATUS is `ERROR`, or `PARTIALLY FAILED`.	CLOB for Oracle and DB2. TEXT for PostgreSQL.	Not available
ACTED_AS_CODE	This column is specific to delegation flows. It contains the MasterUID of the delegator.	CHARACTER	Not available
ACTED_AS_KEY	This column is specific to delegation flows. It contains the ID of the delegator.	CHARACTER	Not available

When you create a custom report that uses the AUDIT_DATA table, be aware of the following limitations:

Views on the AUDIT_DATA table are not supported
Aliases for the AUDIT_DATA table columns cannot be used in the creation of the report query. You can, however, localize the columns when you generate the report.