Consuming Journal Receiver Data Easily with SQL

A few weeks ago we were tasked with helping a customer log all IBM i user profile changes and deletes for audit and historical purposes.

Of course, you can certainly use the security audit journal for this type of task however you may not want to keep journal receivers on the system forever. The security audit journal receivers can be quite large and frequently generated, depending on the busyness of the server.

Truth be told…I absolutely detest interacting with the audit journal data directly. I always have. The DSPJRN command is clunky and the results look like the journal receiver fell off the ugly tree face-first, making them hard to read and comprehend.

Instead, I generated a couple of tables (user_changes and user_deletes) to collect the information, then retrieved information from the audit journal by way of Run SQL Scripts and two new IBM i services contained in the SYSTOOLS schema:


Both of these functions pull basic and entry-specific information from the security audit journal and were released in 7.3 TR 11 and 7.4 TR 5. SYSTOOLS.AUDIT_JOURNAL_CP contains user profile changes while SYSTOOLS.AUDIT_JOURNAL_DO contains object delete events. While the AUDIT_JOURNAL_CP table function gives me everything I need, the AUDIT_JOURNAL_DO will do much more than that. It returns information about EVERY object deletion, so I need to ensure that the object type returned is a user profile object (i.e., *USRPRF).

SELECT entry_timestamp,User_name,qualified_job_name,object_name

WHERE object_type = ‘*USRPRF’;

Now, each day there’s a job run on schedule that will collect those specific journal entries for user profile changes and deletes and insert them into the two logging tables. Could you trigger that type of call by way of a trigger like an exit point program? Absolutely. I simply chose the job scheduler option simply because it’s quick and easy. Depending on the time it runs each day, I could get a couple of duplicate records but that’s not the end of the world…they’re timestamped entries.

From a user profile change perspective, the real value is being to determine not only who changed a user profile but WHAT they changed. Did they have no special authorities before? Was *ALLOBJ special authority added? Was it a password change? Does that password change meet password requirements? AUDIT_JOURNAL_CP will tell you all that and more very quickly…and with plain English results.

More from this month:

Leave a Comment

Your email address will not be published. Required fields are marked *