IBM i Audit Journal User Profile Changes - SYSTOOLS.AUDIT_JOURNAL_CP

Steps

SQL Query Examples

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

1. All User Profile Changes (Last 7 Days)

SELECT
    ENTRY_TIMESTAMP,
    USER_NAME,
    JOB_NAME,
    USER_PROFILE,
    COMMAND_TYPE,
    PROGRAM_NAME,
    PROGRAM_LIBRARY,
    REMOTE_ADDRESS,
    STATUS
FROM TABLE(SYSTOOLS.AUDIT_JOURNAL_CP(
    STARTING_TIMESTAMP => CURRENT_TIMESTAMP - 7 DAYS
)) AS AJ
ORDER BY ENTRY_TIMESTAMP DESC;

2. User Profile Creation Events

SELECT
    ENTRY_TIMESTAMP,
    USER_NAME AS CREATED_BY,
    USER_PROFILE AS NEW_PROFILE,
    JOB_NAME,
    PROGRAM_NAME,
    PROGRAM_LIBRARY,
    REMOTE_ADDRESS
FROM TABLE(SYSTOOLS.AUDIT_JOURNAL_CP(
    STARTING_TIMESTAMP => CURRENT_TIMESTAMP - 30 DAYS
)) AS AJ
WHERE COMMAND_TYPE = 'CRT'
ORDER BY ENTRY_TIMESTAMP DESC;

3. Profile Modifications by Specific User

SELECT
    ENTRY_TIMESTAMP,
    USER_PROFILE AS PROFILE_CHANGED,
    COMMAND_TYPE,
    JOB_NAME,
    PROGRAM_NAME,
    REMOTE_ADDRESS
FROM TABLE(SYSTOOLS.AUDIT_JOURNAL_CP(
    STARTING_TIMESTAMP => CURRENT_TIMESTAMP - 90 DAYS
)) AS AJ
WHERE USER_NAME = 'QSECOFR'
ORDER BY ENTRY_TIMESTAMP DESC;

4. Changes to Specific User Profile

SELECT
    ENTRY_TIMESTAMP,
    USER_NAME AS CHANGED_BY,
    COMMAND_TYPE,
    JOB_NAME,
    PROGRAM_NAME,
    PROGRAM_LIBRARY,
    REMOTE_ADDRESS
FROM TABLE(SYSTOOLS.AUDIT_JOURNAL_CP(
    STARTING_TIMESTAMP => CURRENT_TIMESTAMP - 60 DAYS
)) AS AJ
WHERE USER_PROFILE = 'JOHNDOE'
ORDER BY ENTRY_TIMESTAMP DESC;

5. Changes to the Status of a profile

SELECT
    ENTRY_TIMESTAMP,
    USER_NAME AS ATTEMPTED_BY,
    USER_PROFILE AS TARGET_PROFILE,
    COMMAND_TYPE,
    JOB_NAME,
    PROGRAM_NAME,
    REMOTE_ADDRESS,
    STATUS
FROM TABLE(SYSTOOLS.AUDIT_JOURNAL_CP(
    STARTING_TIMESTAMP => CURRENT_TIMESTAMP - 30 DAYS
)) AS AJ
WHERE STATUS <> 'SUCCESS'
ORDER BY ENTRY_TIMESTAMP DESC;

6. Profile Changes from Remote Connections

SELECT
    ENTRY_TIMESTAMP,
    USER_NAME,
    USER_PROFILE AS PROFILE_CHANGED,
    COMMAND_TYPE,
    REMOTE_ADDRESS,
    REMOTE_PORT,
    JOB_NAME,
    PROGRAM_NAME
FROM TABLE(SYSTOOLS.AUDIT_JOURNAL_CP(
    STARTING_TIMESTAMP => CURRENT_TIMESTAMP - 30 DAYS
)) AS AJ
WHERE REMOTE_ADDRESS IS NOT NULL
  AND REMOTE_ADDRESS <> ''
ORDER BY ENTRY_TIMESTAMP DESC;

7. Summary Report - Profile Changes by User

SELECT
    USER_NAME,
    COUNT(*) AS TOTAL_CHANGES,
    SUM(CASE WHEN COMMAND_TYPE = 'CRT' THEN 1 ELSE 0 END) AS PROFILES_CREATED,
    SUM(CASE WHEN COMMAND_TYPE = 'CHG' THEN 1 ELSE 0 END) AS PROFILES_CHANGED,
    SUM(CASE WHEN COMMAND_TYPE = 'RST' THEN 1 ELSE 0 END) AS PROFILES_RESTORED,
    MIN(ENTRY_TIMESTAMP) AS FIRST_CHANGE,
    MAX(ENTRY_TIMESTAMP) AS LAST_CHANGE
FROM TABLE(SYSTOOLS.AUDIT_JOURNAL_CP(
    STARTING_TIMESTAMP => CURRENT_TIMESTAMP - 90 DAYS
)) AS AJ
GROUP BY USER_NAME
ORDER BY TOTAL_CHANGES DESC;

8. Summary Report - Changes by Profile

SELECT
    USER_PROFILE,
    COUNT(*) AS TOTAL_CHANGES,
    COUNT(DISTINCT USER_NAME) AS UNIQUE_USERS,
    MIN(ENTRY_TIMESTAMP) AS FIRST_CHANGE,
    MAX(ENTRY_TIMESTAMP) AS LAST_CHANGE,
    LISTAGG(DISTINCT USER_NAME, ', ')
        WITHIN GROUP (ORDER BY USER_NAME) AS CHANGED_BY_USERS
FROM TABLE(SYSTOOLS.AUDIT_JOURNAL_CP(
    STARTING_TIMESTAMP => CURRENT_TIMESTAMP - 90 DAYS
)) AS AJ
GROUP BY USER_PROFILE
HAVING COUNT(*) > 1
ORDER BY TOTAL_CHANGES DESC;

9. Daily Activity Report

SELECT
    DATE(ENTRY_TIMESTAMP) AS CHANGE_DATE,
    COUNT(*) AS TOTAL_CHANGES,
    COUNT(DISTINCT USER_NAME) AS UNIQUE_USERS,
    COUNT(DISTINCT USER_PROFILE) AS PROFILES_AFFECTED,
    SUM(CASE WHEN COMMAND_TYPE = 'CRT' THEN 1 ELSE 0 END) AS CREATES,
    SUM(CASE WHEN COMMAND_TYPE = 'CHG' THEN 1 ELSE 0 END) AS CHANGES,
    SUM(CASE WHEN COMMAND_TYPE = 'RST' THEN 1 ELSE 0 END) AS RESTORES
FROM TABLE(SYSTOOLS.AUDIT_JOURNAL_CP(
    STARTING_TIMESTAMP => CURRENT_TIMESTAMP - 30 DAYS
)) AS AJ
GROUP BY DATE(ENTRY_TIMESTAMP)
ORDER BY CHANGE_DATE DESC;

10. After-Hours Profile Changes

SELECT
    ENTRY_TIMESTAMP,
    USER_NAME,
    USER_PROFILE AS PROFILE_CHANGED,
    COMMAND_TYPE,
    JOB_NAME,
    PROGRAM_NAME,
    REMOTE_ADDRESS,
    HOUR(ENTRY_TIMESTAMP) AS CHANGE_HOUR
FROM TABLE(SYSTOOLS.AUDIT_JOURNAL_CP(
    STARTING_TIMESTAMP => CURRENT_TIMESTAMP - 30 DAYS
)) AS AJ
WHERE HOUR(ENTRY_TIMESTAMP) NOT BETWEEN 8 AND 17
   OR DAYOFWEEK(ENTRY_TIMESTAMP) IN (1, 7)
ORDER BY ENTRY_TIMESTAMP DESC;

11. Changes by Program

SELECT
    PROGRAM_LIBRARY,
    PROGRAM_NAME,
    COUNT(*) AS TOTAL_CHANGES,
    COUNT(DISTINCT USER_PROFILE) AS PROFILES_AFFECTED,
    COUNT(DISTINCT USER_NAME) AS UNIQUE_USERS,
    MIN(ENTRY_TIMESTAMP) AS FIRST_USE,
    MAX(ENTRY_TIMESTAMP) AS LAST_USE
FROM TABLE(SYSTOOLS.AUDIT_JOURNAL_CP(
    STARTING_TIMESTAMP => CURRENT_TIMESTAMP - 90 DAYS
)) AS AJ
WHERE PROGRAM_NAME IS NOT NULL
GROUP BY PROGRAM_LIBRARY, PROGRAM_NAME
ORDER BY TOTAL_CHANGES DESC;

12. Comprehensive Audit Report

SELECT
    ENTRY_TIMESTAMP,
    USER_NAME,
    JOB_NAME,
    USER_PROFILE AS PROFILE_CHANGED,
    COMMAND_TYPE,
    SPECIAL_AUTHORITIES,
    PROGRAM_NAME,
    PROGRAM_LIBRARY,
    REMOTE_ADDRESS,
    STATUS,
    CASE
        WHEN HOUR(ENTRY_TIMESTAMP) BETWEEN 8 AND 17
             AND DAYOFWEEK(ENTRY_TIMESTAMP) BETWEEN 2 AND 6
        THEN 'Business Hours'
        ELSE 'After Hours'
    END AS TIME_CATEGORY,
    CASE
        WHEN REMOTE_ADDRESS IS NOT NULL AND REMOTE_ADDRESS <> ''
        THEN 'Remote'
        ELSE 'Local'
    END AS CONNECTION_TYPE
FROM TABLE(SYSTOOLS.AUDIT_JOURNAL_CP(
    STARTING_TIMESTAMP => CURRENT_TIMESTAMP - 30 DAYS
)) AS AJ
ORDER BY ENTRY_TIMESTAMP DESC;

Common Use Cases:

Security Auditing

Track privileged profile changes for security compliance:

SELECT
    ENTRY_TIMESTAMP,
    USER_NAME AS AUDITOR,
    USER_PROFILE AS PROFILE,
    COMMAND_TYPE,
    REMOTE_ADDRESS,
    STATUS
FROM TABLE(SYSTOOLS.AUDIT_JOURNAL_CP(
    STARTING_TIMESTAMP => CURRENT_TIMESTAMP - 7 DAYS
)) AS AJ
WHERE USER_PROFILE IN ('QSECOFR', 'QSYSOPR', 'QPGMR')
   OR USER_NAME IN ('QSECOFR', 'QSYSOPR')
ORDER BY ENTRY_TIMESTAMP DESC;

Compliance Reporting

Generate audit trail for regulatory compliance:

SELECT
    DATE(ENTRY_TIMESTAMP) AS CHANGE_DATE,
    TIME(ENTRY_TIMESTAMP) AS CHANGE_TIME,
    USER_NAME AS CHANGED_BY,
    USER_PROFILE,
    COMMAND_TYPE AS ACTION,
    JOB_NAME AS JOB,
    PROGRAM_LIBRARY || '/' || PROGRAM_NAME AS PROGRAM,
    COALESCE(REMOTE_ADDRESS, 'LOCAL') AS SOURCE,
    STATUS
FROM TABLE(SYSTOOLS.AUDIT_JOURNAL_CP(
    STARTING_TIMESTAMP => CURRENT_TIMESTAMP - 90 DAYS
)) AS AJ
ORDER BY ENTRY_TIMESTAMP DESC;

Change Management

Analyze changes by time window:

SELECT
    ENTRY_TIMESTAMP,
    USER_NAME,
    USER_PROFILE AS PROFILE,
    COMMAND_TYPE,
    CASE
        WHEN DAYOFWEEK(ENTRY_TIMESTAMP) = 1 THEN 'Sunday'
        WHEN DAYOFWEEK(ENTRY_TIMESTAMP) = 2 THEN 'Monday'
        WHEN DAYOFWEEK(ENTRY_TIMESTAMP) = 3 THEN 'Tuesday'
        WHEN DAYOFWEEK(ENTRY_TIMESTAMP) = 4 THEN 'Wednesday'
        WHEN DAYOFWEEK(ENTRY_TIMESTAMP) = 5 THEN 'Thursday'
        WHEN DAYOFWEEK(ENTRY_TIMESTAMP) = 6 THEN 'Friday'
        WHEN DAYOFWEEK(ENTRY_TIMESTAMP) = 7 THEN 'Saturday'
    END AS DAY_OF_WEEK,
    HOUR(ENTRY_TIMESTAMP) AS HOUR_OF_DAY,
    CASE
        WHEN DAYOFWEEK(ENTRY_TIMESTAMP) IN (1, 7) THEN 'Weekend'
        WHEN HOUR(ENTRY_TIMESTAMP) BETWEEN 22 AND 23
             OR HOUR(ENTRY_TIMESTAMP) BETWEEN 0 AND 6 THEN 'Maintenance Window'
        ELSE 'Business Hours'
    END AS CHANGE_WINDOW
FROM TABLE(SYSTOOLS.AUDIT_JOURNAL_CP(
    STARTING_TIMESTAMP => CURRENT_TIMESTAMP - 30 DAYS
)) AS AJ
ORDER BY ENTRY_TIMESTAMP DESC;

Best Practices

Performance

• Always specify STARTING_TIMESTAMP to limit data scanned
• Select only needed columns to reduce data transfer
• Use appropriate time windows for queries
• Add FETCH FIRST n ROWS ONLY for testing

Security

• Restrict access to audit journal queries to authorized personnel
• Monitor who is querying audit data
• Store query results securely
• Schedule regular reviews of user profile changes

Related Resources

• IBM i Audit Journal Services
• IBM i Security Reference

Related Table Functions

• AUDIT_JOURNAL_PW - Password changes and sign-on attempts
• AUDIT_JOURNAL_AF - Authority failures
• AUDIT_JOURNAL - All audit journal entries

Parameters

Key Columns

COMMAND_TYPE Values