Pages

Wednesday, September 29, 2021

More Audit Journal table functions added

table function for qaudjrn entry types

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:

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:

 

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

2 comments:

  1. Harold Adolfo Mendoza AvendañoSeptember 30, 2021 at 5:07 AM

    Máster graciss

    ReplyDelete
  2. Simon, thanks for sharing. Great read. Have a great weekend..

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.