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.
