Filtering audit journal data mart entries in IBM i 7.5 TR 5

The benefit of using a data mart for audit journal information is to keep audit journal information on the system for a longer time than you typically could when leaving the information in audit journal receivers.

IBM i 7.5 TR 5 and 7.4 TR11 enhance your ability to be even more selective about what information you have in the data mart. A parameter has been added that accepts SQL, allowing you to filter the results.

When might you use this? Here’s an example:

Perhaps you have auditors that require you to prove that you have deleted user profiles on a timely basis when someone leaves the organization. The best way to prove this is via an audit journal entry. But very few organizations can keep audit journal receivers on their system for an entire year (the time period between most audits.) Instead, you could create a data mart for the DO (Deletion of Object) audit journal entries using QSYS2.MANAGE_AUDIT_JOURNAL_DATA_MART. However, this harvests all DO entries, most of which you’re not interested in. With this TR you’ll have the ability to limit the entries just to the deletion of user profiles using the DATA_MART_FILTER parameter.

/* Create a data mart for the DO entries for *USRPRF objects */
/* Note that the quotes around *USRPRF are two single quotes not a double quote */

CALL QSYS2.MANAGE_AUDIT_JOURNAL_DATA_MART(JOURNAL_ENTRY_TYPE => 'DO',
DATA_MART_LIBRARY => 'AUDIT_INFO’,
STARTING_TIMESTAMP => *FIRST,
ENDING_TIMESTAMP => CURRENT TIMESTAMP,
DATA_MART_FILTER => 'OBJECT_TYPE = ''*USRPRF'' ');

/* Prior to deleting audit journal receivers, add entries to the data mart, continuing from where it last left off */

CALL QSYS2.MANAGE_AUDIT_JOURNAL_DATA_MART(JOURNAL_ENTRY_TYPE => 'DO',
DATA_MART_LIBRARY => 'AUDIT_INFO',
STARTING_TIMESTAMP => *CONTINUE,
ENDING_TIMESTAMP => CURRENT TIMESTAMP,
DATA_MART_FILTER => 'OBJECT_TYPE = ‘’*USRPRF'' ');

This new feature of data mart management has also been added to Navigator for i ->Security (padlock icon) -> Audit Journal -> Manage Data Mart as shown below.

Figure 1: Creating a new data mart in Navigator for i