Steps

Background

IBM i writes audit journal entries to QAUDJRN when user profiles are created or changed. Two audit journal entry types are relevant for reporting on new user profiles:

• CO (Create Object) – Records object creation, including *USRPRF objects.
• CP (User Profile Changes) – Records creation-time and change-time user profile attributes.

Audit Journal Services Used

• AUDIT_JOURNAL_CO is used to identify the creation of user profile objects (*USRPRF).
• AUDIT_JOURNAL_CP is used to retrieve the attributes that were explicitly specified during profile creation.
• Each attribute in CP entries is represented by its own column and populated only when that attribute is set.

SQL Report Definition

The following SQL statement reports newly created user profiles along with key attributes assigned at creation time.  The query correlates CO and CP audit entries using the profile name and timestamp sequencing.

Using the Run SQL Scripts tool, run the following SQL Statement:

WITH Profile_Creation AS (
    SELECT
        co.OBJECT_NAME           AS USER_PROFILE,
        co.ENTRY_TIMESTAMP       AS PROFILE_CREATE_TIMESTAMP,
        cp.STATUS,
        cp.GROUP_PROFILE_NAME    AS GROUP_PROFILE,
        cp.USER_CLASS_NAME       AS USER_CLASS,
        cp.SPECIAL_AUTHORITIES,
        cp.INITIAL_PROGRAM,
        cp.INITIAL_MENU,
        cp.JOB_DESCRIPTION,
        cp.LIMIT_CAPABILITIES,
        cp.PASSWORD_EXPIRED,
        cp.PASSWORD_CHANGED,
        cp.MAXIMUM_ALLOWED_STORAGE,
        cp.ENTRY_TIMESTAMP       AS CP_ENTRY_TIMESTAMP,
        ROW_NUMBER() OVER (
            PARTITION BY co.OBJECT_NAME
            ORDER BY cp.ENTRY_TIMESTAMP
        ) AS RN
    FROM
        TABLE(
            SYSTOOLS.AUDIT_JOURNAL_CO(
                STARTING_TIMESTAMP => TIMESTAMP('2026-03-30-00.00.00'),
                ENDING_TIMESTAMP   => CURRENT_TIMESTAMP
            )
        ) AS co
        INNER JOIN
        TABLE(
            SYSTOOLS.AUDIT_JOURNAL_CP(
                STARTING_TIMESTAMP => TIMESTAMP('2026-03-30-00.08.00'),
                ENDING_TIMESTAMP   => CURRENT_TIMESTAMP
            )
        ) AS cp
            ON cp.USER_PROFILE = co.OBJECT_NAME
            AND cp.COMMAND_TYPE = 'CRT'
            AND cp.ENTRY_TIMESTAMP >= co.ENTRY_TIMESTAMP
    WHERE
        co.OBJECT_TYPE = '*USRPRF'
        AND cp.COMMAND_TYPE = 'CRT'
)
SELECT
    USER_PROFILE,
    PROFILE_CREATE_TIMESTAMP,
    STATUS,
    GROUP_PROFILE,
    USER_CLASS,
    SPECIAL_AUTHORITIES,
    INITIAL_PROGRAM,
    INITIAL_MENU,
    JOB_DESCRIPTION,
    LIMIT_CAPABILITIES,
    PASSWORD_EXPIRED,
    PASSWORD_CHANGED,
    MAXIMUM_ALLOWED_STORAGE,
    CP_ENTRY_TIMESTAMP
FROM
    Profile_Creation
WHERE
    RN = 1
ORDER BY
    USER_PROFILE;

NOTES:

• Adjust the Time Frame on the SQL
• Execution time for this SQL statement may vary depending on the size of the audit journal and the time range specified.
• If a user profile is modified after its initial creation, only the CP entry corresponding to the profile’s creation is included in the report.

Sample Report

Interpreting the Results

• Each row represents a user profile creation event.
• Non-NULL attribute columns indicate values explicitly specified on CRTUSRPRF.
• NULL values indicate that system defaults were applied.

Use Cases

• Security and compliance audits
• Verification of user provisioning standards
• Detection of privileged access assignments at creation time
• Incident response and forensic investigations

Conclusion

Using Audit Journal Services provides a reliable, supported mechanism to report on new user profiles and their attributes. This SQL-based approach requires no exit programs or permanent database objects and aligns with IBM i auditing best practices.