Configuring a SailPoint IdentityNow JDBC Source with multivalue fields – ‘mergeColumns’

Recently I was configuring a SailPoint IdentityNow JDBC Source with data a little different than a simple series of single valued columns. The Source was an Oracle SQL View with around 19 columns of which one key column was multi-valued. It contained a list of the Roles a user is a member of.

Just like doing anything for the first time there was some trial and error before I got the JDBC Source all configured and the Import, Correlation and Aggregation working. The IdentityNow Compass documentation was pretty good, but obviously lacked a real-world example. That’s what I’ll cover in this post hoping it helps someone else, but also me the next time I have to do this.

Option to merge specified, but indexColumns were null

By default when creating the JDBC Source, the source configuration (only visible via the IdentityNow API) will have the mergeRows option set to True

connector_(boolean)mergeRows : True

Therefore when you attempt to import data from the JDBC Source where column(s) include data with multiple rows (multi-values) you will get the dreaded Red Box Error.

The documentation details configuring indexColumns and mergeColumns. In my JDBC dataset the ROLE_NAME column is the multi-valued column. Having authenticated to the IdentityNow API as detailed in this post, we can check the configuration of our newly created JDBC Source. We can then also update the configuration to allow us to import the data.

Update JDBC Source for mergeColumn(s)

The PowerShell script below will create an Index and Merge for the ROLE_NAME column.

# JDBC Oracle Source
$jdbcSourceURI = "https://$($orgName).api.identitynow.com/cc/api/source/get/YourSourceID"
$jdbcSource = invoke-restmethod -Method get -Uri $jdbcSourceURI -Headers @{Authorization = "$($v3Token.token_type) $($v3Token.access_token)"}

# JDBC Multi-value ROLES Column - Enable Merge
$jdbcMergeColumns = @{}
$jdbcMergeColumns.Add("connector_indexColumns", "ROLE_NAME")
$jdbcMergeColumns.Add("connector_mergeColumns", "ROLE_NAME")

$jdbcUpdateSourceURI = "https://$($orgName).api.identitynow.com/cc/api/source/update/YourSourceID"
$updateSource = Invoke-RestMethod -Method Post -Uri $jdbcUpdateSourceURI -Headers @{Authorization = "$($v3Token.token_type) $($v3Token.access_token)"} -Body $jdbcMergeColumns
$updateSource

Index value for [ROLE_NAME] on new object was null

Attempting to Import Data from the Source may then give you the error

Index value for [ROLE_NAME] on new object was null

If not all rows contain values for the Merge Column then we need to exclude them. That’s valid if we are using this Source Connection for Certifications anyway.

For me that meant updating my Account SQL Query to only return rows where ROLE_NAME is not NULL e.g.

SELECT * from TABLE_VIEW WHERE ROLE_NAME IS NOT NULL

Data should be sorted in ascending order

We’re almost there. Finally the data must be sorted into Ascending Order. The following error is pretty prescriptive.

Data should be sorted in ascending order

For me that meant updating my Account SQL Query again to order rows on the column I had flagged as Account ID and Name in the Account Schema. That column was USERNAME, so my query became;

SELECT * from TABLE_VIEW WHERE ROLE_NAME IS NOT NULL ORDER BY USERNAME ASC

Summary

If you have a SailPoint IdentityNow JDBC Source that has multi-valued fields in a column you need to;

• configure the Source (via API) for the merge_Index and merge_Column for that column
• alter your query to return only rows that contain values in that column
• sort the data in Ascending Order