Overview
One common challenge in managing Saviynt requests is the confusion between the Request ID displayed in the User Interface (UI) and the corresponding identifiers in the backend database. The discrepancy arises because the UI often shows a user-friendly request identifier that doesn't directly correspond to the primary keys or identifiers used in Saviynt database tables.
This article will help clarify the differences between these IDs, provide an understanding of the common confusions, and offer precise SQL queries using specific database tables to help map the UI Request ID to the corresponding database entries.
Understanding the Difference: UI Request ID vs. Database Identifiers
In Saviynt, several identifiers are used to track requests:
Request ID in the UI: Typically displayed as a unique, user-friendly identifier for each request. This ID is often derived from the workflow instance managed by the Java Business Process Management (JBPM) system.
REQUESTKEY in the Database: This is the internal primary key for each request stored in the ARS_REQUESTS table. It serves as the main reference key for database operations but is not the same as the UI Request ID.
JBPMPROCESSINSTANCEID in the Database: Represents the workflow instance ID generated by the JBPM engine. This ID is crucial in mapping the user-friendly Request ID seen in the UI to the backend records.
Common Confusions and Challenges
Based on multiple discussions on the Saviynt forums, several users have reported confusion when trying to map the UI Request ID to the database due to:
Misinterpretation of Fields: Many users attempt to match the UI Request ID with the REQUESTKEY directly. However, the UI Request ID often corresponds to a part of the JBPMPROCESSINSTANCEID field.
Lack of Workflow Context: When multiple workflow versions exist or modifications are made, identifying the correct workflow version linked to a specific request becomes challenging.
Different Identifiers for Different Uses: The Saviynt system uses multiple fields (REQUESTKEY, JBPMPROCESSINSTANCEID, etc.) for different purposes, making it difficult to find the exact match.
There are at least 5 major forum posts discussing these issues, emphasizing the confusion surrounding these differences and how to accurately find the corresponding Request ID in the database.
How to Find the Corresponding Request ID in the Database
Step-by-Step Process
Identify the REQUESTKEY in the ARS_REQUESTS Table
Start by identifying the request in question using the ARS_REQUESTS table:
SELECT REQUESTKEY, JBPMPROCESSINSTANCEID, STATUS AS 'Request Status', WORKFLOWNAME FROM ars_requests WHERE WORKFLOWNAME = '<Workflow_Name>' AND STATUS = 'Pending';
Replace <Workflow_Name> with the name of the workflow you are investigating. This query retrieves all requests associated with a specific workflow name that are currently in a pending state.
Map the UI Request ID to the Workflow Instance
To match the UI Request ID with the backend data, use the JBPMPROCESSINSTANCEID field:
SELECT ar.REQUESTKEY, ar.JBPMPROCESSINSTANCEID, ar.STATUS AS 'Request Status', ar.WORKFLOWNAME FROM ars_requests ar WHERE ar.REQUESTKEY = '<Your_Request_Key>';
SELECT ar.REQUESTKEY, ar.JBPMPROCESSINSTANCEID, ar.STATUS AS 'Request Status', ar.WORKFLOWNAME FROM ars_requests ar WHERE ar.REQUESTKEY = '<Your_Request_Key>';
Replace <Your_Request_Key> with the actual REQUESTKEY from the previous query results. This will provide the JBPMPROCESSINSTANCEID, which can be cross-referenced in the UI to verify the matching request.
SELECT WORKFLOWKEY, WFNAME, WFVERSION, CREATEDATE, UPDATEDATE, STATUS FROM workflowhistory WHERE WFNAME = '<Workflow_Name>' AND CREATEDATE <= ( SELECT REQUESTDATE FROM ars_requests WHERE REQUESTKEY = '<Your_Request_Key>' ) ORDER BY CREATEDATE DESC;
SELECT WORKFLOWKEY, WFNAME, WFVERSION, CREATEDATE, UPDATEDATE, STATUS FROM workflowhistory WHERE WFNAME = '<Workflow_Name>' AND CREATEDATE <= (SELECT REQUESTDATE FROM ars_requests WHERE REQUESTKEY = '<Your_Request_Key>') ORDER BY CREATEDATE DESC;
This query identifies the workflow version active when the request was made, providing insights into why the request might be stuck or pending.
SELECT ROLES_HISTORYKEY, REQUESTABLE, ROLESTATE, ROLE_NAME, ROLETYPE, STATUS, VERSIONKEY, VERSIONSTATUS FROM roles_history WHERE ROLEKEY = ( SELECT ROLEKEY FROM roles WHERE ROLE_NAME = '<Your Role Name>' );
SELECT ROLES_HISTORYKEY, REQUESTABLE, ROLESTATE, ROLE_NAME, ROLETYPE, STATUS, VERSIONKEY, VERSIONSTATUS FROM roles_history WHERE ROLEKEY = (SELECT ROLEKEY FROM roles WHERE ROLE_NAME = '<Your Role Name>');
SELECT FIELDNAME, NEWVALUE, OLDVALUE, OPERATION, UPDATEUSER FROM roles_historychangelog WHERE ROLEKEY = ( SELECT ROLEKEY FROM roles WHERE ROLE_NAME = '<Your Role Name>' );
SELECT FIELDNAME, NEWVALUE, OLDVALUE, OPERATION, UPDATEUSER FROM roles_historychangelog WHERE ROLEKEY = (SELECT ROLEKEY FROM roles WHERE ROLE_NAME = '<Your Role Name>');
These queries provide a comprehensive view of any role changes that may have caused the request to be in its current state.
Conclusion and Best Practices
To effectively find the corresponding Request ID in the database:
Use JBPMPROCESSINSTANCEID for Mapping: This field is critical in matching the UI's Request ID to the backend database.
Utilize Workflow History: Checking workflow versions and modifications in WORKFLOWHISTORY helps clarify which workflow was in use.
Regularly Check Forums and Documentation: The Saviynt forums have multiple posts discussing these issues; reviewing them can provide insights into common pitfalls and best practices.
