Overview

This guide demonstrates how to track changes to object auditing values on IBM i systems using the SYSTOOLS.AUDIT_JOURNAL_AD table function. This function allows you to monitor when auditing settings are modified for both library objects and IFS (Integrated File System) objects.

What is AUDIT_JOURNAL_AD?

The SYSTOOLS.AUDIT_JOURNAL_AD table function retrieves audit journal entries of type AD (Auditing Change), which are created whenever the auditing value of an object is changed. This is critical for:

• Security Compliance: Track who modified audit settings and when
• Audit Trail: Maintain records of security configuration changes
• Incident Investigation: Identify when auditing was disabled or modified
• Change Management: Monitor unauthorized changes to audit settings

Prerequisites

System Requirements

• IBM i 7.4 or higher (7.4, 7.5+)
• Security auditing must be configured and active
• QAUDJRN (security audit journal) must exist

Required Authority

To query the audit journal, you need:

• *USE authority to the journal (QAUDJRN) and all requested journal receivers
• *OBJEXIST authority to the journal if querying objects that no longer exist
• Appropriate authorization to view security audit information

Security Audit Configuration

Verify Audit Settings

Before using AUDIT_JOURNAL_AD, ensure auditing is properly configured:

DSPSECAUD

Required Settings:

Security journal QAUDJRN exists . . . . . :  YES    
Current QAUDCTL system value  . . . . . . : *AUDLVL
Current QAUDLVL system value  . . . . . . : *SECURITY

Enable Auditing (If Needed)

If auditing is not configured:

CHGSECAUD QAUDCTL(*AUDLVL) QAUDLVL(*SECURITY)

Tracking Library Object Auditing Changes

SQL Query for Library Objects

This query tracks changes to auditing values for objects in libraries:

SELECT ENTRY_TIMESTAMP,
       QUALIFIED_JOB_NAME,
       JOB_USER,
       JOB_NUMBER,
       USER_NAME,
       OBJECT_NAME,
       LIBRARY_NAME,
       OBJECT_TYPE,
       PREV_OBJECT_AUDIT,
       OBJECT_AUDIT,
       CASE
           WHEN PREV_OBJECT_AUDIT = '*NONE' AND OBJECT_AUDIT <> '*NONE'
           THEN 'Auditing Enabled'
           WHEN PREV_OBJECT_AUDIT <> '*NONE' AND OBJECT_AUDIT = '*NONE'
           THEN 'Auditing Disabled'
           ELSE 'Auditing Modified'
       END AS CHANGE_TYPE
FROM TABLE(
    SYSTOOLS.AUDIT_JOURNAL_AD(
        STARTING_TIMESTAMP => CURRENT_TIMESTAMP - 30 DAYS,
        ENDING_TIMESTAMP => CURRENT_TIMESTAMP
    )
) AS AD
WHERE LIBRARY_NAME IS NOT NULL  -- Library objects only
  AND OBJECT_NAME LIKE '%'      -- All objects (modify as needed)
ORDER BY ENTRY_TIMESTAMP DESC;

NOTES:

• Update the date and time range values in the SQL statement to match the desired time range for your query.

Sample Results

Interpretation:

• Row 1: SECADMIN enabled auditing on PAYROLL file in PRODLIB (changed from *NONE to *CHANGE)
• Row 2: DEVELOPER disabled auditing on TESTPGM program in DEVLIB (changed from *CHANGE to *NONE)
• Row 3: ADMIN modified auditing level on CUSTFILE from *CHANGE to *ALL
• Row 4: JSMITH enabled comprehensive auditing on SECPGM (changed from *NONE to *ALL)

Understanding the Query

Common Auditing Values

Tracking IFS Object Auditing Changes

SQL Query for IFS Objects

This query tracks changes to auditing values for objects in the Integrated File System:

SELECT ENTRY_TIMESTAMP,
       QUALIFIED_JOB_NAME,
       JOB_USER,
       JOB_NUMBER,
       USER_NAME,
       PATH_NAME,
       PREV_OBJECT_AUDIT,
       OBJECT_AUDIT,
       CASE
           WHEN PREV_OBJECT_AUDIT = '*NONE' AND OBJECT_AUDIT <> '*NONE'
           THEN 'Auditing Enabled'
           WHEN PREV_OBJECT_AUDIT <> '*NONE' AND OBJECT_AUDIT = '*NONE'
           THEN 'Auditing Disabled'
           ELSE 'Auditing Modified'
       END AS CHANGE_TYPE,
       CASE
           WHEN PATH_NAME LIKE '/home/%' THEN 'User Home Directory'
           WHEN PATH_NAME LIKE '/tmp/%' THEN 'Temporary Files'
           WHEN PATH_NAME LIKE '/QSYS.LIB/%' THEN 'QSYS Library'
           WHEN PATH_NAME LIKE '/QOpenSys/%' THEN 'Open Source'
           ELSE 'Other IFS'
       END AS IFS_LOCATION
FROM TABLE(
    SYSTOOLS.AUDIT_JOURNAL_AD(
        STARTING_TIMESTAMP => CURRENT_TIMESTAMP - 30 DAYS,
        ENDING_TIMESTAMP => CURRENT_TIMESTAMP
    )
) AS AD
WHERE PATH_NAME IS NOT NULL  -- IFS objects only
  AND (PATH_NAME LIKE '%config%' OR PATH_NAME LIKE '%Config%')  -- Example filter
ORDER BY ENTRY_TIMESTAMP DESC;

NOTES:

• Update the date and time range values in the SQL statement to match the desired time range for your query.
• Specify the value in the PATH_NAME field using uppercase letters and also lower case letters . In the sample SQL, replace 'CONFIG' and 'config' with the name of the object that was deleted, moved, or renamed. Ensure the object name is inserted between the percent signs (%) when using the LIKE clause (e.g., LIKE '%CONFIG%').

Sample Results

Interpretation:

• Row 1: SECADMIN enabled auditing on application configuration file in /home/apps/config/
• Row 2: DEVELOPER disabled auditing on system configuration in /QOpenSys/etc/
• Row 3: ADMIN enabled comprehensive auditing on temporary config backup
• Row 4: JSMITH increased auditing level on production config from *CHANGE to *ALL

Understanding the Query

IFS Query Differences

Key Considerations for IFS Objects:

1. Case Sensitivity: IFS paths are case-sensitive. Use appropriate filters:

   WHERE (PATH_NAME LIKE '%config%' OR PATH_NAME LIKE '%Config%')

2. Path Filtering: Target specific IFS directories:

   WHERE PATH_NAME LIKE '/home/production/%'
      OR PATH_NAME LIKE '/QOpenSys/var/%'

3. Location Categorization: The IFS_LOCATION column helps identify which part of the IFS was affected

Best Practices

1. Regular Monitoring

• Schedule periodic reviews of auditing value changes
• Alert on critical changes (e.g., auditing disabled on production objects)
• Archive audit data for long-term compliance

2. Filtering Strategies

-- Focus on recent changes
WHERE ENTRY_TIMESTAMP > CURRENT_TIMESTAMP - 7 DAYS

-- Monitor specific libraries
WHERE LIBRARY_NAME IN ('PRODLIB', 'SECLIB')

-- Track auditing being disabled
WHERE OBJECT_AUDIT = '*NONE'

-- Monitor IFS security directories
WHERE PATH_NAME LIKE '/home/production/%'

3. Security Considerations

• Restrict access to audit journal queries
• Log who runs audit reports
• Implement alerts for unauthorized changes
• Document baseline audit settings

4. Performance Optimization

For large audit journals:

-- Use specific time ranges
STARTING_TIMESTAMP => '2024-01-01 00:00:00',
ENDING_TIMESTAMP => '2024-01-31 23:59:59'

-- Limit result sets
FETCH FIRST 1000 ROWS ONLY

-- Add appropriate indexes on filtered columns

Additional Resources

• IBM i Services: AUDIT_JOURNAL_AD Table Function
• IBM i Security Auditing
• DSPSECAUD Command
• CHGSECAUD Command
• Object Auditing Values

Summary

The SYSTOOLS.AUDIT_JOURNAL_AD table function provides powerful capabilities for tracking auditing value changes on IBM i systems:

• Library Objects: Monitor auditing changes for traditional IBM i objects (*FILE, *PGM, etc.)
• IFS Objects: Track auditing modifications for files and directories in the Integrated File System
• Security Compliance: Maintain comprehensive audit trails of security configuration changes
• Incident Response: Quickly identify when and by whom auditing was modified

By implementing these queries and best practices, you can maintain robust security monitoring and ensure compliance with audit requirements on your IBM i systems.