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. 

[bbagaria]

Same boat as @aaron_burgemeister . If the IDX is not needed, we should have it removed.

Also no IIQ implementation can be without code and mind you the code is whatever APIs are provided by Sailpoint. In our case no custom DB updates/creates but regular LCM, rules, workflows etc. We even went to far to look for closing, de caching, locking/unlocking objects as recommended by Sailpoint. Still we have these issues.

Latest case in point, we launched our quarterly certification, the launch failed with the whole campaign going to error state for no reason. We launched it again and this time it launched. The campaign which went dud the first time now has all the IDX as null in one of the cert archive entity item table, so we cannot delete the old cert campaign unless we run this IDX again.

Going to the question of engaging support, imagine trying to replicate this behavior when you have over 5K manager globally in every country certifying more then 2.5mil line items & staging running across 6 servers (on a very large infra)! What would loggers do & who can stop everything in production while trying to reproduce issue?  The staging takes hours itself with complicated exclusion (not inclusion) rules and you still need to have the system processing LCM events for JML as BAU across every timezone! 

Have already posted on Sailpoint Ideas, but if the product needs to scale up to have LCM, JML, Certs of every kind (transfers, LM, entitlement Owners, Targetted) etc, then the tables needs to be much more normalized and the whole idea of CLOB and IDX seem to be preventive to that. Just my 2 cents.

[kjperkin]

@bbagaria Is that the actual scale of your deployment? We have what I thought was a large deployment, but you mentioned global, and we are certainly not global.

@teresa_warren @Eric_Mendes_CISSP 's Would it be appropriate to use this rule to check and clean up a non-production environment so that it can be monitored for future issues? Could the rule be used in "read only" mode to routinely check and alert for any IDX issues?

If this rule is suitable for use in a production environment, and it correctly identifies and repairs IDX issues, why would it be bad to set it up to run as routine maintenance? I have some ideas, but I'd like your insights as well. The reasons that come to my mind are:

• If the jobs or other code in the environment do not lock objects appropriately, then the repair task could save an object that some other piece of code has loaded and intends to save. This would cause that other piece of code to fail.
• It may be time or resource intensive to identify idx errors and fix them
• Running this as routine maintenance would only serve to mask some underlying problem that needs to be addressed.

These all seem like good reasons to only run it as directed, but at the same time I value keeping things clean and running smoothly.

[teresa_warren]

@kjperkin 

This rule is only meant to be used in situations where a null IDX is suspected, and this tool is not meant to be run as a regular scheduled task. This could be suspected in the case where certain NullPointerException errors are presented, or for issues identified by SailPoint Support.

The reason we do not recommend using this as a regular task or monitoring is that this Rule is updated and tested with each new release of IdentityIQ, and then is updated on this post. Once a rule is scheduled as a task in an environment, this is kind of an "out of sight, out of mind" issue, where someone may not think to update the rule that is scheduled until they see a failure.

Another reason is what you mentioned, where you may be fixing an issue without identifying the root cause of the problem, so you get into a cycle of repair, instead of fixing the code/deployment causing issue.

[klbumgar]

Is there a way to get notified every time SailPoint makes and update to IDX rule?

[a_ladygin]

please add support for AH scheme specified via jndi. Currently, the rule fails since no username password specified in iiq.props for AH scheme