More Audit Journal table functions added

As part of the last Technology Refresh four SQL table functions were added that allowed me to extract certain journal entry types from the system audit journal. In the latest Technology Refreshes, IBM i 7.4 TR5 and 7.3 TR11, twelve new Table functions to extract other journal entries types were introduced:

• Command string (CD)
• Create object (CO)
• User profile changes (CP)
• Delete operations (DO)
• Environment variable (EV)
• Generic record (GR)
• Action to system value (SV)

As well as those seven there are five for the Db2 Mirror product. Alas, I cannot show examples of those as I do not have access to an IBM i partition that is using Db2 Mirror.

• Db2 Mirror setup tools (M0)
• Db2 Mirror communication services (M6)
• Db2 Mirror replication services (M7)
• Db2 Mirror product services (M8)
• Db2 Mirror replication state (M9)

All of these table functions have the same parameters and set of common result columns, the same that those previously release table functions have. Rather than repeat myself I am going to refer you to appropriate part in that post here.

Do notice that, as with the earlier table functions, these also reside in the SYSTOOLS library.

I can only give full examples of the table functions only if the data is being gathered by the Audit journal. On the partition I use for writing these posts there is only certain information gathered. I can identify what types of data will be written to the Audit journal by looking at the QAUDLVL and QAUDLVL2 system value. I could use the Display System Value command, DSPSYSVAL, but what is the fun with that if I can get the system value data using SQL:

SELECT SYSTEM_VALUE_NAME AS "Name", CURRENT_CHARACTER_VALUE AS "Values" FROM QSYS2.SYSTEM_VALUE_INFO WHERE SYSTEM_VALUE_NAME LIKE 'QAUDLVL%'

This returns to me:

Name Values -------- -------------------------------------- QAUDLVL *AUTFAIL *SAVRST *SYSMGT *PGMFAIL QAUDLVL2 *NONE

Those values mean:

• *AUTFAIL:  Authority failure events are logged.
• *SECCFG:  Security configuration is audited.
• *SYSMGT:  Use of systems management functions is logged.
• *PGMFAIL:  System integrity violations are logged.

Which means that not all the journal type entries are being written to the System Audit journal. This makes it difficult for me to write about what I cannot see. Therefore, I will give the SQL statement and example results for the ones I can retrieve information for. The others I will just have the basic statement that you can copy and try on your IBM i partition.

 

Command string journal entries (CD)

The "CD" journal entries are for command strings that have happened on your partition. These are retrieved from the audit journal using the AUDIT_JOURNAL_CD table function. In this case I am only interested in the most recent journal entries:

01 SELECT QUALIFIED_JOB_NAME,OBJECT_LIBRARY,OBJECT_NAME,OBJECT_TYPE, 02 ENTRY_TYPE_DETAIL,WHERE_RUN_DETAIL,COMMAND_STRING 03 FROM TABLE(SYSTOOLS.AUDIT_JOURNAL_CD( 04 STARTING_TIMESTAMP => CURRENT_TIMESTAMP – 4 HOURS)) 05 ORDER BY ENTRY_TIMESTAMP DESC 06 LIMIT 5

Lines 1 and 2: I did not want all of the columns from AUDIT_JOURNAL_CD, just the following:

• QUALIFIED_JOB_NAME:  What I would call the full job name
• OBJECT_LIBRARY, OBJECT_NAME, OBJECT_TYPE:  Are what the columns name say they are
• ENTRY_TYPE_DETAIL:  I could have used the ENTRY_TYPE column, but that is only one character. Without the mapping it is not self-evident what that types mean. ENTRY_TYPE_DETAIL is the description of the ENTRY_TYPE.
• WHERE_RUN_DETAIL:  This is the description of the WHERE_RUN column, which only returns a single character. In my example results some of the columns end with '…' as the width of the data is wider than I can show here
• COMMAND_STRING:  The actual command string. If not command is performed the result is null

Lines 3 and 4: This is the definition of the table function. I am using the STARTING_TIMESTAMP parameter to limit the results to just anything that happened within the last just four hours.

Line 5: I want to order the results so the most recent is the first displayed.

Line 6: I only want five results, therefore, I use the LIMIT clause.

My results are as follows:

OBJECT_ OBJECT OBJECT ENTRY_ QUALIFIED JOB_NAME LIBRARY _NAME _TYPE TYPE_DETAIL ----------------------- ------- ------ ------ ------------- 420651/QUSER/QZRCSRVS QSYS CHGJOB *CMD Command run 409494/QSECOFR/##SECDTA QSYS FTP *CMD Proxy command 429323/SIMON/QPADEV0003 QPDA WRKMBRPDM *CMD Command run 429323/SIMON/QPADEV0003 QSYS WRKMBRPDM *CMD Proxy command 429323/SIMON/QPADEV0003 MYLIB WM *CMD Proxy command WHERE_RUN_DETAIL ------------------------------------------------------------- The command string was passed as a parameter to one of the... From a compiled OPM CL program or an ILE CL program Interactively from a command line or by choosing a menu op... Interactively from a command line or by choosing a menu op... Interactively from a command line or by choosing a menu op... COMMAND_STRING --------------------------------------------------- CHGJOB JOB(370597/QUSER/QZDASOINIT) INQMSGRPY(*DFT) <NULL> WM FILE(DEVSRC) <NULL> <NULL>

The last three results are all about my proxy command WM I use in place of the WRKMBRPDM command.

 

Create object journal entries (CO)

No journal entries.

SELECT * FROM TABLE(SYSTOOLS.AUDIT_JOURNAL_CO())

 

User profile changes journal entries (CP)

No journal entries.

SELECT * FROM TABLE(SYSTOOLS.AUDIT_JOURNAL_CP())

 

Delete operations journal entries (DO)

The AUDIT_JOURNAL_DO table function returns a list of object deleted from the IBM i partition. I have found that I need to give a starting date and time for the table function to return any results. If I omit all of the parameters no results are returned.

My statement looks like:

01 SELECT OBJECT_LIBRARY,OBJECT_NAME,OBJECT_TYPE,OBJECT_ATTRIBUTE, 02 ENTRY_TYPE_DETAIL, 03 QUALIFIED_JOB_NAME,ENTRY_TIMESTAMP 04 FROM TABLE(SYSTOOLS.AUDIT_JOURNAL_DO( 05 STARTING_TIMESTAMP => CURRENT_TIMESTAMP - 1 MONTH)) 06 ORDER BY ENTRY_TIMESTAMP DESC 07 LIMIT 10

Lines 1 - 3: The columns I want returned.

• OBJECT_LIBRARY:  Library name
• OBJECT_NAME:  Object name
• OBJECT_TYPE:  Type of object
• OBJECT_ATTRIBUTE:  If appropriate object attribute, or null
• ENTRY_TYPE_DETAIL:  Description of the entry type
• QUALIFIED_JOB_NAME:  Full job name
• ENTRY_TIMESTAMP:  Date and time the deletion happened

Lines 4 and 5: Definition of the AUDIT_JOURNAL_DO table function, and I need to pass the starting date and time to this table function to return results.

Line 6: Sort the file by the deletion timestamp, with the most recent coming first.

Line 7: As this is only a test I just want ten results returned.

My returned results are as follows:

OBJECT_ OBJECT_ OBJECT_ OBJECT LIBRARY NAME TYPE ATTRIBUTE --------- ---------- ------- --------- QGPL SYSLOADUPD *CMD <NULL> #SYSLOADX SYSLOADUPC *PGM CLP QGPL SLUPDCMD *FILE PF-DTA #SYSLOADX GETSYSLOAR *PGM RPGLE #SYSLOADX SYSVALRTNS *SRVPGM RPGLE #SYSLOADX SYSVALRTNS *MODULE RPGLE #SYSLOADX #SYSLOADQ *JOBQ <NULL> #SYSLOADX OUTPUT *OUTQ <NULL> #SYSLOADX #SYSLOADJ *JOBD <NULL> #SYSLOADX #SYSLOAD *CLS <NULL> ENTRY_TYPE_DETAIL ------------------------------------------------- Object was deleted (not under commitment control) Object was deleted (not under commitment control) Object was deleted (not under commitment control) Object was deleted (not under commitment control) Object was deleted (not under commitment control) Object was deleted (not under commitment control) Object was deleted (not under commitment control) Object was deleted (not under commitment control) Object was deleted (not under commitment control) Object was deleted (not under commitment control) QUALIFIED_JOB_NAME ENTRY_TIMESTAMP ------------------------- -------------------------- 382359/#SYSLOAD/INSTSL 2021-09-20 13:11:40.702192 382359/#SYSLOAD/INSTSL 2021-09-20 13:11:40.695072 382359/#SYSLOAD/INSTSL 2021-09-20 13:11:40.568640 382325/#SYSLOAD/SYSLOADUP 2021-09-20 13:11:08.421392 382325/#SYSLOAD/SYSLOADUP 2021-09-20 13:11:07.594736 382325/#SYSLOAD/SYSLOADUP 2021-09-20 13:11:07.578928 382325/#SYSLOAD/SYSLOADUP 2021-09-20 13:11:07.570112 382325/#SYSLOAD/SYSLOADUP 2021-09-20 13:11:07.563984 382325/#SYSLOAD/SYSLOADUP 2021-09-20 13:11:07.421232 382325/#SYSLOAD/SYSLOADUP 2021-09-20 13:11:07.417328

The results lists various different types of object that were deleted.

 

Environment variable journal entries (EV)

No journal entries.

SELECT * FROM TABLE(SYSTOOLS.AUDIT_JOURNAL_EV())

 

Generic record journal entries (GR)

It is a bit strange to have "Generic record" or "General purpose" journal entries, but the operating system does create them. To find them I can use the AUDIT_JOURNAL_GR table function.

01 SELECT USER_NAME,QUALIFIED_JOB_NAME,PROGRAM_LIBRARY,PROGRAM_NAME, 02 ENTRY_TYPE_DETAIL,ACTION_DETAIL, 03 FUNCTION_REGISTRATION_OPERATION,FUNCTION_NAME 04 FROM TABLE(SYSTOOLS.AUDIT_JOURNAL_GR()) 05 ORDER BY ENTRY_TIMESTAMP DESC 06 LIMIT 5

Line 1: These columns are all from the common columns that are found in all of these Audit journal table functions. You may wonder why I have included the user name, when it is normally in the qualified job name. You will have to be a patient.

Lines 2 and 3: These are columns specific to the AUDIT_JOURNAL_GR table function:

• ENTRY_TYPE_DETAIL:  Rather than give the single character ENTRY_TYPE column, I prefer to use the description column
• ACTION_DETAIL:  The ENTRY_TYPE column is a two character code. This column give the description
• FUNCTION_REGISTRATION_OPERATION:  When the ENTRY_TYPE is "F" this column will contain data. If it is any other entry type then the value will be null
• FUNCTION_NAME:  If ENTRY_TYPE is "F" this column contains data, otherwise it is null

My results are:

QUALIFIED_ PROGRAM_ PROGRAM USER_NAME JOB_NAME LIBRARY _NAME --------- ----------------------- --------- -------- RPGPGM 370597/QUSER/QZDASOINIT QSYS QZDASOINIT RPGPGM 370597/QUSER/QZDASOINIT QSYS QZDASOINIT RPGPGM 370597/QUSER/QZDASOINIT QSYS QZDASOINIT RPGPGM 370597/QUSER/QZDASOINIT QSYS QZDASOINIT QSECOFR 424214/QSECOFR/##SECDTA FB400 FTPSAVEBCH FUNCTION_ ACTION REGISTRATION ENTRY_TYPE_DETAIL _DETAIL _OPERATION -------------------------------- ------- ------------ Function registration operations Read CHECK USAGE Function registration operations Read CHECK USAGE Function registration operations Read CHECK USAGE Function registration operations Read CHECK USAGE Function registration operations Read CHECK USAGE FUNCTION_NAME ------------------------ QIBM_DB_ZDA QIBM_DB_ZDA QIBM_DB_ZDA QIBM_DB_ZDA QIBM_QTMF_CLIENT_REQ_10

The first four rows happened when I connected my "Run SQL Scripts" to this partition. This is why the USER_NAME and the user name in QUALIFIED_JOB_NAME are different.

 

Action to system value journal entries (SV)

I am glad I can retrieve the changes made to system values entries. I will be adding this to my things to check on a regular basis.

The AUDIT_JOURNAL_SV is the table function I would use to do this. When writing this post I did find that if I left the table function's parameters blank I did not return any results. If I used the STARTING_TIMESTAMP parameter I could.

This is the statement I used:

01 SELECT SYSTEM_VALUE,ENTRY_TYPE_DETAIL,OLD_VALUE,NEW_VALUE, 02 USER_NAME,QUALIFIED_JOB_NAME,ENTRY_TIMESTAMP 03 FROM TABLE(SYSTOOLS.AUDIT_JOURNAL_SV( 04 STARTING_TIMESTAMP => CURRENT_TIMESTAMP - 1 MONTH)) 05 ORDER BY SYSTEM_VALUE,ENTRY_TIMESTAMP DESC

Lines 1 and 2: I decide to have what I would call the "interesting" information as the first columns, followed by the common ones.

• SYSTEM_VALUE:  System value name. Or, rarely, some special values that you can find in the documentation
• ENTRY_TYPE_DETAIL:  The ENTRY_TYPE is only one character, and without the key it is almost impossible to determine what it means. Therefore, I am including the column that contains the description for the entry type
• OLD_VALUE:  Value in the system value before it was changed
• NEW_VALUE:  Value in the system value after it was changed
• USER_NAME:  I am including this in case the system value was changed in ACS's "Run SQL Scripts"
• QUALIFIED_JOB_NAME:  The full job name
• ENTRY_TIMESTAMP:  When the change was made

Lines 3 and 4: The definition for the AUDIT_JOURNAL_SV, including the STARTING_TIMESTAMP parameter>

Line 5: I want the results returned to me sorted first by the system value, and then in reverse date and time order so that the newest entry for each system value comes first.

There were only two results in the desired date and time range:

SYSTEM OLD_ NEW_ _VALUE ENTRY_TYPE_DETAIL VALUE VALUE ---------- ----------------------- ----- ----- QIPLDATTIM Change to system values *NONE *NONE QIPLDATTIM Change to system values *NONE *NONE USER_ NAME QUALIFIED_JOB_NAME ENTRY_TIMESTAMP ----- -------------------- -------------------------- QPGMR 370600/QPGMR/QSYSSCD 2021-09-19 23:50:13.805024 QPGMR 293045/QPGMR/QSYSSCD 2021-09-17 14:17:03.472832

I think these results show that even if you view the system value using the Change System Value command, CHGSYSVAL, or use the "2" option in the Work System Value command, WRKSYSVAL, even if I do not change anything and I press Enter it is recorded by a "SV" type journal entry.

 

You can learn more about this from the IBM website:

• Audit journal entry table functions
• Audit journal entry types

 

This article was written for IBM i 7.4 TR5 and 7.3 TR11.