Audit Journal Data Mart

This is something that caught my attention when the latest Technology Refreshes were announced: the ability to create a database of journal entry types entries. I had been doing something similar using the CREATE TABLE SQL statement and various of the Audit journal entry services, AUDIT_JOURNAL_xx. But these additions added functionality that makes it easier for me to extract the journal entries from the System Audit Journal, QAUDJRN.

The two new additions are:

1. MANAGE_AUDIT_JOURNAL_DATA_MART:  SQL procedure that uses the various AUDIT_JOURNAL_xx to create an output table.
2. AUDIT_JOURNAL_DATA_MART_INFO:  SQL View that displays information about all the files created with the MANAGE_AUDIT_JOURNAL_DATA_MART SQL procedure.

Before I go onto to discuss both of the above I want to check what type of journal type entries there are in QAUDJRN. I can use the DISPLAY_JOURNAL table function. I could use the statement below to list the various journal entry types and the number of each there is in QAUDJRN:

01 SELECT JOURNAL_ENTRY_TYPE, 02 TO_CHAR(COUNT(*),'999G999G999') AS "Count" 03 FROM TABLE(DISPLAY_JOURNAL('QSYS','QAUDJRN')) 04 GROUP BY JOURNAL_ENTRY_TYPE 05 ORDER BY JOURNAL_ENTRY_TYPE

Line 1: I want the journal entry type code in my result.

Line 2: Along with a count of each type. I am using the TO_CHAR scalar function to insert thousand separator characters.

Line 3: I am getting the details from the QAUDJRN journal receivers.

Lines 4 and 5: I want to group my results by journal entry type, therefore, I need the GROUP BY and ORDER BY clauses.

The full results are:

JOURNAL_ENTRY_TYPE Count ------------------ ------ AD 21 AF 3 AP 4 CA 3,894 CO 661 DO 651 GR 43 GS 260 IP 3,061 JS 8,789 KF 10 LD 639 OM 46 OW 13 PR 1 PS 79 PW 3 SF 19 SG 3,607 SK 5,877 ZC 2

I cannot find a table that contains the descriptions of these journal entry types. All I can do is refer you to IBM's Documentation for the descriptions, which you can find here.

I am going to divide this post into two parts to discuss the MANAGE_AUDIT_JOURNAL_DATA_MART and AUDIT_JOURNAL_DATA_MART_INFO in turn. Let me start with…

 

MANAGE_AUDIT_JOURNAL_DATA_MART

As I mentioned above MANAGE_AUDIT_JOURNAL_DATA_MART is a SQL procedure that is called to create, update, or delete the file for that journal entry type. It uses the various audit journal services, AUDIT_JOURNAL_xx, to get the data from QAUDJRN. While most of the journal entry types have a corresponding AUDIT_JOURNAL_xx, there are a few types that do not, and these cannot use MANAGE_AUDIT_JOURNAL_DATA_MART.

MANAGE_AUDIT_JOURNAL_DATA_MART has five parameters:

1. JOURNAL_ENTRY_TYPE  The journal entry type you are interested in.
2. DART_MART_LIBRARY  The library within which the output table is found or will be found.
3. STARTING_TIMESTAMP  A timestamp can be entered and any journal entries that are newer than that will be copied. The are a couple of special values that can be used in place of an actual timestamp:
   • *FIRST  Start copying journal entries from the first one (oldest) found.
   • *CONTINUE  Add journal entries that are newer than the newest entry currently in the output table.
4. ENDING_TIMESTAMP  End of the timestamp range. If this is not given the default is the current timestamp value.
5. DATA_MART_ACTION  Type of action to perform to the output table. Allowed values are:
   • CREATE  Crates and populates a new output table.
   • ADD  Appends to an existing table.
   • REPLACE  Replaces the contents of an existing output table.
   • DROP  Deletes the output table.

The created output file's short (system) name will be "AJ_xx", and its long name is "AUDIT_JOURNAL_xx".

In the following examples I am interested in the journal entry type of CA, which is for authority changes.

First I want to create the output file for authority changes that have happened as far back as I can retrieve. The statement would be:

01 CALL QSYS2.MANAGE_AUDIT_JOURNAL_DATA_MART( 02 JOURNAL_ENTRY_TYPE => 'CA', 03 DATA_MART_LIBRARY => 'MYLIB', 04 STARTING_TIMESTAMP => '*FIRST', 05 DATA_MART_ACTION => 'CREATE')

Line 1: As this is a SQL procedure it must be called.

Line 2: I want CA entries.

Line 3: I want the output file to be created in my library.

Line 4: I want all the entries there are. I use the *FIRST so it will copy all the CA entries from the first one. As there is not end timestamp it will copy all the entries up to the current time.

Line 5: As the output file does not exist I need to run this statement with the create action.

I am not going to show my results. If I wanted to show them I would use the following:

SELECT * FROM MYLIB.AUDIT_JOURNAL_CA

I do want to show that the output file was created.

I can check to see what objects are in MYLIB by using the OBJECT_STATISTICS table function:

01 SELECT OBJNAME,OBJLONGNAME,SQL_OBJECT_TYPE 02 FROM TABLE(QSYS2.OBJECT_STATISTICS('MYLIB','*FILE'))

The results are:

OBJNAME OBJLONGNAME SQL_OBJECT_TYPE ------- ---------------- --------------- AJ_CA AUDIT_JOURNAL_CA TABLE

The OBJNAME column contains the short name. As I mentioned above the name is 'AJ_' followed by the journal entry type.

OBJLONGNAME, as the name suggests, contains the long column name. Which is always 'AUDIT_JOURNAL_' followed by the journal entry type. Personally, I prefer this name to the short name.

SQL_OBJECT_TYPE confirms that the output file is a DDL table.

If I wanted to delete the output file I would use the following statement:

01 CALL QSYS2.MANAGE_AUDIT_JOURNAL_DATA_MART( 02 JOURNAL_ENTRY_TYPE => 'CA', 03 DATA_MART_LIBRARY => 'MYLIB', 04 DATA_MART_ACTION => 'DROP')

With the drop action there is no need to give either the start or end timestamp.

Let me turn my attention to LD journal entry types, for link, unlink, and search directory entries.

I want to extract all the LD up to a month ago. My statement would look like:

01 CALL QSYS2.MANAGE_AUDIT_JOURNAL_DATA_MART( 02 JOURNAL_ENTRY_TYPE => 'LD', 03 DATA_MART_LIBRARY => 'MYLIB', 04 STARTING_TIMESTAMP => '*FIRST', 05 ENDING_TIMESTAMP => CURRENT_TIMESTAMP - 1 MONTH, 06 DATA_MART_ACTION => 'CREATE')

Line 5: I am using the ending timestamp to be the current timestamp less one month. I have used this, rather than hard code in a timestamp value, so that I can run this multiple times and get results excluding the last months' worth of data.

Again I am not going to show the data. But I do want to the earliest and most recent entries timestamps:

01 SELECT MIN(ENTRY_TIMESTAMP) AS "Minimum", 02 MAX(ENTRY_TIMESTAMP) AS "Maximum" 03 FROM MYLIB.AUDIT_JOURNAL_LD

Lines 1 and 2: I use the MIN and MAX scalar functions to extract the earliest and latest timestamps.

The results are:

Minimum Maximum -------------------------- -------------------------- 2024-04-15 11:50:22.950784 2024-07-13 14:34:56.863392

I now want to bring the output file up to date. I can use the starting timestamp value of *CONTINUE to do that:

01 CALL QSYS2.MANAGE_AUDIT_JOURNAL_DATA_MART( 02 JOURNAL_ENTRY_TYPE => 'LD', 03 DATA_MART_LIBRARY => 'MYLIB', 04 STARTING_TIMESTAMP => '*CONTINUE', 05 DATA_MART_ACTION => 'ADD')

Line 4: The starting timestamp is *CONTINUE.

Line 5: As the output file already exists I need to add to the existing file.

Running the same statement as I did before to get the minimum and maximum timestamp values:

01 SELECT MIN(ENTRY_TIMESTAMP) AS "Minimum", 02 MAX(ENTRY_TIMESTAMP) AS "Maximum" 03 FROM MYLIB.AUDIT_JOURNAL_LD

The minimum timestamp remains unchanged. The maximum timestamp has changed to that of the latest row in the file.

Minimum Maximum -------------------------- -------------------------- 2024-04-15 11:50:22.950784 2024-08-13 14:38:06.081456

Let me now replace the contents of the output table:

01 CALL QSYS2.MANAGE_AUDIT_JOURNAL_DATA_MART( 02 JOURNAL_ENTRY_TYPE => 'LD', 03 DATA_MART_LIBRARY => 'MYLIB', 04 STARTING_TIMESTAMP => CURRENT_TIMESTAMP - 1 MONTH, 05 DATA_MART_ACTION => 'REPLACE')

Line 4: I want to copy all the entries that are new than current timestamp minus one month.

Line 5: The action is now replaced.

Let me check the min and max values in the output file:

01 SELECT MIN(ENTRY_TIMESTAMP) AS "Minimum", 02 MAX(ENTRY_TIMESTAMP) AS "Maximum" 03 FROM MYLIB.AUDIT_JOURNAL_LD

I can see that my minimum value is a month ago.

Minimum Maximum -------------------------- -------------------------- 2024-07-13 14:44:57.609264 2024-08-13 14:38:06.081456

I think on the advantages of having these output files is if I don't want all the journal entries I can just delete them from the output file.

Before I start deleting records I want a row count of the output file:

01 SELECT TO_CHAR(COUNT(*),'999G999') AS "Count" 02 FROM MYLIB.AUDIT_JOURNAL_LD

Below is the result:

Count ------- 13,690

Now I perform the delete. I want to delete all the entries for jobs run by the QSYS profile:

01 DELETE FROM MYLIB.AUDIT_JOURNAL_LD 02 WHERE JOB_USER = 'QSYS'

I get the following message as the bottom of the ACS RSS window:

Statement ran successfully (198 ms) 8934 rows were affected by the statement

Let me do the count again to see if this number is less than the one before:

01 SELECT TO_CHAR(COUNT(*),'999G999') AS "Count" 02 FROM MYLIB.AUDIT_JOURNAL_LD

It is. This proves I deleted rows from the output file.

Count ------- 4,756

 

AUDIT_JOURNAL_DATA_MART_INFO

The AUDIT_JOURNAL_DATA_MART_INFO view allows me to list all of the output files created by MANAGE_AUDIT_JOURNAL_DATA_MART.

This is the statement I decided was most useful to what I want from this view:

01 SELECT DATA_MART_LIBRARY AS "Library", 02 DATA_MART_TABLE AS "Table", 03 JOURNAL_ENTRY_TYPE AS "Typ", 04 AUDIT_JOURNAL_STARTING_TIMESTAMP AS "Audit jrn start", 05 AUDIT_JOURNAL_ENDING_TIMESTAMP AS "Audit jrn end", 06 BUILD_START AS "Bld start", 07 BUILD_END AS "Bld end", 08 BUILD_JOB AS "Bld job", 09 FAILURE_DETAIL AS "Fail dtl" 10 FROM QSYS2.AUDIT_JOURNAL_DATA_MART_INFO 11 ORDER BY 1,2

At present on this IBM i partition there are only two output tables that have been created by MANAGE_AUDIT_JOURNAL_DATA_MART, therefore, my results are limited:

Library Table Typ -------- ---------------- --- MYLIB AUDIT_JOURNAL_CA CA MYLIB AUDIT_JOURNAL_LD LD Audit jrn start Audit jrn end -------------------------- -------------------------- 2024-04-15 11:49:14.000000 2024-08-13 14:35:31.065941 2024-07-13 14:42:54.504503 2024-08-13 14:42:54.519964 Bld start Bld end -------------------------- -------------------------- 2024-08-13 14:35:31.249434 <NULL> 2024-08-13 14:42:54.524329 2024-08-13 14:42:59.411819 Bld job Fail dtl ----------------------- ------------------------------ 087803/QUSER/QZDASOINIT MANAGE_AUDIT_JOURNAL_DATA_M... 087803/QUSER/QZDASOINIT <NULL>

IBM's documentation says that the build end date will only be null if the output file is being updated or the process that created the file ended in error.

This is a good view to use when I need to check that all my output files were updated on the dates I wanted them to be.

 

You can learn more about this from the IBM website:

• MANAGE_AUDIT_JOURNAL_DATA_MART procedure
• AUDIT_JOURNAL_DATA_MART_INFO view

 

This article was written for IBM i 7.5 TR4 and 7.4 TR10.