Reporting and Analytics for ERP

Get Involved. Join the Conversation.

Topic

    Wendy Ware
    SQL to Report on List of User Logins Per DayAnswered
    Topic posted January 28, 2019 by Wendy WareBronze Crown: 15,000+ Points, tagged BI Publisher, Fusion Financial reporting, OBI Answers, OBIEE Answers, OTBI, Reports 
    262 Views, 13 Comments
    Title:
    SQL to Report on List of User Logins Per Day
    Summary:
    seeking a listing of unique user logins
    Content:

    Can anyone provide SQL, or perhaps just table name, to get a list of unique user logins into Fusion?  Thanks, Wendy

    Best Comment

    Viswanadha Reddy

    Hi Wendy,

     

    Please find below query to get the user login details. user login information stored in table ASE_USER_LOGIN_INFO, to populate the data in this table we need to run The job "Import User Login History".

    select b.username,c.full_name,a.LAST_LOGIN_DATE
    from ASE_USER_LOGIN_INFO a,
    per_users b,
    per_person_names_f c
     where a.user_guid=b.user_guid
    and b.person_id=c.person_id(+)
    and sysdate between c.effective_start_date(+) and c.effective_end_date(+)
    and c.name_type(+)='GLOBAL'​

     

    Thanks

    Viswa

    Comment

     

    • Gopinath Kartheesan

      Hi Wendy, did you try the report that comes out under "My Services".  It does show the user login trend as a weekly report and you can report in PDF format

      • Wendy Ware

        Hi Gopinath, thank you for your prompt reply.  I would be interested in this report if it gives detailed listing of specific user logins and not just login counts.  Can you please direct me further on how to locate it from the My Services dashboard I assume you are talking about?  Thanks, Wendy

    • Nishanth B Jain

      Hi Wendy,

      can you check this subject area of OTBI report

      "User System Usage"

      Thanks,

      Nishanth

      • Wendy Ware

        Hi Nishanth, this looks very promising but it appears that none of the users have a value in "Last Activity Date."  Can you provide more guidance?  Admittedly, I'm not very experienced in using SAs but this seems like a nice straightforward one that would get me what I need. 

        Thanks, Wendy

        • Nishanth B Jain

          Hi Wendy,

          from NOTE

          The Last Activity Date was null because the profile option "FND_TRACK_USER_ACTIVITY" was disabled.

          Steps to enable:

          ->Sign in to the application as a system administrator.
          ->Go to the Setup and Maintenance work area and select the Sales offering.
          ->Search for the Manage Administrator Profile Values task and go to the task page.
          ->On the Manage Administrator Profile Values page, search for the profile option code FND_TRACK_USER_ACTIVITY.
          ->Set the site level profile value to Enabled.
          ->Click Save and Close.

          After enabling it, "# of active days" fact should be added in the report to get the Last Activity Date.

          Note: The report will show the Last Activity Date of the users who logged into the application after the profile option was enabled.


          For further information, please refer: Creating and Administering Analytics for Sales> 3 Setup and Configuration > Managing User Adoption Analytics > Enabling User Adoption

          About User System Usage subject area: https://docs.oracle.com/en/cloud/saas/sales/18c/faosb/subject-areas.html#User_System_Usage_SA_65

          Thanks,

          Nishanth

          • Wendy Ware

            Hi Nishanth, thank you for your detailed reply.  I will enable that profile option to facilitate future reporting and read the documentation to which you refer.  Thanks again!  Wendy

    • Mark daynes

      Does (using BIP) "select * from ase_user_login_info" give you what you need?

       

    • Viswanadha Reddy

      Hi Wendy,

       

      Please find below query to get the user login details. user login information stored in table ASE_USER_LOGIN_INFO, to populate the data in this table we need to run The job "Import User Login History".

      select b.username,c.full_name,a.LAST_LOGIN_DATE
      from ASE_USER_LOGIN_INFO a,
      per_users b,
      per_person_names_f c
       where a.user_guid=b.user_guid
      and b.person_id=c.person_id(+)
      and sysdate between c.effective_start_date(+) and c.effective_end_date(+)
      and c.name_type(+)='GLOBAL'​

       

      Thanks

      Viswa

    • Wendy Ware

      Hi Mark and Viswa,

      That table only holds 166 records from a few days in 2015 for us.  Any idea why?  (Data Source "ApplicationDB_FSCM")  Thanks, Wendy

       

      select min(LAST_LOGIN_DATE), max(LAST_LOGIN_DATE), count(*) from ase_user_login_info

      MIN_LAST_LOGIN_DATE_

      MAX_LAST_LOGIN_DATE_

      COUNT___

      2015-06-09T13:24:17.000+00:00

      2015-06-16T21:17:45.000+00:00

      166

    • Viswanadha Reddy

      Hi Wendy,

      Please submit the schedule process "Import User Login History" and then execute the above query.

       

      Thanks

      Viswa

      • Wendy Ware

        Hi Viswa, ah, yes.  I see now that you mentioned this process in your initial reply.  That did the trick!  Thanks, Wendy

    • Viswanadha Reddy

      Hi Wendy,

      Schedule process "Import User Login History" populates the ASE_USER_LOGIN_INFO table with data coming from FND_SESSION.
      The data older than 7 days is removed from the table FND_SESSION so if the  schedule process "Import User Login History" is not scheduled
      with the right recurrence, it will cause data to be lost.

       

      Thanks

      Viswa

      • Wendy Ware

        Hi Viswa, this is good to know.  Luckily, at the current time, I am only looking for very recent info but I will definitely schedule the process to capture the data in a timely manner going forward.  Thanks again!  Wendy