Extracting and Analyzing Oracle Fusion User Audit Data

In Oracle Fusion, we  many times need to debug or analyze user activity via audit trails. Whether for security compliance, incident or troubleshooting, querying the audit tables directly provides more flexibility than standard UI reports.

Below are the most effective SQL queries for extracting specific user activity data.

Monitoring Password Resets

If we need to identify who initiated a password reset, use the following query. This example retrieves the 10 most recent records, but we can adjust the filters to target specific users or date ranges.

SELECT *
FROM   (SELECT *
        FROM   fusion.ase_pwd_reset_req
        ORDER  BY creation_date DESC)
WHERE  rownum < 10  

User Modifications - Who,When, How
SELECT *
FROM   fusion.ase_audit_user_changes
ORDER  BY audit_user_changes_id desc 




To track the lifecycle of a security event

Example: 

SELECT SA.action_type,
       SA.action_name,
       SAV.action_id,
       SAV.attribute_name,
       SAV.attribute_value_text,
       SAV.created_by,
       SAV.last_updated_by,
       SAV.last_update_date
FROM   fusion.ase_staged_action SA
       JOIN fusion.ase_staged_action_value SAV
         ON SA.action_id = SAV.action_id
WHERE  SA.action_type LIKE 'USER'
       AND SA.action_name = 'DELETE'
       AND To_char(SAV.last_update_date, 'YYYY-MM-DD')>  '2024-05-14' 
ORDER  BY SA.last_update_date DESC 

For All users  - Suspended/Activate and ldap request completed

SELECT pu.username          "USERNAME",
       plu.request_type     "ACCOUNT STATUS",
       plu.last_update_date "DATE",
       plu.last_updated_by  "BY"
FROM   fusion.per_ldap_users plu,
       fusion.per_users pu
WHERE  plu.user_guid = pu.user_guid
       AND plu.request_type IN ( 'ACTIVATE', 'SUSPEND' )
       AND plu.request_status = 'COMPLETE'
ORDER  BY pu.username,
          plu.last_update_date DESC   

We can filter based on specific user or date range:

SELECT pu.username          "USERNAME",
       plu.request_type     "ACCOUNT STATUS",
       plu.last_update_date "DATE",
       plu.last_updated_by  "BY"
FROM   fusion..per_ldap_users plu,
       fusion.per_users pu
WHERE  plu.user_guid = pu.user_guid
       AND plu.request_type IN ( 'ACTIVATE', 'SUSPEND' )
       AND plu.request_status = 'COMPLETE'
       AND PU.username = '<ENTER USERNAME>' 

 

SELECT pu.username          "USERNAME",
       plu.request_type     "ACCOUNT STATUS",
       plu.last_update_date "DATE",
       plu.last_updated_by  "BY"
FROM   fusion.per_ldap_users plu,
       fusion.per_users pu
WHERE  plu.user_guid = pu.user_guid
       AND plu.request_type IN ( 'ACTIVATE', 'SUSPEND' )
       AND plu.request_status = 'COMPLETE'
       AND plu.last_update_date BETWEEN To_date('01/01/2024', 'MM/DD/YYYY') AND 
                                        To_date( 
                                        '12/15/2025', 'MM/DD/YYYY') 
ORDER  BY pu.username,
          plu.last_update_date