Most of the customers want to know the list of users who logged in to Fusion Applications and want to encourage other employees too to use the Fusion system. For this, the customer needs to generate a report with the list of users who have logged in within a period of time like last 30days or on specific date or in last 6 months, etc.
In order to report on the logged in user history, we need to schedule “Importing User Login History” process to be run daily which updates the security tables in Fusion (ASE_USER_LOGIN_INFO) with the User login details from “FND_SESSIONS” table which stores the user information for 7 days before it gets purged automatically.
Steps to Schedule or run ad-hoc basis:
- Open the Scheduled Processes work area from Navigator > Tools > Scheduled Processes
- In the Search Results section of the Overview page, click Schedule New Process.
- In the Schedule New Process dialog box, search for and select the “Import User Login History“. Click OK.
- In the Process Details dialog box, click Advanced.
- On the Schedule tab, set Run to Using a schedule.
- Set Frequency to Daily and Every to 1. Enter start and end dates and times.
- Click Submit. Click OK to close the Confirmation message.
Once the User History Import is done, we can create a BI Report to report the user data.
SQL Query to get list of users who logged in last 30 days:
SELECT U.USERNAME, to_char(U.CREATION_DATE, 'dd-mm-yyyy') CREATION_DATE, to_char(A.LAST_LOGIN_DATE, 'dd-mm-yyyy') LAST_LOGIN_DATE FROM ASE_USER_LOGIN_INFO A,PER_USERS U WHERE A.USER_GUID = U.USER_GUID AND U.USERNAME NOT LIKE 'FUSION_APPS_%' AND A.LAST_LOGIN_DATE IS NOT NULL AND A.LAST_LOGIN_DATE > sysdate - 30
If you aren’t using this process, then you can use the FND_SESSIONS table query to get the required information, however this table might get purged and it is not suggested to go this way.
SELECT DISTINCT user_name "LOGIN_USERNAME" ,to_char(last_connect, 'dd-mm-yyyy') "LAST_LOGIN_DATE" FROM fnd_sessions WHERE user_name NOT LIKE 'FUSION_APPS%' ORDER BY to_char(last_connect, 'dd-mm-yyyy')
If you have any questions, please feel free to reach out to me by posting in comments section.
If you are interested in learning all Fusion Technical tools go through this article.
If you liked the article, please share it with your friends/ colleagues/ teammates or anyone who might also benefit from it.
Top 10 Popular Posts from our Knowledgebase
- DataFusing FREE Cloud based SQL Developer like tool for Oracle Cloud
- Core HR Entity Relationship Diagram (ERD) for HCM Cloud
- Most Frequently used Document IDs from Oracle Support
- FREE Oracle Training & Accreditation with Oracle Learning Explorer
- How HDL Transformation Formula works with ElementEntry Sample
- FREE Oracle Fusion Demo Instance (VISION) Access
- Top 10 FREE useful tools for Fusion Consultants
- Learning Fusion HCM Technical Skillset
- Sample HCM Data Loader (HDL) files for all Business Objects
- Most frequently used Tables list in Fusion HCM