Steps

Audit Journal Service Used

This solution uses the table function

• SYSTOOLS.AUDIT_JOURNAL_DO

This service returns rows from the system audit journal that correspond to DO (Delete Operation) entries. 

Correlation Strategy

All deletions performed during a user profile delete occur within the same job. The QUALIFIED_JOB_NAME column is therefore used to correlate the *USRPRF delete entry with subsequent object delete entries written by the same job.
 

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 Deleted_Profiles AS (
    SELECT
        ENTRY_TIMESTAMP        AS PROFILE_DELETE_TIMESTAMP,
        OBJECT_NAME            AS USER_PROFILE,
        QUALIFIED_JOB_NAME,
        USER_NAME              AS DELETING_USER
    FROM TABLE (
        SYSTOOLS.AUDIT_JOURNAL_DO(
            STARTING_TIMESTAMP => CURRENT_TIMESTAMP - 7 DAYS
        )
    ) AJ
    WHERE OBJECT_TYPE = '*USRPRF'
      AND ENTRY_TYPE IN ('A', 'C')
),

Deleted_Objects AS (
    SELECT
        ENTRY_TIMESTAMP        AS OBJECT_DELETE_TIMESTAMP,
        OBJECT_LIBRARY,
        OBJECT_NAME,
        OBJECT_TYPE,
        OBJECT_ATTRIBUTE,
        PATH_NAME,
        ENTRY_TYPE,
        QUALIFIED_JOB_NAME
    FROM TABLE (
        SYSTOOLS.AUDIT_JOURNAL_DO(
            STARTING_TIMESTAMP => CURRENT_TIMESTAMP - 7 DAYS
        )
    ) AJ
    WHERE OBJECT_TYPE <> '*USRPRF'
      AND ENTRY_TYPE IN ('A', 'C', 'P')
)

SELECT
    P.USER_PROFILE              AS DELETED_USER_PROFILE,
    P.DELETING_USER             AS DELETED_BY_USER,
    P.QUALIFIED_JOB_NAME        AS DELETE_JOB,
    O.OBJECT_LIBRARY,
    O.OBJECT_NAME,
    O.OBJECT_TYPE,
    O.OBJECT_ATTRIBUTE,
    O.PATH_NAME,
    O.ENTRY_TYPE                AS DELETE_ENTRY_TYPE,
    O.OBJECT_DELETE_TIMESTAMP
FROM Deleted_Profiles P
LEFT JOIN Deleted_Objects O
  ON O.QUALIFIED_JOB_NAME = P.QUALIFIED_JOB_NAME
ORDER BY
    P.USER_PROFILE,
    O.OBJECT_DELETE_TIMESTAMP;

NOTES:

• The STARTING_TIMESTAMP parameter should be adjusted as needed for historical reporting. 
• The report defaults to showing activity from the last seven days.
• Execution time for this SQL statement may vary depending on the size of the audit journal and the time range specified.
• The report includes both Library Type objects and IFS files.

Sample Report

Report Output Description

Each row in the report represents an object deletion that occurred as part of a user profile deletion. The key columns are:

• DELETED_USER_PROFILE: The name of the deleted user profile
• DELETED_BY_USER: The user profile that initiated the delete
• DELETE_JOB: The qualified job name that executed the delete
• OBJECT_LIBRARY: Library containing the deleted object (NULL for some types)
• OBJECT_NAME: Name of the deleted object
• OBJECT_TYPE: Type of object deleted (*FILE, *PGM, etc.)
• OBJECT_ATTRIBUTE: Object attribute, when applicable
• PATH_NAME: IFS path for stream files and directories
• OBJECT_DELETE_TIMESTAMP: Timestamp of the object deletion

Use Cases

• Security and compliance audits
• Verification of user provisioning standards
• Detection of Deleted Objects at the time a profile is deleted/
• Incident response and forensic investigations

Conclusion