Turning on the audit journal and reading it with SQL

QAUDJRN records who did what and when. It is not active by default, and it cannot be enabled retroactively: events preceding activation are not reconstructable from any other source.

Checking the state

DSPSYSVAL SYSVAL(QAUDCTL)

*NONE means auditing is off. *AUDLVL means it is on, with the events listed in QAUDLVL and QAUDLVL2.

Turning it on

CHGSECAUD QAUDCTL(*AUDLVL) QAUDLVL(*DFTSET)

The command does not merely set the system values: if the QAUDJRN journal does not exist in QSYS, it creates it together with the first receiver and attaches it. It requires the *ALLOBJ and *AUDIT special authorities.

If auditing has never been switched on, the queries in this article will not run:

SQL0443  DISPLAY_JOURNAL ... JOURNAL QAUDJRN NOT FOUND IN LIBRARY QSYS

*DFTSET enables the default set of events.

Alternatively the levels are listed individually: *AUTFAIL, *CREATE, *DELETE, *SECURITY and the others.

Receiver growth has to be planned at the same time. Auditing enabled without a procedure for detaching and deleting receivers consumes space continuously and without threshold. The procedure is in freeing space from journalreceivers, and it belongs in the same piece of work that enables auditing.

The query functions

Entries are classified by type:

• PW Password attempts

• AF Authority failures

• CP User profile changes

• CD Commands run

• SV System value changes

For each type there is a dedicated table function:

SELECT * FROM TABLE(SYSTOOLS.AUDIT_JOURNAL_PW());

The library is SYSTOOLS, not QSYS2. It is the only relevant family of SQL services not in QSYS2.

Without arguments the window is the last day. The parameters are common to the whole family:

Three basic queries

Failed authentication attempts over the last seven days:

SELECT ENTRY_TIMESTAMP, USER_NAME, QUALIFIED_JOB_NAME,
       REMOTE_ADDRESS, VIOLATION_TYPE_DETAIL
  FROM TABLE(SYSTOOLS.AUDIT_JOURNAL_PW(
         STARTING_TIMESTAMP => CURRENT TIMESTAMP - 7 DAYS))
 ORDER BY ENTRY_TIMESTAMP DESC;

Access denied for lack of authority, with the requested object:

SELECT ENTRY_TIMESTAMP, USER_NAME, OBJECT_LIBRARY, OBJECT_NAME, OBJECT_TYPE
  FROM TABLE(SYSTOOLS.AUDIT_JOURNAL_AF(
         STARTING_TIMESTAMP => CURRENT TIMESTAMP - 7 DAYS))
 ORDER BY ENTRY_TIMESTAMP DESC;

User profile changes over the last month:

SELECT ENTRY_TIMESTAMP, USER_NAME, USER_PROFILE, ENTRY_TYPE_DETAIL,
       PREVIOUS_SPECIAL_AUTHORITIES, SPECIAL_AUTHORITIES
  FROM TABLE(SYSTOOLS.AUDIT_JOURNAL_CP(
         STARTING_TIMESTAMP => CURRENT TIMESTAMP - 30 DAYS))
 ORDER BY ENTRY_TIMESTAMP DESC;

Two columns here need telling apart, and confusing them makes you read the list backwards:

USER_NAME is who made the change, USER_PROFILE is the profile that was changed.

PREVIOUS_SPECIAL_AUTHORITIES and SPECIAL_AUTHORITIES are the before and after. That is the pair answering the question actually asked during a review: who granted *ALLOBJ to whom, and when, without reconstructing it by comparing two snapshots of USER_INFO taken at different times. The journal carries it in one row.

The before and after exist for every special authority, one by one. Alongside the two summary columns there are ALLOBJ, SECADM, SAVSYS, SPLCTL, SERVICE, AUDIT, JOBCTL, IOSYSCFG and their PREVIOUS_ counterparts. They help when the question is about one authority only: filtering on ALLOBJ is more precise than searching for a string inside SPECIAL_AUTHORITIES.

AUDIT_JOURNAL_AF is the most informative of the three during analysis: authority failures describe the paths attempted, not only those that succeeded.

The columns are not the same for all of them. The parameters are, the columns are not:

beyond a common core each function exposes its own, tied to the entry type. The textual description, for instance, is called VIOLATION_TYPE_DETAIL on AUDIT_JOURNAL_PW and ENTRY_TYPE_DETAIL on AUDIT_JOURNAL_CP. Before writing a query against a new function it is worth looking at what it actually returns:

SELECT * FROM TABLE(SYSTOOLS.AUDIT_JOURNAL_CD()) WHERE 1 = 0;

The always-false condition returns no rows and leaves the column headings.

Example: from activation to first read

Full sequence on a system where auditing has never been enabled.

DSPSYSVAL SYSVAL(QAUDCTL)

If it returns *NONE, enable it:

CHGSECAUD QAUDCTL(*AUDLVL) QAUDLVL(*DFTSET)

From this point entries accumulate. To generate one immediately, a single sign-on attempt with a wrong password is enough; after a few minutes the read below is at once the proof that the journal was created and that it is recording:

SELECT ENTRY_TIMESTAMP, USER_NAME, REMOTE_ADDRESS, VIOLATION_TYPE_DETAIL
  FROM TABLE(SYSTOOLS.AUDIT_JOURNAL_PW())
 ORDER BY ENTRY_TIMESTAMP DESC;

The rows below are illustrative, shown to convey the shape of the result; they do not come from a real system.

ENTRY_TIMESTAMP             USER_NAME  REMOTE_ADDRESS  VIOLATION_TYPE_DETAIL
2026-08-26 09:14:52.318000  MROSSI     10.0.7.44       Password not valid
2026-08-26 09:14:31.007000  MROSSI     10.0.7.44       Password not valid
2026-08-26 09:14:08.664000  MROSSI     10.0.7.44       Password not valid

Three attempts from the same profile, from the same workstation, twenty seconds apart: that is someone mistyping. The same profile from different origins, or at regular intervals, describes something else; and that is the distinction REMOTE_ADDRESS makes legible and that a count of failed attempts, on its own, does not.

To understand what was attempted after a successful sign-on, the reading continues on AUDIT_JOURNAL_AF filtered on the same profile.

Read authority

The *AUDIT special authority is required, along with authority over QAUDJRN and its receivers.

The restriction has a substantive rather than merely formal justification: the journal contains the list of active profiles, the hours in which they operate and the objects they use. It is a profile of the organisation, and it warrants the same protection as the data it documents.

Which versions this works on

CHGSECAUD and QAUDJRN have always existed. The SYSTOOLS.AUDIT_JOURNAL_xx functions arrived with IBM i 7.4 TR4 and 7.3 TR10 — the first four, AF, CA, OW, PW — and were extended with 7.4 TR5 and 7.3 TR11, which added among others CD, CO, CP, DO, EV, GR and SV.

On earlier releases DSPJRN on QAUDJRN with OUTFILE and QSYS2.DISPLAY_JOURNAL remain.

Sources

• IBM Documentation — AUDIT JOURNAL table function common information

• IBM Documentation — Change Security Auditing (CHGSECAUD)

• IBM — Find the Distribution of Journal Entry Types in the Audit Journal