Aggregation

For more information about aggregation, refer to Loading Account Data.

The following topics provide additional information for configuring aggregation:

Aggregating Accounts and Entitlements

After you create and test the connection, you can load accounts or entitlements.

You must first discover the schema and identify attributes to use for required identity data before loading accounts or entitlements.

Account Aggregation

After you have discovered the schema and identified required attributes, you can load accounts.

Prerequisite: Ensure you entered an Account SQL Query into the source connection. For more information, refer to Account Query Settings.

1. Go to the Import Data tab and select Start beside Manual Aggregation. For more information on running a manual aggregation, refer to Starting a Manual Aggregation.

2. Check the results in the Aggregation Activity log.

   If the results do not seem right, try running the query as a database user and compare the number of rows returned by the query.

Entitlement Aggregation

Load entitlements to SailPoint from the JDBC server.

1. Enter the query into the Group SQL Query field in the Group Query Settings.
2. Manually start an Entitlement Aggregation in the Import Data tab.

3. Check the results in the Aggregation Activity log.

   If the results do not seem right, try running the query as a database user and compare the number of rows returned by the query.

Loading a Single Account or Group

Loading a single account or group is helpful when you want to check the status of an account or entitlement.

The normal SQL syntax is:

Select * from <TableName> where NativeIdentity = '$(identity)'

For example:

Select * from Users where UserID = '$(identity)'

• You configure the SQL query for accounts in the Single Account SQL Query field in Account Query Settings.
• You configure the SQL query for groups in the Single Group SQL Query field in Group Query Settings.

Account Status

For the JDBC source, to set an account as enabled or disabled based on the values inside a specific column, you can add the IIQDisabled (string) attribute in the source account schema. For more information, refer to Adding Attributes to a Source Schema.

The following SQL query can be used to set up the state for the account:

select <Column1>, <Column2>, <Column...n>, case when <conditional Column for enable disable> then 'true' else 'false' end as "IIQDisabled" from <table_name>;

For example:

select EmployeeId, FirstName, LastName, IsActive, Groups, case when IsActive=0 then 'true' else 'false' end as "IIQDisabled" from Employee;

If you are using a stored procedure, the stored procedure needs to be updated to set a status.

For example:

CREATE PROCEDURE empstatus AS select empid, FirstName, LastName, isactive, Groups, case when isactive=0 then 'true' else 'false' end as "IIQDisabled" from master.dbo.emp GO;

Data Merging Support

For the JDBC source, if the data for a single account spans multiple rows of database table, you can merge the data for successful aggregation.

For the usage of the REST APIs for the following attributes and keys, refer to:

• API to get source details:

  https://developer.sailpoint.com/docs/api/v2024/list-sources

• API to get specific source's current schema details:

  https://developer.sailpoint.com/docs/api/v2024/get-source-schemas/

• API to add new schema attribute to source:

  https://developer.sailpoint.com/docs/api/patch-requests/#add

For example, if you want to merge data of the attributes such as email and phone, you can use the REST API to set the mergeRows attribute and use the value as true.

If you want to merge the columns, set form-data values as follows:

• key: use the indexColumns attribute key

• value: the value of this key must be the name of the column or list of columns that you want as index column for merging. For example, you can you use value in a format such as:

  [<IndexKey1>, <IndexKey2>,...<IndexKeyn>]
  

• key: use the mergeColumns attribute key

• value: the value of this key must be the name of the column or list of columns that you want to merge with the index column. For example, you can you use value in a format such as:

  [<MergeKey1>, <MergeKey2>,...<MergeKeyn>]
  

eg: select * from <Table> order by IndexKey1, IndexKey2

Delta Aggregation Tables

When delta aggregation is enabled, only accounts that have changed since the last aggregation are loaded. A delta aggregation typically takes less time and fewer system resources than a full aggregation.

1. Perform a full aggregation as described in Loading Account Data.
2. Create the delta change table (as described below).
3. Enable delta aggregation in your source as described in Account Delta Aggregation.

The next time you load accounts, only accounts that have changed in the source database tables are loaded.

Creating the Delta Change Table

The database table containing the delta changes attribute must be provided with the value of table name (this is what you enter in the Database Table Name that Contains the Delta Changes field). This table must have read and write permissions.

To create the Delta Change tables, complete the following:

1. Create two tables to capture the identities whose data is modified in the master tables:

   • One table for capturing the account whose attributes, entitlements, or direct permissions are modified in the master table for account, master table for its entitlements, or master table for its direct permissions, respectively.
   • Another table for capturing the account-group whose attributes are modified in the master table for account-group.

   Each of the two tables must contain two columns such that:

   • The first column stores the identity attribute defined in SailPoint.
   • The second column stores the action. The values of the action can be Insert, Update, or Delete.

   For example, the SQL command to create such a table for account or entitlement in Oracle Database is as follows:

   CREATE TABLE USER_DELTA (USER_ID VARCHAR2(20), ACTION VARCHAR2(10));
   

2. Assign the privileges to read and delete the records from the tables created in Step 1 to the connection user whose credentials you entered in the source configuration page within SailPoint.

3. Create the triggers on the master table.

   When there is a change in the attributes, entitlements, or permissions on the master table (or the tables that are linked to it, like the Entitlements table and the Permissions table for the Account), this triggers the system to write the Account Identity attribute in the first table created in Step 1.

   For example, in Oracle databases the following trigger writes the user IDs in the first table (Account table), which has undergone some modifications, along with the respective action:

   CREATE or REPLACE TRIGGER T1
   AFTER DELETE OR INSERT OR UPDATE ON USER_MASTER
   FOR EACH ROW
   BEGIN
       IF INSERTING THEN
           INSERT INTO USER_DELTA (USER_ID, ACTION)
           VALUES (:NEW.USER_ID, 'Insert');
       END IF;
   
       IF UPDATING THEN
           INSERT INTO USER_DELTA (USER_ID, ACTION)
           VALUES (:NEW.USER_ID, 'Update');
       END IF;
   
       IF DELETING THEN
           INSERT INTO USER_DELTA (USER_ID, ACTION)
           VALUES (:OLD.USER_ID, 'Delete');
       END IF;
   END;
   /
   

   With the above example, triggers can be created for the following tables also by replacing USER_MASTER with the following respective table names:

   • Entitlements table for account (ACCOUNTENTITLEMENT_MASTER)
   • Permissions table for account (PERMISSION_MASTER)

4. After the delta aggregation completes, all processed rows are deleted from the delta tables. Rows that are added during or after the aggregation are processed in the next delta aggregation.

Aggregation Out of Memory Error and Resolution

If your JDBC source has to aggregate a large amount of data, it may encounter an out of memory error. To mitigate this issue and aggregate the data, you can perform one of the following solutions:

• Use the TYPE_FORWARD_ONLY ResultSet type for JDBC drivers that support it.
• Increase RAM of the Java heap size (memory) in the Java virtual machine for JDBC drivers that do not support the TYPE_FORWARD_ONLY ResultSet type.

JDBC drivers that support the TYPE_FORWARD_ONLY ResultSet type read the data in the forward direction only which reduces data caching and results in lower memory consumption. Use the SailPoint REST API to update JDBC drivers that support the TYPE_FORWARD_ONLY ResultSet type.

Use the following attribute keys:

For JDBC drivers that don't support the TYPE_FORWARD_ONLY ResultSet type, increase the RAM allocated for the Java heap size (memory) in the Java virtual machine. To increase the allocated RAM:

1. Create a text file and name it java_overwrites.yaml. Copy the following parameters into the file.

   #Xmx: 912m
   #DbsfPoolSize: 5
   

   Currently, the virtual appliance only supports the following CCG JAVA HEAP options:

   • Xmx
   • DbsfPoolSize

2. Uncomment the two lines #Xmx: & #DbsfPoolSize by removing the #. Then configure your required values and save the file.

   Note

   The default value for Xmx, 912m, represents 912 MB. This parameter can only be configured in megabytes. For example, if you want to configure 24 GB, set Xmx to 24000m.

   For example:

   Xmx: 24000m
   DbsfPoolSize: 5
   

   Caution

   Always leave at least 3GB of RAM on the virtual appliance to run non-CCG processes.

3. Copy the java_overwrites.yaml file to the virtual appliance in the following directory:

   /home/sailpoint/ccg/
   

   Note

   On newer CCG images, CCG is owned by root. To add the java_overwrites.yaml file, you may need to run the following command:

   sudo /usr/bin/chown sailpoint /home/sailpoint/ccg
   

4. Restart CCG by running the following command:

   sudo systemctl restart ccg
   

5. Confirm the ccg.log, found at /home/sailpoint/log/ccg.log does not contain errors related to the new configuration.

Aggregation Retry Configuration

Use the following parameters to specify the number of aggregation retries, the delay between each attempt, and create custom retry exceptions.

Enable Retry

This enables you to configure the number of times the system attempts to retry aggregation and the delay between each attempt. Use this feature to retry the whole aggregation in case of failure.

To enable this feature, set the connRetryEnable attribute to true.

Retry Count

Specify the number of times you want the system to retry aggregation. The maximum attempts is 10.

To specify the number of retries, set the connMaxRetry attribute and enter the value as a <string number>.

Delay Between Retry Attempts (in Seconds)

Specify the delay time between attempts in seconds. The maximum is 10 seconds.

To specify the delay time, set the connWaitTimeForRetry attribute and enter the value as a <string number>.

connRetryExceptionList

This parameter must be set to retry only specific exceptions.

Use the following format:

• key: connRetryExceptionList
• value: a comma separated list of the exception strings

For example, you can use the value: [<Communications link failure >, <Connection reset >]

In this case, the system only retries exceptions that contain the strings "Communications link failure" or "Connection reset".

Account Schemas

Manage the account schema from the JDBC source. The Account Schema option in the source's Import Data tab enables you to discover your JDBC account schema, edit the schema, and add attributes to the schema.

Discover the Schema

You must discover the schema and identify the attributes to use for the required identity data before doing anything else.

Prerequisite: Ensure you entered an Account SQL query into the source connection. For more information, refer to Account Query Settings.

1. From your connector page, go to Import Data > Account Schema.

2. Select Options > Discover Schema.

   The Account SQL Query automatically executes and the discovered attribute names are displayed.

3. Define the attributes to be used for Name and Account ID. Mark them in the Account ID column.

Add a New Account Schema Attribute

Add additional attributes to your JDBC account schema from SailPoint.

1. In the JDBC source page, go to Import Data > Account Schema.

2. Select Add New Attribute to create a new attribute.

   A form to configure the new attribute displays.

3. Add a Name and unique Description to the attribute, then select a Type. These are required fields.

4. (Optional) As required, check the boxes for Entitlement and Multi-Valued.
5. (Optional) If you want to create another new attribute after saving the current one, check the Add Another box. When you save the current one, another form displays for the next attribute you want to create.
6. Select Save.

Edit the Account Schema

Give the account schema a unique description, and define the attributes for the schema's Account ID and Account Name.

1. In the JDBC source page, go to Import Data > Account Schema.

2. Select Options > Edit Schema.

   A form displays which allows you to update the schema.

3. Enter a unique description into the Description field.

4. Select the attribute to use as the schema's Account ID from the Account ID dropdown.
5. Select the attribute to use as the schema's Account Name from the Account Name dropdown.
6. Select Update.

Entitlement Schemas

You need to manually add and configure entitlement schemas to the JDBC source.

Add Entitlement Types

This process describes how you can add entitlement types (entitlement schemas) and configure their attributes in SailPoint.

1. In the JDBC source page, go to Import Data > Entitlement Types.

2. Select Create Entitlement Type.

   A form to configure the new entitlement type displays.

3. Give the entitlement a Name and a unique Description.

   Note

   Once you enter the entitlement type's name you can't change it later.

4. (Optional) If the new entitlement should include permissions during aggregations, check the Include permissions in aggregations checkbox.

5. Select Save.

Add Attributes to an Entitlement Type

Edit the attributes associated with each of your entitlement types.

1. In the JDBC source page, go to Import Data > Entitlement Types.

2. Select an entitlement type from the list.

   The entitlement type entry expands to display the associated attributes.

3. Select Add New Attribute to create a new attribute.

   A form to configure the new attribute displays.

4. Add a Name and unique Description to the attribute, then select a Type. These are required fields.

5. (Optional) As required, select the checkboxes for Entitlement and Multi-Valued.
6. (Optional) If you want to create another new attribute after saving the current one, select the Add Another checkbox. When you save the current one, another form displays for the next attribute you want to create.
7. Select Save.

Configuring Multiple Group Objects

The JDBC connector supports multiple group objects for Entitlement Aggregation. Depending on the managed system that you are connecting with the JDBC source, you can set up attributes such as entitlements and roles. You can add one or more entitlement schemas for aggregation.

Adding or Updating Multiple Group Objects from SailPoint

Once you establish a connection to the JDBC database as described in Account Connection Settings, you can access the Account Schema and Entitlement Types options in the Import Data tab.

Adding an Entitlement Schema Using the REST API

In the JDBC source for entitlement aggregation, you can add the entitlement schema using the REST API. This topic provides information on adding the entitlement schema using the REST API. Alternatively, you can add the entitlement schema in the SailPoint user interface. For more information, refer to Entitlement Schemas.

Example request body:

{
  "id": "string",
  "name": "string",
  "nativeObjectType": "string",
  "identityAttribute": "string",
  "displayAttribute": "string",
  "hierarchyAttribute": "string",
  "includePermissions": true,
  "features": [
    "string"
  ],
  "configuration": {},
  "attributes": [
    {
      "name": "string",
      "type": "STRING",
      "schema": {
        "type": "ACCOUNT_CORRELATION_CONFIG",
        "id": "string",
        "name": "string"
      },
      "description": "string",
      "isMultiValued": true,
      "isEntitlement": true,
      "isGroup": true
    }
  ],
  "created": "string (date-time)",
  "modified": "string (date-time)"
}

Configuring Multiple Group Objects with the REST API

Use the following request body for the REST API:

{
  "id": "string",
  "name": "string",
  "nativeObjectType": "string",
  "identityAttribute": "string",
  "displayAttribute": "string",
  "hierarchyAttribute": "string",
  "includePermissions": true,
  "features": [
    "string"
  ],
  "configuration": {},
  "attributes": [
    {
      "name": "string",
      "type": "STRING",
      "schema": {
        "type": "ACCOUNT_CORRELATION_CONFIG",
        "id": "string",
        "name": "string"
      },
      "description": "string",
      "isMultiValued": true,
      "isEntitlement": true,
      "isGroup": true
    }
  ],
  "created": "string (date-time)",
  "modified": "string (date-time)"
}

Partitioning Aggregation

The JDBC connector supports the manual partitioning through configured SQL statements.

For example, if there is an employee dataset that has 100,000 rows with a sequential employeeId field, the partitioning statements that can be used are as follows:

select x,y,z from a where employeeId <= 10000;
select x,y,z from a where employeeId > 10000 AND employeeId <= 20000;
select x,y,z from a where employeeId > 20000 AND employeeId < =30000;
…
select x,y,z from a where employeeId > 90000;

The above example has 10 partitions, handling approximately 10,000 accounts. The last row (with employeeId > 90000) handles a larger number of accounts depending on the total number of employees in the system.

To enable partitioning, add the following attribute key-value pairs into the source XML using the REST API.

• Attribute Key - partitionAggregationEnabled; Value - true to trigger a specific operation rule.
• Attribute Key - partitionMode; Value - true to assign the rule to the source.

• Attribute Key - partitionStatements; Value - enter either of the following to use SQL statements or stored procedures for aggregation:

  SQL query, for example:

  select x,y,z from a where employeeId <= 10000;
  

  Stored procedure, for example:

  EXEC emp ;
  

  Important

  To use a stored procedure with the partitioningStatements instead of a SQL query, you must also add the useStoredProcedurePartition attribute key with the value true.

Documentation Feedback

Feedback is provided as an informational resource only and does not form part of SailPoint’s official product documentation. SailPoint does not warrant or make any guarantees about the feedback (including without limitation as to its accuracy, relevance, or reliability). All feedback is subject to the terms set forth at https://developer.sailpoint.com/discuss/tos.