Audit – Track Last Login of Employee – Custom Sample Query
Objective
To get an idea on last login time of Fusion Users
Use case
Customer want to understand list of users who logged into Fusion Application in last n number days
Query 1
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
Pre-requisites of running this script:
It is mandatory to run the Import User Login History process. This process updates the security tables in fusion (ASE_USER_LOGIN_INFO) with user login details from FND_SESSIONS tables which stores the user information for 7 days before it purges automatically
Query 2
Below query can be used as well (but not recommended) .This should be used only if you are not planning to run import user login history process
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’)