Oracle HCM Sign in/Sign out REST API for Yearly Audit Reports 

Written by Mandeep, Principal Consultant at Fusion Practices

The problem

Many a times, the customer wishes to capture login/logouts details of end users into Oracle Cloud HCM. Needless to state, this data is crucial in order to ensure efficient functioning of Audit process. However, the barrier in achieving this is the lack of any standard report available to provide the details requested by customer administrator/ external auditor.  Oracle provides Audit REST APIs which can be used to get the sign in – sign out information of people logging into applications but it is massively limited in its timespan. The drawback of this approach is that only last 7 days of data is available using the rest APIs. The Audit report requires the customer to sift through a year’s worth of data and thus requiring an immediate solution to the issue. This blog delves into the aforementioned problem and provides an implementable solution. 

The solution

The solution is to make use of customer’s PaaS Database to store the audit information older than 7 days. Over the time, PaaS Database will have all the details of previous periods of login history for all workers who accessed the application. Custom login audit dashboards can be created using Oracle APEX taking into consideration the information stored in PaaS DB: 

Implementation Steps: 

Below are the high-level steps: 

1. Enable the profile ASE_ADVANCED_USER_MANAGEMENT_SETTING at site level. 

2. Check “Enable Administration Access for Sign In-Sign Out Audit REST API” for administrator at User page (Advanced Information) in Security console.   

3. Run the Audit REST API with date from and date to parameter values: 

https://xxyyzz-dev2.oracle.com/oam/services/rest/access/api/v1/audit/stats

4. Create a custom staging table on PaaS database and store the details. 

5. Create analysis based on custom stage table data using Oracle APEX. 

Points to Consider While Implementing this solution: 

1. This approach doesn’t trace the logins which have happened using REST APIs or web services. 

2. Only two event types are recorded which are Credential Validation and Logout. 

3. Additional PaaS Database is required to store data for more than 7 days. 

For details related to API, parameters and attributes please check Fusion Security: Using Sign In – Sign Out Audit REST API (Doc ID 2661308.1) on my oracle support.