OIM Certification Entity Details using SQL

Using SQL query you can find the details of certification entity by providing certification id as input. There are 3 different queries for Application Instance, Entitlement and Role.

OIM does have certification APIs for getting the entity details from certification id like:

CertificationService -> getIDCEntitlementsByBatch
CertificationService -> getIDCRolesByBatch
CertificationService -> getIDCApplicationInstancesByBatch

But many occasion, these APIs create problem during execution.

Below are the sample queries, which can easily replace the above APIs:

----------------------------------- Entitlement Certification --------------------------------------
SELECT
cert_certs.id AS certification_id,
cert_certs.cert_name AS certificationname,
certs_ent_defn.attr_value AS attribute_value,
certd_ent_defn.glossary_def AS name,
          certd_ent_defn.entity_id AS entitlement_entity_id
FROM
certd_ent_defn certd_ent_defn
INNER JOIN cert_certs cert_certs
ON certd_ent_defn.cert_id = cert_certs.id
INNER JOIN certs_ent_defn certs_ent_defn
ON certd_ent_defn.attr_val_id = certs_ent_defn.id
WHERE
         cert_certs.id = [CERT_ID]

------------------------------------ Application Instance Certification ------------------------
SELECT
certd_app_inst.cert_id AS certification_id,
certs_app_inst.iam_endpoint_id AS applicationid,
certs_app_inst.endpoint_name AS application_name
FROM
certs_app_inst, certd_app_inst,
cert_certs 
WHERE
cert_certs.id = certd_app_inst.cert_id 
AND certs_app_inst.id = certd_app_inst.endpoint_id 
        AND cert_certs.id=[CERT_ID]

------------------------------------------ Role Certification --------------------------------------
SELECT
cert_certs.id AS certification_id,
certs_role.rolename,
certs_role.entity_display_name
FROM
certs_role, certd_role,
cert_certs 
WHERE
cert_certs.id = certd_role.cert_id 
AND certd_role.role_id = certs_role.id
AND  cert_certs.id = [CERT_ID]

Close OIM 11gR2 PS3 Identity Certification in bulk using API

closing identity certification from OIM identity console is possible by an administrator, but feasible for large number. Here is a code example of doing the same for bulk.

You can get the list of certification which are not completed yet from the OIM database, using below query

select CERT_ID, USER_ID from CERTD_USER where CERT_ID in (select ID from CERT_SERTS where CERT_STATE in ('17','2','1','11','12',13','14','15','16'))

the CERT_SATE numbers are defined as below inside OIM

STATE_EXPIRED : 4
STATE_COMPLETE : 3
STATE_FINAL_REVIEW : 17
STATE_IN_PROGRESS : 2
STATE_NEW : 1
STATE_PHASE_1 : 11
STATE_PHASE_1_D : 12
STATE_PHASE_1_V : 13
STATE_PHASE_2 : 14
STATE_PHASE_2_D : 15
STATE_PHASE_2_V : 16

You can use the below code snippets to close all the open certification tasks.

/**
  *
  * @param cert_id: certification id. available in CERTD_USER table
  * @param userEntityId: User id for the certification which are pending. available in CERTD_USER.
  * @throws Exception
  */
 public void closeCertification(long cert_id,String userEntityId) throws Exception{
  
   CertificationService certificationService = (CertificationService)oimClient.getService(CertificationService.class);

    char[] oimAdminPassword = "Welcome1".toCharArray(); //Xelsysadm password
    
   List<Long> userEntityIds = new ArrayList<Long>();
   userEntityIds.add(Long.valueOf(userEntityId));
  
   //get the certification instance from cert id
   CertificationInstance certInstance = certificationService.loadCertification(cert_id,null);
   System.out.println("Status : "+certInstance.getStatus());
   System.out.println("STATE_EXPIRED : "+CertificationConstants.STATE_EXPIRED);
   System.out.println("STATE_COMPLETE : "+CertificationConstants.STATE_COMPLETE);
  
   //get the different status level
   int status = certInstance.getStatus();
   int complete = CertificationConstants.STATE_COMPLETE;
   int expired = CertificationConstants.STATE_EXPIRED;
  
   //Checking the current status of the certification instance.
   if (status==complete) {
   
    System.out.println("certification already completed : "+cert_id);
   }else if(status==expired){
    System.out.println("certification already completed : "+cert_id);
   }  
   else{
   
    //Certifying user of the cert instance with cancel comment
    certificationService.certifyUsers(cert_id, null, userEntityIds, CertificationConstants.STATUS_ABSTAIN, "Canceled due migration");
    certificationService.certifyRemainingUserContent(cert_id, null, userEntityIds, "Canceled due migration");
   
    if ( certificationService.canBeCompleted(cert_id, null) ) {
    
     //completing the certification.
     certificationService.completeCertification(cert_id, null, oimAdminPassword);
     System.out.println("Certification Completed successfully : "+cert_id);
    }else{
     System.out.println("Certification not Completed yet : "+cert_id+" , "+certInstance.getPercentComplete()+"% completed");
    }
   }