8 Replies Latest reply: Feb 8, 2013 9:50 AM by Kevin Pinsky RSS

    Audit query for user history in OIM 11g

    940940
      I would like to know if we can come up with a query to get the details of when and by whom a user has been modified over a certain time frame.


      Thank you in advance.

      Edited by: 937937 on Jan 30, 2013 1:00 PM
        • 1. Re: Audit query for user history in OIM 11g
          BikashBagaria
          {message:id=9912469}
          {message:id=9948327}

          and more if you search for "UPA" in the forum.

          -Bikash
          • 2. Re: Audit query for user history in OIM 11g
            940940
            Thank you for your response

            I enabled the audit level to Process task, and ran the generatesnapshot.sh. I ran the queries referred in the link still could not find any details. Am I missing something.
            • 3. Re: Audit query for user history in OIM 11g
              Kevin Pinsky
              The default audit level will provide you this. Are you running the "Issue Audit Message" scheduled task though that processes the events? Run "select count(*) from aud_jms;" to see how many audit records are waiting to be processed.

              -Kevin
              • 4. Re: Audit query for user history in OIM 11g
                940940
                No I am not running the schedule task.

                And the count is 0.
                • 5. Re: Audit query for user history in OIM 11g
                  940940
                  Actually the task is set to run every 5 mins
                  • 6. Re: Audit query for user history in OIM 11g
                    Kevin Pinsky
                    You should be able to use the OOTB reports for the User Profile History. Here is the query that is used in the report:

                    SELECT DISTINCT UPA_USR.USR_LOGIN AS USERID,
                    upa_usr.usr_first_name as FirstName,
                    upa_usr.USR_LAST_NAME as CurrentLastName,
                    upa_usr.ACT_NAME as Organization,
                    upa_usr.USR_MGR_FIRST_NAME as ManagerFirstName,
                    upa_usr.USR_MGR_LAST_NAME as ManagerLastName,
                    upa_usr.USR_MGR_LOGIN as ManagerUID,
                    upa_usr.USR_STATUS as Status,
                    upa_usr.USR_EMP_TYPE as EmployeeType,
                    upa_usr.create_date as IdentityCreationDate,
                    UPA_USR.UPA_USR_EFF_FROM_DATE AS EFFECTIVEFROMDATE,
                    UPA_USR.UPA_USR_EFF_FROM_DATE AS EFFECTIVEFROMTIME,
                    f1.field_name as UserProfileParameterName,
                    f1.field_new_value as UserProfileParameterValue
                    FROM UPA_USR LEFT OUTER JOIN UPA_GRP_MEMBERSHIP
                    ON upa_usr.upa_usr_key = upa_grp_membership.upa_usr_key,
                    upa_fields f1
                    WHERE UPA_USR.UPA_USR_KEY = F1.UPA_USR_KEY
                    And f1.field_name not like '%Key%'
                    And (nvl(:p_varchar_FN, ' ') = ' ' or upper(upa_usr.usr_first_name) like upper(:p_varchar_FN))
                    And (nvl(:p_varchar_LN, ' ') = ' ' or upper(upa_usr.usr_last_name) like upper(:p_varchar_LN))
                    And (nvl(:p_varchar_UID, ' ') = ' ' or upper(upa_usr.usr_login) like upper(:p_varchar_UID))
                    And (nvl(:p_varchar_Org, ' ') = ' ' or upper(upa_usr.act_name) like upper(:p_varchar_Org))
                    And (nvl(:p_varchar_GrpN, ' ') = ' ' or upper(upa_grp_membership.ugp_name) like upper(:p_varchar_GrpN))
                    And (nvl(:p_varchar_MgrID, ' ') = ' ' or upper(upa_usr.USR_MGR_LOGIN) like upper(:p_varchar_MgrID))
                    And (nvl(:p_varchar_Status, ' ') = ' ' or upper(upa_usr.usr_status) like upper(:p_varchar_Status))
                    AND (NVL(:P_VARCHAR_EMPTYPE, ' ') = ' ' OR UPPER(UPA_USR.USR_EMP_TYPE) LIKE UPPER(:P_VARCHAR_EMPTYPE))
                    AND upa_usr.upa_usr_eff_from_date between :p_date_UDateFrm And :p_date_UDateTo
                    AND NVL(upa_grp_membership.upa_grp_eff_from_date,:p_date_GDateTo) <= :p_date_GDateTo
                    AND NVL(UPA_GRP_MEMBERSHIP.UPA_GRP_EFF_TO_DATE,:p_date_GDateFrm) >= :p_date_GDateFrm
                    Order by upa_usr.usr_login, EffectiveFromDate, EffectiveFromTime

                    -Kevin
                    • 7. Re: Audit query for user history in OIM 11g
                      940940
                      I was able to use this, but the thing is I want to get the details as below

                      Over a time span when and who modified a particular user and what attribute has been modified.
                      • 8. Re: Audit query for user history in OIM 11g
                        Kevin Pinsky
                        Each change is identified by the UPA_USR_KEY. If 5 fields get updated at once, all of these are groups by the UPA_USR_KEY field. The person whom made the change is Users.Updated By Login. However, if the same user made the change as the last time, then obviously there is no change on this field. So you would need to go back to previous events to see who is in this field in the most recent until you find an entry. This means the same user made the change each time.

                        -Kevin