Correct IDX rule to detect and fix IDX values

[#1]

• Overview
• Why IDX values are used and sometimes need correction
• Executing the correct IDX rule

Overview

The attached rule was updated in September 2023 to include IdentityIQ 8.4. As a best practice, always use the latest rule as indicated for the version of IdentityIQ.

The latest update and recommended version of the "Correct IDX" rule is found in this article and will be periodically updated as needed.  It is supported by all IIQ releases, currently versions up to 8.4. This rule should not be part of a maintenance routine, but as a one-time fix when recommended by SailPoint Support.

The attached "Correct IDX" rule resolves issues with IdentityIQ including NullPointerException error, related to null indexes. Exceptions and errors are visible in IdentityIQ logs, and can occur at various execution points including identity refresh, aggregation, perform maintenance task execution, or during other process execution. Review the full stack trace of the exception to ensure that this rule will provide benefit to correcting the issue. If you are unsure about executing this rule, please reach out to Support by opening a ticket and include the logs containing the error stack trace, along with a description of when you are seeing the NullPointerException.

This rule should not be confused with the IIQ Integrity Scanner (IdentityIQ IDX Integrity Scanner), which should be considered only for IdentityIQ versions prior to 7.3 only. The approach described in the IdentityIQ IDX Integrity Scanner document has been superseded via the plugin, Support Data Collector Plugin.

Why IDX values are used and sometimes need correction

IDX values are created and updated by Hibernate, a framework leveraged by IdentityIQ.  IDX values are used in part to iterate over a list of objects more quickly, in an operation such as such as finding all work items in a certification, aggregation of accounts on identities, refreshing links on identities, etc. The IDX value is a hibernate data-persistence layer pointer that provides the application with the location of the object within a list.

The most common cause for incorrect IDX values or NullPointerException errors is usually incorrect updates using custom code, for example, failing to lock during an update to an object, or when objects are deleted from IdentityIQ without taking proper steps to remove related linked objects. Other causes are one-offs, for example caused by network re-transmissions of packets or data due to collisions, packets arriving out of order, temporary loss of communication within a network, the database cache temporarily not accepting updates, a driver issue at the data layer, or for other reasons.

Executing the correct IDX rule

Always test this rule in a lower environment prior to using in production and ensure that you have appropriate backups in place to handle any recovery in the occurrence of any data issues. Keep in mind this rule is provided as a tool to assist customers, but you are responsible for maintaining the rule and ensuring its proper use. Please follow the directions below to perform this work:

1. Download the file at the bottom of this article: Rule - IDXCheckAll.xml.zip
2. Unzip above and it contains Rule - IDXCheckAll.xml
3. Go to Global Settings, Import from File
4. Under "Import Objects", select the Browse button and find the unzipped file Rule - IDXCheckAll.xml
5. Click the Import button, and the following files are imported.

Rule:SupportRuleIDXCK

TaskDefinition:Support IDXCK Rule

TaskDefinition:Support Task for Hibernate IDX

6. The task will show up as "Support Task for Hibernate IDX" in the Setup->Tasks on the Task tab.
7. The first execution should only require the options "process all DB tables" and "scan tables for null-valued key-column composited with non-null 'idx' key-column" options. This executes the rule in "read mode” and does not update the database.
8. If the task execution finds NULL IDX issues, it will fail with the pertinent errors highlighted in red, and warnings in yellow on the task result. If there are no related NULL IDX issues, then it will simply return a Processing Summary such as below:

<server name> processed nn out of nn mysql table(s) for v.r release

9. If the task returns errors, then you would want to execute the task again with the options above, plus "update DB table rows to resequence 'idx' key-column" selected. This will update the database to re-sequence the affected database rows. The summary should include the updates performed. It is good to execute the task a 2nd time, to ensure that the updates have been done, and you return successful task results (no red items noted).

Please note that this task will only repair NULL IDX issues and cannot fix issues such as null parent foreign key issues, or similar items that may show up as warnings. These types of issues are not as critical as correcting the NULL IDX issues.

If you have any questions or comments regarding this rule, please open a ticket with SailPoint Support. 

[keithsmith]

We could use some screen shots of different scenarios, using the new TaskDefinition.

Prompt	field type	What it sets
update DB table rows to resequence idx key-column	boolean (Checkbox)	flagFix
select coverage of DB tables	boolean (Checkbox)	flagAllTbls
single DB table name to process	string (Text)	strTblOverride
use single SQL statement to recompute all DB table rows	boolean (Checkbox)	flagFixAllRows
do not run SQL statements	boolean (Checkbox)	flagNoRunSQL
string to override runtime IIQ release	string (Text)	strIIQVerOverride
string to override runtime RDBMS product	string (Text)	strSQLSyntaxOverride
scan table rows for null values in key-column composited with 'idx' key-column	boolean (Checkbox)	flagParentCol

[nazampreetkaur]

Hi

I am running this rule in update mode and it is not updating any tables. I figured out that one of the checks for updating the tables is incorrect. Code is trying to call update method if flagNoRunSQL is set to True.

[sujitidentity]

Hi,

We have IIQ8.1 installed in our environment and this script is failing even we added 8.1 version in if condition. Can someone please share latest code compatible for 8.1 version.

Thanks in advance!!

Sujit

[aaron_burgemeister]

Same here; why in the world the idx columns always break, or even exist, is beyond me, but we are dealing with this on a daily basis at this point.

[jason_qiu]

Same here. Fails in IIQ8.1, is there any direction from SailPoint?

[sujitidentity]

We removed few tables which does not exist in 8.1 any more and now script is completing with warnings. However, this is not right approach and I am waiting for latest updated script which will have correct tables included in the script as per latest version. Very surprised to know that SailPoint is not giving solution for such a important issue as OOTB feature.

[Eric_Mendes_CISSP]

Hey @sujitidentity , @jason_qiu , @aaron_burgemeister, 

Out of curiosity, is there a specific use case you are attempting to resolve by running this IDX rule? From reading your comments, it seems to be that you seem to be running the rule routinely, but it specifically states within this article: "This rule should not be part of a maintenance routine, but as a one-time fix when recommended by support". Essentially, if you are not attempting to resolve an outstanding issue, you should NOT be running this on a regular basis. If it was recommend by SailPoint support to run the rule, then they would be able to assist you in updating the logic for your specific use case. 

In my experience, the only times I have ever run into huge IDX issues were with IdentityIQ 7.0 and prior versions where the IDX table was utilized for the relationship between identities and application links. These issues were pretty evident since attempting to interact with the identity object would throw an uncaught exception. In IdentityIQ version 7.1, the identity to link relationship was changed so that the idx column was not utilized any longer. In IdentityIQ 7.1+, I have never experienced an IDX related issue even though it does seem like the system still utilizes an idx column for other object relationships because it is a Hibernate feature. With that said, I'm not actively running the IDX task for any of my clients running IdentityIQ 7.1+ per the recommendation within this article. 

Here are the Release Notes for IdentityIQ 7.1 which removed the need for the idx column to define the order of accounts associated with an identity:

"The relationship between an identity and an account (referred to as a link in the database) has changed to remove the need for an index (idx) column to define the order of accounts associated with an identity. The upgrade process does not delete this column or modify the data in the column, but after the upgrade the data in the column will no longer be maintained or used in any way.

Some maintenance tools and custom code such as the IdentityIQ IDX Integrity Scanner that identify issues with the data in this column will either no longer work or will report errors that do not need to be corrected until the tools are updated to be compatible with this release."

Hopefully this helps! 

Cheers!

[aaron_burgemeister]

Eric, thanks for responding.  I wish experience matched the documentation.

That release notes may state this is no longer required is one thing, but the comments in this thread would seem to make it apparent that problems remain.  If idx is not needed, why didn't the upgrade (to 7.2, 7.3, 8.0 or 8.1) remove them?  If they are not required, why do we continue to get IDX issues in logs regularly if values are not sequential? If they are not required, why does running the rule still fix those issues?

If Sailpoint thinks the issues are resolved, they should remove the idx column from the tables (all of them) storing the values.  In the meantime we run the cleaner regularly to fix problems that continue, in IIQ 8.1, to nag about non-sequential idx column value in one table or another (spt_scope and spt_certification_entity so far this week).  The issues crop up when using standard tools to work with rows in those tables, so it's not like we're even doing something fun like inserting/modifying rows in tables directly; the code just seems to be buggy.

[Eric_Mendes_CISSP]

Hey @aaron_burgemeister , 

It is my pleasure to provide my insight based on my experience in the field as a SailPoint Certified Architect. 

Since this task should only be run as a recommendation via SailPoint Support, did you already submit a ticket detailing your issues? I'm assuming not since you would be relaying your concerns directly to their support staff. I'm thinking that you are getting the IDX errors only from running this IDX task, which again, this task is not intended to be used for maintenance purposes. 

"That release notes may state this is no longer required is one thing, but the comments in this thread would seem to make it apparent that problems remain.  If idx is not needed, why didn't the upgrade (to 7.2, 7.3, 8.0 or 8.1) remove them?"

It is important to point out that the release notes highlight the fact that not all IDX relationships were removed, but rather just this one specific idx relationship. Also, as I mentioned previously, the idx columns are a native feature of Hibernate, so I'm assuming SailPoint Engineering would need to craft custom solutions if they removed the idx relationships. Since that work takes time to build, test, integrate and maintain, I would think they would only devote that effort for the places where the most issues are most prevalent/reported. I imagine most of the idx issues that had tickets opened with SailPoint Support were issues with the identity and link relationship, hence why it was removed.

If you are having issues with other idx relationships in your implementation, it is very important to open up a ticket with SailPoint Support so they can review your implementation and detect if it is an issue with the product or any custom code/logic that is being implemented. Have you submitted any tickets in regard to your idx issues? It is my understanding that most idx issues come down to how custom code is implemented or issues with the database transactions/network communications. If there is a specific idx relationship that keeps breaking, SailPoint Support should be able to help diagnose the underlining issue and see if they can replicate in their testing environments that do not feature all of the custom logic and other variables of a client infrastructure. This is also why this article states that this rule should not be used for maintenance purposes and should only be used in conjuncture with SailPoint Support. If there is custom logic that is not implemented correctly which is causing your idx issues, executing this rule routinely will only help to disguise it. 

"If they are not required, why do we continue to get IDX issues in logs regularly if values are not sequential? If they are not required, why does running the rule still fix those issues?"

Which are logs/object classes are producing those errors? Is it just via running the IDX task? Without running the IDX task, does the system not function as expected, in addition to producing the idx error logs? Again, the idx relationships are still used in certain places in the product so you need to open a ticket with SailPoint Support in order to determine if the issues are related to your custom implementation or if it is a defect in the product. If it is a defect in the product, we all benefit from that being reported and hopefully resolved in a future patch or update. 

"The issues crop up when using standard tools to work with rows in those tables, so it's not like we're even doing something fun like inserting/modifying rows in tables directly; the code just seems to be buggy."

Are those standard SailPoint tools? From the phrasing used here, it sounds like you are manipulating data directly via the database and not through the SailPoint API? It is almost certain that any code will contain bugs to various degrees, so IdentityIQ will as well (I have personally discovered a few different bugs which I reported to SailPoint Support and resulted in updates to the product logic to resolve the bugs), but it is important to submit a ticket so it can be resolved either through updates to site custom logic or the application logic itself.

Cheers!

[aaron_burgemeister]

Support ticket: no, and Sailpoint has a hard time duplicating anything that isn't 100% reliable (in my experience), for example anything that is sporadic, or anything that I cannot cause to happen with X or Y in the UI, and they regularly push back on anything using their APIs from custom code with, "It's your code, so fix it."  In the end, I'd rather just work around it myself and hope that someday they'll fix their code (as they may have done in this case).

As mentioned, the latest issues were with scope objects (making me wonder if we're the only place using these extensively) and access certifications (clearly something many others are using).  Once in a while (not always) if I try to modify a scope hierarchy (moving a leaf or subtree around in the web UI) the IDX error will show up.  I'm hoping the web UI counts as something other than "custom code", but I've thought that same thing with other components going into a ticket and been told "unsupported" at the end of the day.

As far as I know we have nothing that makes changes to the IIQ database directly.  There are rules and workflows that effectively work with database-hosted objects as everything is, but it's all via IdentityIQ's API, which I presume is supposed to abstract away the minutia of database hacking, including problems introduced with the idx columns.  As a result, I do not think "custom code" should be the problem, but again I have thought that many times before and been told it was unsupported.

In the end I'd like the product to work reliably, yet the need for this IDX analyzer tool has persisted long past IIQ 7.1 (which I think was the version you said fixed some issues).  If there are enough customers asking for it through 8.0, and now through 8.1, then Sailpoint should fix those.  If they cannot get people to call in and report the issues, perhaps the comments above will help them understand others' (or at least my) hesitance in spending that time reporting bugs.  Until then, when the logs show some problem with the IDX column, I am usually able to make the old rule work to fix up the applicable table's idx column values, and then things work again (not scheduled, just as needed, and without spending time calling Support, sending them an About page, confirming this is Prod, finding a log file with nothing in it, and then waiting a couple days for a response to ask for logs to be turned up to actually get something useful from a sporadic issue).

Take care.