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

Sailpoint Identity IQ Reading Data from Custom Table

Reading Data from the Custom Table created in the Sailpoint identity IQ Database ,

Once the table is created within the IdentityIQ database, it can be queried like any JDBC connection.  However, the advantage of using a table within the IdentityIQ database is that the connection to the database can be obtained from the IIQ context rather than having to store the URL, username, and password within the code itself.

Below sample code can be used in any places , such as showing the data in the Form (from custom table) , Rules or Workflow

   List itemList = new ArrayList();
            
   try{

   String  columnSearch = "ACCOUNT_ID";
   String  tableName = "VIS_SERVICE_ACCOUNT";
   String  conditionColumnName = "ACCOUNT_ID";
   String  sqlStatement ="select "+columnSearch+" from "+tableName;
   Iterator it = context.search("sql:"+sqlStatement, null,null);

    while ((null != it) && it.hasNext()) {
      String i= it.next();
      if(!itemList.contains(i)) {
       itemList.add(i);
      }
    }
   Util.flushIterator(it);
   }
   catch(GeneralException ex){
    logger.error("Error in form : " +ex.getMessage());
   }
   return itemList;

Database - JDBC Application Configuration Using OOTB Connector - Provisioning

1. Perform the steps to configure the Database/JDBC connector as mentioned in the link

2. Navigate to → Application → Rules → Provisioning Rule → Global Provisioning Rule, here we are writing the Rule to Perform the Create and Delete operation , Sample code is attached below

3. Below is the Sample code

import sailpoint.object.ProvisioningResult; import sailpoint.object.ProvisioningPlan; import sailpoint.object.ProvisioningPlan.AccountRequest; import sailpoint.object.ProvisioningPlan.AttributeRequest; import sailpoint.object.Filter; import sailpoint.object.ManagedAttribute; import sailpoint.object.Link; import sailpoint.tools.Util; import sailpoint.api.IdentityService; import java.util.List; import java.util.HashMap; import java.sql.PreparedStatement; ProvisioningResult result = new ProvisioningResult(); if (plan != null){ List accountRequests = plan.getAccountRequests(); if (( accountRequests != null ) && ( accountRequests.size() > 0 )){ for(AccountRequest accRequest: accountRequests){ try { System.out.println("Opeartion Requested: "+accRequest.getOperation()); if(AccountRequest.Operation.Create.equals(accRequest.getOperation())){ accRequest.setNativeIdentity(plan.getNativeIdentity()); PreparedStatement statement = connection.prepareStatement("INSERT INTO MARS(LANID) values (?)"); statement.setString(1, plan.getNativeIdentity()); statement.executeUpdate(); result.setStatus(ProvisioningResult.STATUS_COMMITTED); } if(AccountRequest.Operation.Delete.equals(accRequest.getOperation())){ accRequest.setNativeIdentity(plan.getNativeIdentity()); PreparedStatement statement = connection.prepareStatement("DELETE FROM MARS WHERE LANID =(?)"); statement.setString(1, plan.getNativeIdentity()); statement.executeUpdate(); result.setStatus(ProvisioningResult.STATUS_COMMITTED); } }catch (SQLException e) { result.setStatus(ProvisioningResult.STATUS_FAILED); result.addError(e); } } } } System.out.println("returning the result: "+result.toXml()); return result;

4. Click on Application→ Provisioning Policy → Create a new policy and attach the same to the Create operation

5. Checking the Provisioning for the application , Navigate to → Manage Request → Account Request → Select the Identity for which Account need to be request

(Make sure the setting is done to make this application as requestable)

6. Checking  the Database to validate if the Provisioning created the account for the Custom Application ,  

Here we can see the Entry for the User is added to the Configured table

7. We won’t we able to see the Link until we run the Account Aggregation Task which we created in earlier post

8. Click on Save and Execute and Check the Result from the task Result Tab.

9. Navigate to Application → Application Definition → Accounts and see all the Accounts which are pulled from the DB.

10. Checking the linked Account to the Identity,

11. Navigate to Identity → Identity Warehouse → Select the Identity

Click on the Application Accounts to see the the Accounts if the Link Exists.

12 . Few Important point Noticed :

Link will be created only once the Aggregation Task is ran , ie if the Application is requested for create
Provisioning will be done at the End point , but no link can be seen on the Identity Cube .

Link will be deleted if the Application is requested to perform delete .

getSQL operation will work only once the link is present on the account.

Database - JDBC Application Configuration Using OOTB Connector - Aggregation

1. Created the Custom Table Named “MARS”.
2. Below are the Details of the Custom Table which need to be Integrated for Provisioning and Aggregation.

 3. Select the Application Type as JBDC , Provide the Name , Owner and Application Type.

4. Click on Configuration → Setting  and Provide the JBDC Connection Settings and Query Settings.

5. Click on Test Connection and Check the Connectivity with the DB Which need to be Integrated.

6. Navigate to Schema ,and click on Discover Schema Attribute to Fetch all the Table column from the Database table provided to be managed.

7. Based on the Column which need to be managed , delete the unwanted column from the attributes list.

8. Modify Identity Attributes and Display Attribute based on the Requirement.

9. Navigate to Correlation and Add the Correlation Rule based on which the Account need to be Aggregated and link to the Identity.

10. Navigate to Setup and Click on Task , To create the Account Aggregation Task to Pull the Data from the Table.

11. Click on Save and Execute and Check the Result from the task Result Tab.

12. Navigate to Application → Application Definition → Accounts and see all the Accounts which are pulled from the DB.

13. Checking the linked Account to the Identity,

14. Navigate to Identity → Identity Warehouse → Select the Identity and Click on the Application Accounts to see the Accounts if the Links exists.