alon (Prolink) asked a question.
How to find the roles of old change items with change_item_type of "AddUserToPendingBizRole"
Hello,
I have the following question: I see in the database in pv_change_request_detail
old change items with change_item_type of "AddUserToPendingBizRole".
I want to find what are the relevant roles (from pv_unified_entitlement or pv_roles or the roles table),
but the id of the role (stored in value_id column in pv_change_request_detail) is not found in t_av_role
(I also tried t_av_roleversions).
Where the values of the old pending roles is found ?
So do you have other "symptoms" for these change items?
And what is your goal if finding these old roles?
Asking mostly because we are currently looking into a problem where roles sometimes end up in "Request->Activites->By pending verification".
Where we currently think this strange behavior could be caused by the "splitting algorithm" (Admin->System->Settings->Max items per change request)
Because if this is the same problem you see, then these roles will be "ghosts", and so far we only find references to them in pv_change_request_detail, where state is still PZ for a change request that is in "complete state".
And where we (so far) have been able to trigger the problem by using "active split algorithm + running rule "Add Users to Roles". (8.0.0 P08, but this bug I mentioned here has probably been around for a long long time, maybe even 7.5.x)
@OverthinkerDave (Customer)
Thank you for your reply.
The goal is for a report that shows the user direct entitlements (from pv_user_direct_access)
with additional information from pv_change_request (when the entitlement was requested for the user, the name of the request etc.)
check T_AV_AUDIT_SNAPSHOT
search using this condition: object_data like '%P247%'
247 is the value from pv_change_request_detail (value_id column)
@Boris.Lekumovich (RSA)
Thanks for this query ! I didn't know this table until now.
If I understood correctly the value of OBJECT_ID column in T_AV_AUDIT_SNAPSHOT
is the value of the entitlement_id of the role.
See my query for example:
For one user (with 2 change items) it took 8 seconds.
I didn't try this query for all users and all change items of type adding GlobalRoleVersion.
Probably this is the condition which is slowing down the query
AND object_data like '%P' || value_id || '%'
However I don't have any ideas on alternative approaches