SQL Query to get User last login information
SQL Query to get User last login information

 2,746 total views

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.

The following two tabs change content below.
Sricharan is a Fusion HCM Solution Architect with over 13+ years of overall experience and 5+ years of Fusion experience. He is passionate about technical aspects of Fusion HCM and writes interesting articles on HCM Extracts, HCM Data Loader, Fast Formula, BI Publisher, Integrations and Automation etc.

This Post Has 10 Comments

  1. manish

    Hi Sricharan,

    Do we have this officially documented from Oracle? We are doing this but the ASE_USER_LOGIN_INFO table stores data only for the last login and not the history of say a month or year.

    Thanks,
    Manish

  2. Hi Manish,

    Sorry for the late reply.. Yes it is officially documented by Oracle.. I saw it one of the Document ID on Oracle Support.

    Thanks,
    Sricharan

  3. Check the Doc ID How To Resend Email Notification with User ID And Password To All The Users Who Never Logged In (Doc ID 2008728.1) and How To Purge FUSION.FND_SESSIONS (Doc ID 1621145.1). Once the FND_SESSIONS table size becomes huge, oracle will purge these tables.. so till then you can use it.. however if you are not scheduling the process, then you would lose the data once Oracle purges the FND_SESSIONS.

  4. Santosh

    hi Sricharan,as always, a nice post. would you like to correct the process name to ‘Import User Login History’?

    1. Sricharan

      Hi Santosh,

      Thanks for catching it. At one place it was correct, and at another place there was a mistake..
      I have updated it now.

      Thanks,Sricharan

  5. Pavan

    Hi SriCharan,

    ASE_USER_LOGIN_INFO having one record for user so history is not storing. I have req like total user login history for 1 or 2 years how to can we achieve that

    Thanks,

  6. Sricharan

    Yes it stores one row for user. Looks like there isn’t a way to get the history login information.

  7. Michael

    Hi how can i see a report of all employees even those who haven’t logged on yet.

  8. Sricharan

    Hi Michael,
    You need to write a query to pull all users from PER_USERS table and add a not exists or not in clause to check for a row in ASE_USER_LOGIN_INFO table to get the report that you are looking for.
    Thanks,
    Sricharan

Leave a Reply