Sailpoint Identity IQ CertificationAutomaticClosing Rule

We have seen many time to send the Expiry Notification , when the Access Review is Expired , ie no action is taken CertificationAutomaticClosingRule can be used to send the notification , here is the sample code for using this Rule. 

  import sailpoint.object.Identity;
  import sailpoint.object.EmailOptions;
  import sailpoint.object.EmailTemplate;  
  import sailpoint.object.Certification;
  import org.apache.commons.logging.Log;
  import sailpoint.object.SignOffHistory;
  import org.apache.commons.logging.LogFactory;

  System.out.println("Entering Certification Auto closing rule.");

  List toAddresses = Arrays.asList("");

  List certifiers = certification.getCertifiers();
  Identity certifier = context.getObjectByName(Identity.class,certifiers.get(0).toString());
  System.out.println("certifier : " + certifier);

  if(null != certifier){
    toAddresses = Arrays.asList(certifier.getEmail());
    System.out.println("toAddresses : " + toAddresses);
  }

  String tplName = "Certification Expired Notification";
  EmailTemplate template = context.getObjectByName(EmailTemplate.class, tplName);
  if (null == template) {  
    System.out.println("Could not find email template [ " + tplName + "]");
    return;
  }

  Map args = new HashMap();  
  args.put("certification", certification);
  args.put("ownerName", certifier.getDisplayName());

  System.out.println("certification : " + certification);
  System.out.println("ownerName : " + certifier.getDisplayName());

  certification.addSignOffHistory((Identity)context.getObjectByName(Identity.class, "spadmin"));
  
  //Send Notifciation of Expiry
  
  System.out.println("Exiting Certification Auto closing rule");
  return ;  

Sailpoint Identity IQ CertificationSignOffApprover Rule

We have seen many time to send the Completion Notification , when the Access Review is completed CertificationSignOffApprover Rule can be used to send the notification , here is the sample code for using this Rule. 

  import java.util.Map;
  import java.util.List;
  import java.util.Arrays;
  import sailpoint.object.Identity;
  import sailpoint.api.SailPointContext;
  import sailpoint.object.Certification;
  import sailpoint.object.EmailOptions;
  import sailpoint.object.EmailTemplate; 
  import sailpoint.object.SignOffHistory; 
  import org.apache.commons.logging.Log;
  import org.apache.commons.logging.LogFactory;

  System.out.println("Entering Certification Sign Off Rule.");


  List signOffList=certification.getSignOffHistory();
  System.out.println("signOffList : " + signOffList);


  for(SignOffHistory signOffHistory:signOffList){
    if(signOffHistory.getSignerName().equalsIgnoreCase("spadmin")){      
      System.out.println("signOffHistory.getSignerName() : " + signOffHistory.getSignerName());
      return null;
    }
  }

  Date activationDate = certification.getActivated();
  System.out.println("activationDate : " + activationDate);

  if (activationDate != null) {

    List certifiers = certification.getCertifiers();
    Identity certifier = context.getObjectByName(Identity.class,certifiers.get(0).toString());
    System.out.println("certifier : " + certifier);

    List toAddresses = Arrays.asList("");
    if (null != certifier) {
      toAddresses = Arrays.asList(certifier.getEmail());
    }

    String tplName = "Certification Completion Notification";
    EmailTemplate template = context.getObjectByName(EmailTemplate.class, tplName);
    if (null == template) {
      System.out.println("Could not find email template [ " + tplName + "]");
      return null;
    }

    Map args = new HashMap();
    args.put("certification", certification);
    args.put("ownerName", certifier.getDisplayName());

    //Send Notifciation of completion 
	
  }
  System.out.println("Exiting Certification Sign Off Rule");

  return null;

Sailpoint IIQ - Item was revoked but has not been removed - Items database query

Many time in certification we see the message "Item was revoked but has not been removed.", this comes when the certifier takes the action on the item and either it get failed (in case of the connected system) or have generated the Workitem or ticket and the file is not aggregated back . below is the query which gives the information of the such items such as the identity , entitlement name , application to which this entitlement belongs , native identity of the user for the application and date on which the certifer took the action . 

This query can we further modified to get more information

SELECT 
SPT_IDENTITY.NAME,
SPT_IDENTITY_ENTITLEMENT.VALUE,
SPT_CERTIFICATION_ITEM.EXCEPTION_APPLICATION,
SPT_PROVISIONING_TRANSACTION.STATUS,
SPT_IDENTITY_ENTITLEMENT.NATIVE_IDENTITY,
SPT_CERTIFICATION_ACTION.STATUS,
( To_date('1970-01-01 00', 'yyyy-mm-dd hh24') + ( SPT_CERTIFICATION_ACTION.DECISION_DATE) / 1000 / 60 / 60 / 24 )                    AS "CERT_DECISION_DATE" 
FROM 
  SPT_IDENTITY_ENTITLEMENT,
  SPT_CERTIFICATION_ITEM,
  SPT_CERTIFICATION_ACTION,
  SPT_IDENTITY,
  SPT_APPLICATION,
  SPT_MANAGED_ATTRIBUTE,
  SPT_CERTIFICATION_ENTITY,
  SPT_PROVISIONING_TRANSACTION
WHERE 
CERTIFICATION_ITEM IS NOT NULL
AND SPT_CERTIFICATION_ITEM.ID=SPT_IDENTITY_ENTITLEMENT.CERTIFICATION_ITEM
AND SPT_CERTIFICATION_ACTION.ID=SPT_CERTIFICATION_ITEM.ACTION
AND SPT_CERTIFICATION_ACTION.STATUS='Remediated'
AND SPT_IDENTITY.ID=SPT_IDENTITY_ENTITLEMENT.IDENTITY_ID
AND SPT_CERTIFICATION_ITEM.EXCEPTION_APPLICATION=SPT_APPLICATION.NAME
AND SPT_MANAGED_ATTRIBUTE.APPLICATION=SPT_APPLICATION.ID
AND SPT_MANAGED_ATTRIBUTE.VALUE=SPT_IDENTITY_ENTITLEMENT.VALUE 
AND SPT_CERTIFICATION_ENTITY.TARGET_ID=SPT_IDENTITY.ID 
AND SPT_CERTIFICATION_ENTITY.CERTIFICATION_ID=SPT_PROVISIONING_TRANSACTION.CERTIFICATION_ID 
AND SPT_PROVISIONING_TRANSACTION.SOURCE='Certification' 
AND SPT_PROVISIONING_TRANSACTION.application_NAME=SPT_APPLICATION.NAME 
AND SPT_IDENTITY_ENTITLEMENT.NATIVE_IDENTITY=SPT_PROVISIONING_TRANSACTION.NATIVE_IDENTITY

SQL ACCESS REVIEW CERTIFICATION ITEMS DETAILS SAILPOINT IDENTITY IQ

This query is used to get the details about the Access Review , Certification which is generated and will give all the details 
for all the items whether action is taken or not.

SELECT SPT_CERTIFICATION.ID AS "CERT_ID",
   (
      TO_DATE('1970-01-01 00', 'YYYY-MM-DD HH24') + (SPT_CERTIFICATION.ACTIVATED) / 1000 / 60 / 60 / 24
   )
   AS "CREATE DATE",
   (
      TO_DATE('1970-01-01 00', 'YYYY-MM-DD HH24') + (SPT_CERTIFICATION.EXPIRATION) / 1000 / 60 / 60 / 24
   )
   AS "EXPIRATION DATE",
   (
      TO_DATE('1970-01-01 00', 'YYYY-MM-DD HH24') + (SPT_CERTIFICATION.SIGNED) / 1000 / 60 / 60 / 24
   )
   AS "SIGNED DATE",
  SPT_CERTIFICATION.SHORT_NAME AS "CERT_NAME",
  SPT_CERTIFICATION_ITEM.SUMMARY_STATUS AS "CERT_STATUS",
  SPT_CERTIFICATION.MANAGER AS "CERT_ASSIGNED_TO_ID",
  SPT_IDENTITY.EMAIL AS "CERT_ASSIGNED_TO_EMAIL",
  SPT_IDENTITY.DISPLAY_NAME AS "CERT_ASSIGNED_TO_NAME",
  user2.DISPLAY_NAME AS "MANAGER_DISPLAY_NAME",
  USER2.EMAIL AS "MANAGER_EMAIL",
  SPT_CERTIFICATION_ENTITY.TARGET_NAME AS "USER_ID",
  SPT_CERTIFICATION_ENTITY.TARGET_DISPLAY_NAME AS "USER_DISPLAY_NAME",
  SPT_CERTIFICATION_ENTITY.FIRSTNAME AS "USER_FIRST_NAME",
  SPT_CERTIFICATION_ENTITY.LASTNAME AS "USER_LAST_NAME",
  user1.EMAIL AS "USER_EMAIL",
  SPT_CERTIFICATION.TOTAL_ENTITIES,
  SPT_CERTIFICATION.EXCLUDED_ENTITIES,
  SPT_CERTIFICATION.COMPLETED_ENTITIES,
  SPT_CERTIFICATION.PERCENT_COMPLETE,
  SPT_CERTIFICATION.CERTIFIED_ENTITIES,
  SPT_CERTIFICATION.TOTAL_ITEMS,
  SPT_CERTIFICATION.EXCLUDED_ITEMS,
  SPT_CERTIFICATION.COMPLETED_ITEMS,
  SPT_CERTIFICATION.ITEM_PERCENT_COMPLETE,
  SPT_CERTIFICATION.CERTIFIED_ITEMS,
  SPT_CERTIFICATION.REMEDIATIONS_KICKED_OFF,
  SPT_CERTIFICATION.REMEDIATIONS_COMPLETED,
  (
      TO_DATE('1970-01-01 00', 'YYYY-MM-DD HH24') + (SPT_CERTIFICATION.MODIFIED) / 1000 / 60 / 60 / 24
   )
   AS "CERT_UPDATE_DATE",
  SPT_CERTIFICATION_ITEM.EXCEPTION_APPLICATION AS "ACCOUNT_APPLICATION_NAME",
  SPT_CERTIFICATION_ITEM.EXCEPTION_ATTRIBUTE_NAME AS "RECORD_TYPE",
  SPT_IDENTITY_ENTITLEMENT.NATIVE_IDENTITY AS "ACCOUNT_NAME",
  SPT_CERTIFICATION_ITEM.EXCEPTION_ATTRIBUTE_VALUE AS "ACCOUNT_ENTITLEMENT_NAME",
  NULL AS "CERT_DECISION",
  NULL AS "CERT_DECISION_COMMENTS",
  NULL AS "REMEDIATION_ACTION",
  NULL AS "REMEDIATION_DETAILS",
  NULL AS "CERT_DECISION_DATE",
  NULL AS TICKET,
  NULL AS TICKET_STATUS,
  SPT_CERTIFICATION_ITEM.ACTION AS "ACTION"
FROM SPT_CERTIFICATION_ENTITY,
  SPT_CERTIFICATION,
  SPT_CERTIFICATION_ITEM,
  SPT_IDENTITY,
  SPT_IDENTITY user1,
  SPT_IDENTITY user2,
  SPT_IDENTITY_ENTITLEMENT,
  SPT_APPLICATION
WHERE SPT_CERTIFICATION_ENTITY.CERTIFICATION_ID = SPT_CERTIFICATION.ID
  AND SPT_CERTIFICATION_ITEM.CERTIFICATION_ENTITY_ID = SPT_CERTIFICATION_ENTITY.ID
  AND SPT_IDENTITY.NAME = SPT_CERTIFICATION.MANAGER
  AND SPT_CERTIFICATION_ENTITY.TARGET_NAME = user1.NAME
  AND user1.MANAGER = user2.ID
  AND SPT_CERTIFICATION_ITEM.EXCEPTION_APPLICATION = SPT_APPLICATION.NAME
  AND SPT_CERTIFICATION_ITEM.EXCEPTION_ATTRIBUTE_VALUE = SPT_IDENTITY_ENTITLEMENT.VALUE
  AND user1.ID = SPT_IDENTITY_ENTITLEMENT.IDENTITY_ID
  AND SPT_APPLICATION.ID = SPT_IDENTITY_ENTITLEMENT.APPLICATION

SQL ACCESS REVIEW CERTIFICATION ACTION ITEMS DETAILS SAILPOINT IDENTITY IQ

This query is used to get the details about the Access Review , Certification which is generated and will give all the details 
for all the items for which the action is already taken.

SELECT SPT_CERTIFICATION.ID AS "CERT_ID",
   (
      TO_DATE('1970-01-01 00', 'YYYY-MM-DD HH24') + (SPT_CERTIFICATION.ACTIVATED) / 1000 / 60 / 60 / 24
   )
   AS "CREATE DATE",
   (
      TO_DATE('1970-01-01 00', 'YYYY-MM-DD HH24') + (SPT_CERTIFICATION.EXPIRATION) / 1000 / 60 / 60 / 24
   )
   AS "EXPIRATION DATE",
   (
      TO_DATE('1970-01-01 00', 'YYYY-MM-DD HH24') + (SPT_CERTIFICATION.SIGNED) / 1000 / 60 / 60 / 24
   )
   AS "SIGNED DATE",
  SPT_CERTIFICATION.SHORT_NAME AS "CERT_NAME",
  SPT_CERTIFICATION_ITEM.SUMMARY_STATUS AS "CERT_STATUS",
  SPT_CERTIFICATION.MANAGER AS "CERT_ASSIGNED_TO_ID",
  SPT_IDENTITY.EMAIL AS "CERT_ASSIGNED_TO_EMAIL",
  SPT_IDENTITY.DISPLAY_NAME AS "CERT_ASSIGNED_TO_NAME",
  
  user2.DISPLAY_NAME AS "MANAGER_DISPLAY_NAME",
  USER2.EMAIL AS "MANAGER_EMAIL",
  
  SPT_CERTIFICATION_ENTITY.TARGET_NAME AS "USER_ID",
  SPT_CERTIFICATION_ENTITY.TARGET_DISPLAY_NAME AS "USER_DISPLAY_NAME",
  SPT_CERTIFICATION_ENTITY.FIRSTNAME AS "USER_FIRST_NAME",
  SPT_CERTIFICATION_ENTITY.LASTNAME AS "USER_LAST_NAME",
  user1.EMAIL AS "USER_EMAIL",
  
  SPT_CERTIFICATION.TOTAL_ENTITIES,
  SPT_CERTIFICATION.EXCLUDED_ENTITIES,
  SPT_CERTIFICATION.COMPLETED_ENTITIES,
  SPT_CERTIFICATION.PERCENT_COMPLETE,
  SPT_CERTIFICATION.CERTIFIED_ENTITIES,
  SPT_CERTIFICATION.TOTAL_ITEMS,
  SPT_CERTIFICATION.EXCLUDED_ITEMS,
  SPT_CERTIFICATION.COMPLETED_ITEMS,
  SPT_CERTIFICATION.ITEM_PERCENT_COMPLETE,
  SPT_CERTIFICATION.CERTIFIED_ITEMS,
  SPT_CERTIFICATION.REMEDIATIONS_KICKED_OFF,
  SPT_CERTIFICATION.REMEDIATIONS_COMPLETED,
   (
      TO_DATE('1970-01-01 00', 'YYYY-MM-DD HH24') + (SPT_CERTIFICATION.MODIFIED) / 1000 / 60 / 60 / 24
   )
   AS "CERT_UPDATE_DATE",
   
  SPT_CERTIFICATION_ITEM.EXCEPTION_APPLICATION AS "ACCOUNT_APPLICATION_NAME",
  SPT_CERTIFICATION_ITEM.EXCEPTION_ATTRIBUTE_NAME AS "RECORD_TYPE",
  SPT_IDENTITY_ENTITLEMENT.NATIVE_IDENTITY AS "ACCOUNT_NAME",
  SPT_CERTIFICATION_ITEM.EXCEPTION_ATTRIBUTE_VALUE AS "ACCOUNT_ENTITLEMENT_NAME",
  SPT_CERTIFICATION_ACTION.STATUS AS "CERT_DECISION",
  SPT_CERTIFICATION_ACTION.DESCRIPTION AS "CERT_DECISION_COMMENTS",
  SPT_CERTIFICATION_ACTION.REMEDIATION_ACTION AS "REMEDIATION_ACTION",
  TO_CHAR(SPT_CERTIFICATION_ACTION.REMEDIATION_DETAILS) AS "REMEDIATION_DETAILS",
   (
      TO_DATE('1970-01-01 00', 'YYYY-MM-DD HH24') + (SPT_CERTIFICATION_ACTION.DECISION_DATE) / 1000 / 60 / 60 / 24
   )
   AS "CERT_DECISION_DATE",
  TO_CHAR(Regexp_substr(SPT_CERTIFICATION_ACTION.REMEDIATION_DETAILS, 'requestID="(.*?)"\sstatus', 1, 1, NULL, 1)) AS TICKET,
  TO_CHAR(Regexp_substr(SPT_CERTIFICATION_ACTION.REMEDIATION_DETAILS, 'status="(.*?)"\/', 1, 1, NULL, 1)) AS TICKET_STATUS,
  SPT_CERTIFICATION_ITEM.ACTION AS "ACTION"
FROM SPT_CERTIFICATION_ENTITY,
  SPT_CERTIFICATION,
  SPT_CERTIFICATION_ITEM,
  SPT_IDENTITY,
  SPT_CERTIFICATION_ACTION,
  SPT_IDENTITY user1,
  SPT_IDENTITY user2,
  SPT_IDENTITY_ENTITLEMENT,
  SPT_APPLICATION
WHERE SPT_CERTIFICATION_ENTITY.CERTIFICATION_ID = SPT_CERTIFICATION.ID
  AND SPT_CERTIFICATION_ITEM.CERTIFICATION_ENTITY_ID = SPT_CERTIFICATION_ENTITY.ID
  AND SPT_IDENTITY.NAME = SPT_CERTIFICATION.MANAGER
  AND SPT_CERTIFICATION_ACTION.ID = SPT_CERTIFICATION_ITEM.ACTION
  AND SPT_CERTIFICATION_ENTITY.TARGET_NAME = user1.NAME
  AND user1.MANAGER = user2.ID
  AND SPT_CERTIFICATION_ITEM.EXCEPTION_APPLICATION = SPT_APPLICATION.NAME
  AND SPT_CERTIFICATION_ITEM.EXCEPTION_ATTRIBUTE_VALUE = SPT_IDENTITY_ENTITLEMENT.VALUE
  AND user1.ID = SPT_IDENTITY_ENTITLEMENT.IDENTITY_ID
  AND SPT_APPLICATION.ID = SPT_IDENTITY_ENTITLEMENT.APPLICATION

SQL ACCESS REVIEW CERTIFICATION DETAILS SAILPOINT IDENTITY IQ

This Query can be used to get the Access review details which is assigned to the Reviewer. This will contain the basic information of the Access Review  and will not contain any information related to the identity details which is to be reviewed.

SELECT
   SPT_CERTIFICATION_DEFINITION.NAME AS "CERTIFICATION DEFINITION NAME",
   SHORT_NAME AS "CERTIFICATION NAME",
   (
      TO_DATE('1970-01-01 00', 'YYYY-MM-DD HH24') + (ACTIVATED) / 1000 / 60 / 60 / 24
   )
   AS "CREATE DATE",
   (
      TO_DATE('1970-01-01 00', 'YYYY-MM-DD HH24') + (EXPIRATION) / 1000 / 60 / 60 / 24
   )
   AS "EXPIRATION DATE",
   (
      TO_DATE('1970-01-01 00', 'YYYY-MM-DD HH24') + (SIGNED) / 1000 / 60 / 60 / 24
   )
   AS "SIGNED DATE",
   SPT_CERTIFICATION.MANAGER AS "ASSIGNED TO USER",
   SPT_IDENTITY.EMAIL AS "ASSIGNED USER EMAIL",
   SPT_CERTIFICATION.TOTAL_ENTITIES AS "TOTAL IDENTITY INCLUDED",
   SPT_CERTIFICATION.EXCLUDED_ENTITIES AS "TOTAL IDENTITY EXCLUDED",
   SPT_CERTIFICATION.COMPLETED_ENTITIES AS "COUNT ACTION TAKEN ON IDENTITY",
   SPT_CERTIFICATION.PERCENT_COMPLETE AS "PERCENTAGE ACTION TAKEN ON IDENTITY",
   SPT_CERTIFICATION.CERTIFIED_ENTITIES,
   SPT_CERTIFICATION.TOTAL_ITEMS AS "TOTAL ITEMS ASSIGNED",
   SPT_CERTIFICATION.EXCLUDED_ITEMS AS "TOTAL ITEMS EXCLUDED",
   SPT_CERTIFICATION.COMPLETED_ITEMS AS "COUNT ACTION TAKEN ON ITEM",
   SPT_CERTIFICATION.ITEM_PERCENT_COMPLETE AS "PERCENTAGE ACTION TAKEN ON ITEM",
   SPT_CERTIFICATION.CERTIFIED_ITEMS,
   SPT_CERTIFICATION.REMEDIATIONS_KICKED_OFF,
   SPT_CERTIFICATION.REMEDIATIONS_COMPLETED 
FROM
   SPT_CERTIFICATION,
   SPT_IDENTITY,
   SPT_CERTIFICATION_GROUPS,
   SPT_CERTIFICATION_GROUP,
   SPT_CERTIFICATION_DEFINITION 
WHERE
   SPT_IDENTITY.NAME = SPT_CERTIFICATION.MANAGER
   AND SPT_CERTIFICATION_DEFINITION.ID = SPT_CERTIFICATION_GROUP.CERTIFICATION_DEFINITION
   AND SPT_CERTIFICATION_GROUP.ID = SPT_CERTIFICATION_GROUPS.GROUP_ID 
   AND SPT_CERTIFICATION_GROUPS.CERTIFICATION_ID = SPT_CERTIFICATION.ID 
   AND SPT_CERTIFICATION_DEFINITION.ID = SPT_CERTIFICATION.CERTIFICATION_DEFINITION_ID 
   AND SPT_CERTIFICATION_GROUP.NAME = '<CERTIFICATION NAME>'


Same Query can be modified and can be used to get the data with more details like extended attribute from Identity and other deatils.

SPT_CERTIFICATION_DEFINITION - Certification Definition 
SPT_CERTIFICATION_GROUP - Certification Details
SPT_CERTIFICATION_GROUPS - Link Certification and Access Review 
SPT_CERTIFICATION - Access Review details doesn't include the Actioned Identity details