2.18.6 Referencing audit trails

Audit trail information is output to the audit trail file in binary format. For this reason, it cannot be opened and referenced directly in the host operating system. Audit trail information can be referenced by using either of the following methods:

Executing a SELECT statement with the ADB_AUDITREAD function specified

ADB_AUDITREAD is a function that converts the audit trail information in the audit trail file to data in tabular format (as a table function derived table). Audit trail information can be referenced by executing a SELECT statement with the ADB_AUDITREAD function specified.
Using the adbconvertaudittrailfile command to convert audit trail information

The audit trail information that has been output to an audit trail file can be converted by using the adbconvertaudittrailfile command so that the audit trail information can be referenced with JP1/Audit, which is another product. For details about conversion of audit trail information, see 2.18.9 Conversion of audit trail information (linkage with JP1/Audit Management - Manager).

This subsection describes the audit trail reference method that executes a SELECT statement with the ADB_AUDITREAD function specified.

Specify in the ADB_AUDITREAD function the path of the audit trail file that stores the audit trail information you want to investigate. The HADB server reads the audit trail information from the audit trail file and converts it to data in tabular format.

The following figure shows an example of using the ADB_AUDITREAD function to read the audit trail information in an audit trail file.

Figure 2‒65: Example of using `ADB_AUDITREAD` function to read audit trail information from audit trail file

Explanation

When the following SELECT statement is executed, the audit trail information in the audit trail file specified in the underlined portion is read and converted to data in tabular format.

SELECT * FROM
  TABLE(ADB_AUDITREAD(MULTISET['/audit/*.aud'])) "DT"

Important: The path of the audit trail file (/audit/*.aud) is specified in the ADB_AUDITREAD function. In this example, the special character * is specified. Specifying the path in this way means that the ADB_AUDITREAD function reads all audit trail files in the /audit directory.

For details about the ADB_AUDITREAD function, see ADB_AUDITREAD function in the manual HADB SQL Reference.

The HADB server determines the column name and data type of each column in the tabular data converted by the ADB_AUDITREAD function. For details about the column names and data type of individual columns and the information they contain, see 12.9.2 Column structure of table function derived table when retrieving audit trails.

■ Example of retrieving audit trails

You can search the audit trail information by using a SELECT statement that specifies search conditions such as a specific period of interest. The following shows an example of retrieving specific audit trail information:

Example:

In this example, a list of HADB users who accessed the HADB server between April 1st and April 30th, 2017 is retrieved.

SELECT DISTINCT "USER_NAME"                                           ...1
  FROM TABLE(ADB_AUDITREAD(MULTISET['/audit/*.aud'])) "DT"
    WHERE "EXEC_TIME" BETWEEN TIMESTAMP'2017/04/01 00:00:00.000000'   ...2
                          AND TIMESTAMP'2017/04/30 23:59:59.999999'

Example search results

USER_NAME
---------
ADBUSER02
ADBUSER06
ADBUSER07

Explanation

The USER_NAME column of the tabular data converted by the ADB_AUDITREAD function contains the authorization identifiers of the HADB users who accessed the HADB server.
The EXEC_TIME column contains the time at which the HADB user performed the operation (the event completion time).

Important: Only HADB users with the audit viewer privilege can reference audit trail information. That is, only these users can execute SELECT statements that specify the ADB_AUDITREAD function.

To Page Top