Orphan Accounts Report Failure and Custom Report Workaround in Identity Manager

Products

• CA Identity Suite

Issue/Introduction

The Orphan Accounts Report in Identity Manager (IM) fails to generate for specific endpoints (e.g., "a custom endpoint to API Gateway"), resulting in indefinite loading and timeouts. This issue is not observed for other endpoints (e.g., "Active Directory Endpoint") even with larger data volumes. JasperServer logs indicate database-related errors such as "Error executing SQL statement for: Query.", "No more data available to read.", "ORA-01013: user requested cancel of current operation", "Report has been cancelled", and "Error closing connection. / Already closed."

Environment

dentity Manager (IM) - VAAP 14.5 GA (without SP1 or CP)
JasperServer version 7.1.0
Oracle Database

Cause

The primary suspected causes for the report failure are:

1. JasperServer Version Incompatibility: The JasperServer version 7.1.0 is not the recommended version (IM supports JasperServer 8.2), which may lead to integration issues or unexpected behavior with complex reports.

2. Database Connectivity/Timeout Issues: The Oracle database logs indicate "ORA-01013: user requested cancel of current operation," suggesting that the database is cancelling long-running queries, likely due to timeouts or resource limitations when attempting to execute the complex SQL query for the problematic endpoint.

Resolution

The direct issue of the standard Orphan Accounts Report failing to generate was not resolved because client was not able to  upgrading JasperServer in this instance. Instead, a successful workaround was implemented:

A custom report was created directly within JasperSoft. This custom report queries the Identity Manager database tables (specifically IMREPORT6 and IMRUSERACCOUNTRELATIONSHIP) to extract the necessary orphan account information. This approach bypassed the problematic standard report functionality and successfully delivered the required data to the client.

Additional Information

Best Practices:

• Version Alignment: Always ensure that all integrated components, such as Identity Manager and JasperServer, are running on officially supported and compatible versions as per the product documentation. Version mismatches can lead to unforeseen issues.
• Database Performance Monitoring: "ORA-01013" and "No more data available to read" errors are strong indicators of underlying database performance or timeout problems. Involve the database administration team to investigate query execution plans, resource usage, and potential database-level timeouts.
• Workaround Solutions: When faced with persistent issues in standard reporting, exploring the possibility of creating custom reports by directly querying the underlying database can provide a viable and timely workaround, especially if the required data is accessible and can be efficiently extracted.