Reporting and Analytics for ERP

Get Involved. Join the Conversation.

Topic

    Kimberly N.
    A report that tracks user access activity history
    Topic posted October 2, 2019 by Kimberly N.Red Ribbon: 250+ Points, tagged BI Publisher, Fusion Financial reporting, OBI Answers, OTBI, Reports 
    148 Views, 13 Comments
    Title:
    A report that tracks user access activity history
    Summary:
    A report for security that tracks user login history what data or module was touched
    Content:

    Does anyone know how/where I can get this information together?  This is for security audit and we need to be able to see the /what/when data attributes.  Who login and what data or module this user accessed with history logging.  I'm only able pull the User and Roles data but not at the what data accessed during the logged in session. 

    Version:
    18C

    Comment

     

    • Carrie Gose

      I opened an SR last week asking a similar question.  I wanted to know when attachments were opened on an invoice or requisition.  They referenced some auditing documents in the reply, i will paste their replies below in case they help.


      Per related service requests:
      A) In order to enable audit for journal, please follow below mentioned action plan:-

      1. Go to Setup and maintenance -> Manage Audit Policies task.
      2. Select 'Oracle Fusion Applications' --> Click on Configure Business Object Attributes.
      3. Select product 'General Ledger'.
      4. Go to 'Journals' object and enable 'Journal Batch', 'Journal Header', 'Journal Line'.
      5. Click on save.

      Create operation on journals is intentionally not audited, only updates are audited.
      Supporting create would create high volume of audit trail and cause performance issues.

      B) Per another related request:This was implemented as of Rel13 18B as enhancement request Bug 22682571 : AUDIT TRAIL ON GENERAL LEDGER.

      Setup and Maintenance > Manage Audit Policies
      Configure Business Objects Attributes
      Select Application, General Ledger

      To be able to access Audit policies, make sure FND_MANAGE_AUDIT_POLICIES_PRIV is added to the roles being used.

       

      Auditing for BI Publisher and OTBI:

      Auditing came with 19A...and shows only BIP data model, reports, etc created, modified, edited....and information on running statistics.
      OTBI auditing will be in 19D ( https://www.oracle.com/webfolder/technetwork/tutorials/tutorial/cloud/r13/wn/common/releases/19A/19A-common-wn.htm#F6392 ) Audit and Performance Monitoring Now Available and Turned On by Default.
      There are some seeded reports that are part of the Audit.zdrz (you will have to unzip in xmlpserver not analytics)

       

       

    • Sudhakara Rao Kovuru

      You can create your own custom BIP report to track changes

      • Kimberly N.

        Do you know the tables that have update history?  I'm happy to build a custom report but I can't find the source/s

    • Dave Sherrard

      Did you get any additional help with this, I need to create a similar report for my auditors as the user/user roles are not enough.

      Dave

      • Kimberly N.

        Hi Dave,

        No, I have not.  I'm still trying to figure this out for the same purpose.

        • Dave Sherrard

          I have created a user roles report using the query below, trying to see how much more i can get from it.

           

          select  peru.USERNAME as "USER_NAME",
                  pern.FULL_NAME as "FULL_NAME",
                  pere.EMAIL_ADDRESS as "EMAIL_ADDRESS",
                  rv.ROLE_COMMON_NAME as "ROLE_COMMON_NAME",
                  rv.ROLE_NAME as "ROLE_NAME",
                  rv.DESCRIPTION as "ROLE_DESCRIPTION",
           peru.active_flag,
                  peru.creation_date,
          peru.last_update_date,
          peru.last_updated_by

                  
          from    FUSION.PER_USERS peru,
                  FUSION.PER_PERSON_NAMES_F_V pern,
                  FUSION.PER_EMAIL_ADDRESSES_V pere,
                  FUSION.PER_USER_ROLES ur,
                  FUSION.PER_ROLES_DN_VL rv
          where   1=1
            AND   pern.person_id = peru.PERSON_ID      
            AND   pere.person_id = peru.person_id
            AND   pern.EFFECTIVE_END_DATE > SYSDATE + 1
            AND   ur.user_id = peru.user_id
            AND   rv.role_id = ur.ROLE_ID
              AND   UPPER(rv.ROLE_COMMON_NAME) like NVL(UPPER(:P_ROLE_NAME), '%')
            AND   UPPER(peru.username) like NVL(UPPER(:P_USERNAME), '%')
            AND   UPPER(pern.FULL_NAME) like NVL(UPPER(:P_FULLNAME), '%')

          Order by pern.FULL_NAME asc

          • Sudhakara Rao Kovuru

            Use below query... hope this should work for you guys... please let me know.

             

            SELECT DISTINCT Lower(pu.username)                 username, 
                            ppn.first_name, 
                            ppn.last_name, 
                            To_char(pu.start_date, 'mm/dd/rrrr')                 acct_start_date, 
                            To_char(pu.end_date, 'mm/dd/rrrr')                 acct_end_date, 
                            To_char(pu.creation_date, 'mm/dd/rrrr')                 acct_creation_date, 
                            To_char(pu.last_update_date, 'mm/dd/rrrr')                 acct_last_update_date, 
                            prdv.role_name, 
                            prdv.role_common_name                                   code           , 
                            To_char(pur.start_date, 'mm/dd/rrrr')                 role_start_date, 
                            To_char(pur.end_date, 'mm/dd/rrrr')                 role_end_date, 
                            To_char(pur.creation_date, 'mm/dd/rrrr')                 role_creation_date, 
                            pur.created_by                 role_created_by, 
                            To_char(pur.last_update_date, 'mm/dd/rrrr')                 role_last_update_date, 
                            pur.last_updated_by                 role_last_updated_by, 
                            (SELECT ppnf.full_name 
                             FROM   per_all_assignments_m paam, 
                                    per_periods_of_service ppos, 
                                    per_assignment_supervisors_f pasf, 
                                    per_person_names_f ppnf 
                             WHERE  pu.person_id = paam.person_id 
                                    AND ppos.person_id = paam.person_id 
                                    AND ppos.period_of_service_id = 
                                        paam.period_of_service_id 
                                    AND paam.primary_assignment_flag = 'Y' 
                                    AND paam.assignment_type IN ( 'E', 'C' ) 
                                    AND paam.assignment_status_type ! = 'INACTIVE' 
                                    AND Trunc(SYSDATE) BETWEEN paam.effective_start_date AND 
                                                               paam.effective_end_date 
                                    AND pu.person_id = pasf.person_id 
                                    AND pasf.manager_type = 'LINE_MANAGER' 
                                    AND ppnf.person_id = pasf.manager_id 
                                    AND ppnf.name_type = 'GLOBAL' 
                                    AND Trunc(SYSDATE) BETWEEN pasf.effective_start_date AND 
                                                               pasf.effective_end_date)              Manager 
            FROM   per_users pu, 
                   per_user_roles pur, 
                   per_person_names_f ppn, 
                   per_roles_dn_vl prdv 
            WHERE  pu.user_id = pur.user_id 
                   AND pu.person_id = ppn.person_id 
                   AND pur.role_id = prdv.role_id 
                   AND ( pu.end_date IS NULL      OR pu.end_date > SYSDATE ) 
                   AND pu.suspended = 'N' 
                   AND pur.active_flag = 'Y' 
                   AND ppn.name_type = 'GLOBAL' 
                   AND Trunc(SYSDATE) BETWEEN ppn.effective_start_date AND      ppn.effective_end_date 
                   AND Trunc(pur.start_date) BETWEEN Nvl(:P_Role_From, Trunc(pur.start_date)) AND   Nvl(:P_Role_To, 
                           Trunc(pur.start_date)) 
                   AND Trunc(pu.start_date) BETWEEN Nvl(:P_Acc_From, Trunc(pu.start_date)) 
                                                    AND Nvl( 
                                                    :P_Acc_To, Trunc(pu.start_date)) 
                   AND ( :P_Username IS NULL 
                          OR ( Upper(pu.username) LIKE '%' 
                                                       || Upper(:P_Username) 
                                                       ||'%' ) ) 
            ORDER  BY 1, 
                      2, 
                      prdv.role_name 

            • Dave Sherrard

              Thank you however the information we are looking for is the last login date of a user, not the time the account was updated.

              All this information I could get from fnd_users in the Oracle EBS 12.1.3 but struggling to get this information since we moved to Fusion.

              Dave

              • Sudhakara Rao Kovuru

                Ok... In fusion I could see only below tables has the LAST_LOGIN_DATE column so please try this table ASE_USER_LOGIN_INFO with above query and also check below tables which may help you...

                ARB_REG_USER_PROFILES
                ARB_REG_USER_PROFILES_SEC
                ASE_USER_LOGIN_INFO
                ASE_USER_LOGIN_INFO_SEC
                ARB_REG_USER_PROFILES
                ASE_USER_LOGIN_INFO

                • Dave Sherrard

                  Will try and let you know

                • Kimberly N.

                  Thank you for the tips.  The ASE_USER_LOGIN_INFO table has user LAST_LOGIN_DATE which is useful and I join this with user Roles related tables.  Now, if I could find out what modules user accessed during the login session, that would suffice audit request.  Do you know of any tables that would store what modules was accessed by user during a logged in session? 

                   I found PER_CLICK_HISTORY but there is only one record in that table.  Any tips on how to enable/collect this data?

                  Thanks,
                  Kimberly

                  • Dave Sherrard

                    My auditors don't require that additional information but if you figure it out let me know.

                    I am ok with what I have for now.

                    Thank you