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.

Best Practice

Test the SQL queries outside of SailPoint to make sure you get the results you expect. If you receive a general error, check that your query does not contain a semicolon (;) at the end.

Account Aggregation

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

Enter the query into the Account SQL Query field in the Account Query Settings.
Go to Account Management > Account Aggregation and select Start Aggregation.
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.

Enter the query into the Group SQL Query field in the Group Query Settings.
Go to Entitlement Management > Entitlement Aggregation and select Start Aggregation.
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

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;

Note

In cases where the stored procedure can not be modified, use the JDBC BuildMap Rule to set the account status. For more information, refer to Identity Security Cloud Rule Guide - BuildMap Rule.

Data Merging Support

For the JDBC SaaS 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>]
```

Note

The Account SQL query should have order by clause when merging is enabled. The query should be ordered by the index columns defined in the source.

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.

Perform a full aggregation as described in Loading Account Data.
Create the delta change table (as described below).
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.

Note

Currently SailPoint does not support delta aggregation for groups. Open a support ticket for assistance.

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:

Create two tables:
- One table to use for account aggregation, where all identity data resides.
- Another table to capture only those identities who are undergoing modifications. This table must contain two columns:
  - 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 accounts in SAP HANA Cloud Database is as follows:
```
CREATE COLUMN TABLE EMPLOYEES_DELTA (EMPLOYEE_ID NVARCHAR (20), ACTION NVARCHAR(10));
```
Assign the privileges to read and delete the records from the tables you created in the previous step to the connection user whose credentials you entered in the source configuration page within SailPoint.

Create triggers on the master table (the account aggregation table) which activate during aggregation processes. The triggers should cause the connector to store modifications in the delta table.

For example, in SAP HANA Cloud databases, the example trigger below writes the EMPLOYEE_ID in the account aggregation table, which has undergone modifications, along with the respective action.

Key

Table for account - EMPLOYEES
Delta table for account - EMPLOYEES_DELTA

CREATE TRIGGER TRIEMPDELTA
AFTER INSERT OR UPDATE OR DELETE
ON MYSCHEMA.EMPLOYEES
FOR EACH ROW
BEGIN
    -- For INSERT operations
    IF :NEW.EMPLOYEE_ID IS NOT NULL AND :OLD.EMPLOYEE_ID IS NULL THEN
        INSERT INTO MYSCHEMA.EMPLOYEES_DELTA (EMPLOYEE_ID, ACTION)
        VALUES (:NEW.EMPLOYEE_ID, 'Insert');
    END IF;

-- For UPDATE operations
    IF :NEW.EMPLOYEE_ID IS NOT NULL AND :OLD.EMPLOYEE_ID IS NOT NULL THEN
        INSERT INTO MYSCHEMA.EMPLOYEES_DELTA (EMPLOYEE_ID, ACTION)
        VALUES (:NEW.EMPLOYEE_ID, 'Update');
    END IF;

-- For DELETE operations
    IF :NEW.EMPLOYEE_ID IS NULL AND :OLD.EMPLOYEE_ID IS NOT NULL THEN
        INSERT INTO MYSCHEMA.EMPLOYEES_DELTA (EMPLOYEE_ID, ACTION)
        VALUES (:OLD.EMPLOYEE_ID, 'Delete');
    END IF;
END;

After you finish the delta aggregation the tables are reset/re-initialized and start capturing changes again.

Aggregation Retry Configuration

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

You can update these parameters using the SailPoint REST API.

Note

For more information on SailPoint's REST APIs, refer to Best Practices: REST API Authentication and REST API - Update Source (Partial) in the SailPoint Developer Community.

Enable Retry

Note

Once Retry is enabled, it's applicable for all aggregation types.

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".

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.