Skip to content
Welcome to World of ORACLE

Welcome to World of ORACLE

Learn Oracle World with me

  • Home
  • EBS Blogs
    • ADF Developer Guide
    • ORACLE OAF Guide
    • EBS HRMS API
    • EBS FND User API
    • EBS Payables API
    • Oracle Knowledge
    • Solution Corner
  • FUSION Blogs
    • Oracle JET
    • Audit
    • Fast Formulas
    • HDL Templates
    • Reports/ Analytics
    • Solution Corner
  • About
  • Contact
You Are Here
  • Home
  • Audit
  • Audit – Track Last Login of Employee – Custom Sample Query

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’)

Share this post: on Twitter on Facebook on LinkedIn

Post navigation

Masking – Override Notification Email Address
Track Deleted Payroll Data – Oracle Delivered Standard Audit Reports

Related Posts

  • Configure Audit in BI Publisher and Standard Reports

  • Enable Audit and Use Standard Reports Functionality

  • Audit Assignment Changes

About Me

PRajkumar

Hi, I’m Puneet Rajkumar, an aspiring blogger with an obsession of Oracle Apps. This site is devoted to assist people to be told Oracle World.

View all posts

Follow Me

Be at liberty to hitch me on my social networks

  • facebook
  • linkedin

Visitor Count

0064978
Total Visit : 64978
Total Hits : 139912
  • Privacy Policy
  • Disclaimer
All Right Reserved 2019
Proudly powered by WordPress | Theme: Blog Cycle by Candid Themes.