Audit Journal Entry Services

I have managed to persuade the admins of the server I use to develop the code for these posts upon to give me access to the Audit Journal, QAUDJRN, this gives me the opportunity to write about several enhancements that have made to Db2 for i that makes getting information from QAUDJRN.

Over the past few releases and Technology Refreshes IBM has given us many Audit Journal Services. As QAUDJRN contains many different types of journal entries IBM has given us a specific Audit Journal Service for just about every journal entry type.

All of Audit Journal Services are called: AUDIT_JOURNAL_xx, where xx is the journal entry type. As of IBM i 7.5 TR4/IBM i 7.4 TR10 there are 41 of these Audit Journal Entry Services, out of a total of 76. That is 54% of all journal entry types covered. I do not know if the plan is to cover all of the job entry types, or not.

The Audit Journal Entry Services we currently have are:

• AUDIT_JOURNAL_AD:  Auditing Change (AD)
• AUDIT_JOURNAL_AF:  Authority Failure (AF)
• AUDIT_JOURNAL_AP:  Adopted Authority (AP)
• AUDIT_JOURNAL_AU:  Attribute Changes (AU)
• AUDIT_JOURNAL_AX:  Row and Column Access Control (AX)
• AUDIT_JOURNAL_CA:  Authority Changes (CA)
• AUDIT_JOURNAL_CD:  Command String (CD)
• AUDIT_JOURNAL_CO:  Create Object (CO)
• AUDIT_JOURNAL_CP:  User Profile Changes (CP)
• AUDIT_JOURNAL_DO:  Delete Operation (DO)
• AUDIT_JOURNAL_DS:  Service Tools User ID and Attribute Changes (DS)
• AUDIT_JOURNAL_EV:  Environment Variable (EV)
• AUDIT_JOURNAL_GR:  Generic Record (GR)
• AUDIT_JOURNAL_IM:  Intrusion Monitor (IM)
• AUDIT_JOURNAL_JS:  Job Change (JS)
• AUDIT_JOURNAL_LD:  Link, Unlink, Search Directory (LD)
• AUDIT_JOURNAL_M0:  Db2 Mirror Setup Tools (M0)
• AUDIT_JOURNAL_M6:  Db2 Mirror Communication Services (M6)
• AUDIT_JOURNAL_M7:  Db2 Mirror Replication Services (M7)
• AUDIT_JOURNAL_M8:  Db2 Mirror Product Services (M8)
• AUDIT_JOURNAL_M9:  Db2 Mirror Replication State (M9)
• AUDIT_JOURNAL_NA:  Attribute Change (NA)
• AUDIT_JOURNAL_OM:  Object Management Change (OM)
• AUDIT_JOURNAL_OR:  Object Restore (OR)
• AUDIT_JOURNAL_OW:  Ownership Change (OW)
• AUDIT_JOURNAL_PA:  Program Adopt (PA)
• AUDIT_JOURNAL_PF:  PTF Operations (PF)
• AUDIT_JOURNAL_PG:  Primary Group Change (PG)
• AUDIT_JOURNAL_PS:  Profile Swap (PS)
• AUDIT_JOURNAL_PU:  PTF Object Change (PU)
• AUDIT_JOURNAL_PW:  Password (PW)
• AUDIT_JOURNAL_RA:  Authority Change for Restored Object (RA)
• AUDIT_JOURNAL_RO:  Ownership Change for Restored Object (RO)
• AUDIT_JOURNAL_RZ:  Primary Group Change for Restored Object (RZ)
• AUDIT_JOURNAL_SK:  Sockets Connections(SK)
• AUDIT_JOURNAL_SM:  Systems Management Change (SM)
• AUDIT_JOURNAL_ST:  Service Tools Action (ST)
• AUDIT_JOURNAL_SV:  Action to System Value (SV)
• AUDIT_JOURNAL_ZC:  Change to Object (ZC)
• AUDIT_JOURNAL_ZR:  Read of Object (RZ)

All of the above have the same parameters:

• STARTING_RECEIVER_NAME:  Name of the starting journal receiver. There are special values: *CURRENT, *CURCHAIN, *CURAVLCHN, and *CURSEQCHN. Default is *CURAVLCHN. Personally, I do not see a reason to use these parameter.
• ENDING_RECEIVER_NAME:  Name of ending journal receiver. Special value is *CURRENT, which is also the default value. I see no reason I would want to use this.
• STARTING_TIMESTAMP:  Start of timestamp range. Default is CURRENT_DATE - 1 DAY.
• ENDING_TIMESTAMP:  End of timestamp range. Default is the current timestamp.
• USER_NAME:  User name for the user name part of the job name. Default is *ALL.
• JOB:  Fully qualified job name. Default is *ALL.
• PROGRAM:  Program name. Default is *ALL.
• STARTING_SEQUENCE:  Starting sequence number that matches the starting sequence number column from DISPLAY_JOURNAL. Default is *FIRST.
• ENDING_SEQUENCE:  Ending sequence number. Default is *LAST
• STARTING_RECEIVER_LIBRARY:  Library the first journal receiver of the receiver chain is found within. Do not think I will ever use this.
• ENDING_RECEIVER_LIBRARY:  Library the last journal receiver of the receiver chain is found within. I am not likely to use this.

As you would expect the results from each Audit Journal Entry Service are unique. The columns in the results can be divided into those columns that are common to all of the Audit Journal Entry Services, and those that are unique.

The common columns are:

• ENTRY_TIMESTAMP:  Timestamp of when entry was added to journal receiver
• SEQUENCE_NUMBER:  Assigned by the system for each journal entry
• USER_NAME:  User profile name
• QUALIFIED_JOB_NAME:  Qualified job name
• JOB_NAME:  Job name from the qualified job name
• JOB_USER:  User name from the qualified job name
• JOB_NUMBER:  Jon number from the qualified job name
• THREAD:  Thread identifier
• PROGRAM_LIBRARY:  Name of library that contains the program
• PROGRAM_NAME:  Program's name
• PROGRAM_LIBRARY_ASP_DEVICE:  Name of ASP device that contains program
• PROGRAM_LIBRARY_ASP_NUMBER:  ASP number that contains the program
• REMOTE_PORT:  Remote address port number
• REMOTE_ADDRESS:  Remote address associated with journal entry
• SYSTEM_NAME:  Name of system the entry is being retrieved upon
• SYSTEM_SEQUENCE_NUMBER:  System sequence number
• RECEIVER_LIBRARY:  Name of library that contains journal receiver
• RECEIVER_NAME:  Journal receiver name
• RECEIVER_ASP_DEVICE:  ASP device the journal receiver is in
• RECEIVER_ASP_NUMBER:  ASP number that contains program
• ARM_NUMBER:  Disk arm that contains the journal entry

I am not going to show results from the following example SQL statements, as IMHO the information is confidential.

In this statement I want to see any user profiles that were changed or restored in the past week:

01 SELECT USER_PROFILE,COMMAND_TYPE,PASSWORD_CHANGED, 02 QUALIFIED_JOB_NAME,ENTRY_TIMESTAMP 03 FROM TABLE(SYSTOOLS.AUDIT_JOURNAL_CP( 04 STARTING_TIMESTAMP => CURRENT_DATE - 7 DAYS)) 05 WHERE COMMAND_TYPE IN ('CHG','RST') 06 ORDER BY ENTRY_TIMESTAMP,USER_PROFILE

Lines 1 and 2: I want to see the user profile, the command type (CHG or RST), was the password changed, the job name that did it, and when it was done.

Line 3: I use the AUDIT_JOURNAL_CP Audit Journal Entry Service as user profile changes are type CP.

Line 4: The start date (timestamp) I use is from seven days ago.

Line 5: In only want to see the changes and restores of user profiles.

Line 6: And sort my results by the time the change/restore happened.

What if I want to know all the authority failures (AF) that have occurred in the past two weeks:

01 SELECT * 02 FROM TABLE(SYSTOOLS.AUDIT_JOURNAL_AF( 03 STARTING_TIMESTAMP => CURRENT_DATE - 14 DAYS)) 04 ORDER BY ENTRY_TIMESTAMP

In this last example I want to list all of the objects that have been created (CO) in the past two days:

01 SELECT * 02 FROM TABLE(SYSTOOLS.AUDIT_JOURNAL_CO( 03 STARTING_TIMESTAMP => CURRENT_DATE - 2 DAYS)) 04 ORDER BY ENTRY_TIMESTAMP

I am sure you get the idea of using the Audit Journal Entry Services, so I am not going to give any more examples.

Using the Audit Journal Entry Services makes it a lot simpler and easier to get the various types of information from QAUDJRN.

You can learn more about this from the IBM website:

• List all AUDIT_JOURNAL_xx
• Common information from AUDIT_JOURNAL_xx
• QAUDJRN journal entry types

 

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