Deciphering Audit Journal Codes

[The code for this article is available for download.]

Hey, David:

Our audit group determined that, based on the Sarbanes-Oxley Act of 2002, we need to start tracking IT access of production objects. We are trying to meet their requirements for an internal control structure and procedures related to financial reporting. First, we removed access to production libraries for most IT staff, then turned on object auditing for all objects in those libraries. Next, we turned on auditing for objects read and changed, along with action auditing for adds, deletes, and object management for all of IT.

Now I need to create reports over the audit data, but interpreting the data is difficult. The display journal (DSPJRN) command lists the audit codes, but I don't want to re-key them into a file unless I have to. Are the audit codes defined in a table, or do I have to create one myself?

--Eric

I don't know of any pre-built table on the iSeries that contains descriptions for audit codes. I did create two tables to help you and other readers who may have similar requirements. The first table describes the journal entry types and is contained in the jetyp.sql script. You can run this script using the Run SQL Scripts option in Operations Navigator, or you can copy the script to a source file on your iSeries system and run it with the Run SQL Statement (RUNSQLSTM) command.

To use Operations Navigator to run jetyp.sql, start Operations Navigator and expand Databases. Right-click the database connection for your system and select Run SQL Scripts. Use the file open dialog to open jetyp.sql. Change YourLibrary on the set schema statement to the name of the library where you want to put the jetyp table. Now click the Run All icon that looks like an hour glass with three blue squares behind it.

You may find it useful to define a table for the access types associated with the YC, YR, ZC, and ZR entries. The access type gives more specific information about how an object was changed. For example, changing the text of a file generates a ZC journal entry, with a 7 access code; a file opened and updated will generate a ZC, with a 30 access code.

The access types SQL script, jeacc.sql, creates a table of access types. If you specify *TYPE5 for the output format on the DSPJRN command, the access type will be a packed value, embedded in positions 30 through 32 of column JOESD. To make it easier to join this embedded value to the JEACC table, I used hex values to specify the numeric codes in an alpha field.

--David

Sponsored By ADVANCED SYSTEMS CONCEPTS

Quoted from an experienced programmer new to the iSeries (AS/400): "The best thing about working on the AS/400 is using ASC's SEQUEL product." If you're tired of the limitations imposed by Query/400 or ODBC-based query and reporting tools, you need SEQUEL. Discover how thousands of sites around the world have improved access to iSeries data using SEQUEL's Windows- and Web-interfaces. It's the one tool you can rely on for virtually all your iSeries data access needs. FREE trial available. Read More about SEQUEL