7 Replies Latest reply: Jul 17, 2012 6:17 PM by BikashBagaria RSS

    Audit when change password fails

    832047
      I need to audit user details whenever a password change attempt fails. I do not find this information in the OOTB auditing tables. Any pointers would be helpful. Thanks.
        • 1. Re: Audit when change password fails
          BikashBagaria
          Use the following query to get the last change set for a particular user (user key 21 in my example):
          SELECT upa.usr_key, upa_usr.usr_login, 
          extractvalue(upa_xml.snapshot,'/UserProfileSnapshot/UserInfo/Attribute[@name=''Users.Updated By Login'']') AS CHANGED_BY_USER, 
          extractvalue(upa_xml.snapshot,'/UserProfileSnapshot/UserInfo/Attribute[@name=''Users.Update Date'']')      AS CHANGED_TIME, 
          field_name, 
          field_old_value, 
          field_new_value 
          FROM upa, upa_usr, upa_fields, (SELECT upa_key, xmltype(SNAPSHOT) snapshot FROM upa) upa_xml 
          WHERE upa.eff_to_date IS NULL 
          AND upa.upa_key = upa_xml.upa_key 
          AND upa.usr_key = upa_usr.usr_key 
          AND upa_usr.upa_usr_eff_to_date IS NULL 
          AND upa_usr.upa_usr_key = upa_fields.upa_usr_key 
          AND upa.usr_key = 21; 
          The above gives me the password reset attempt counter and that I believe should work for you.

          HTH,
          BB
          • 2. Re: Audit when change password fails
            832047
            The password reset attempts counter is set as 0 even though the password change failed. Am I missing any setting in OIM?
            • 3. Re: Audit when change password fails
              BikashBagaria
              user13779546 wrote:
              The password reset attempts counter is set as 0 even though the password change failed. Am I missing any setting in OIM?
              What version of OIM are you on? I tested it by logging in as the end user and then trying to change my password to some which would fail the password policy. This gives me password reset attempts counter correctly via my query.
              If you are on OIM9.x versions have you installed audit and compliance module? Also where are you seeing that the counter is 0 in the USR table?

              -BB
              • 4. Re: Audit when change password fails
                832047
                The OIM Version is 9.1.0.1. Auditing is enabled and I have re-run the Issue Audit Messages Scheduler after password change failed. I checked the snapshot column in the UPA tble.
                • 5. Re: Audit when change password fails
                  BikashBagaria
                  I tried on OIM11.1.1.5 and it's working so I believe there might be some issue with 9x version, although auditing looks same to me in 9x and 11g. I don't have an image of 9x running for me now. Hopefully someone else would try it out and reply. BTW, is the query working for you? Are you able to get updates to user profile via my query?

                  -BB
                  • 6. Re: Audit when change password fails
                    832047
                    Yep, I am able to see the user updates.
                    • 7. Re: Audit when change password fails
                      BikashBagaria
                      ....

                      -Bikash

                      Edited by: Bikash Bagaria on Jul 18, 2012 4:47 AM