cancel
Showing results for 
Search instead for 
Did you mean: 

Reconcile and Repair entry stored procedure issues (cleaning up part 2)

Former Member
0 Kudos

Hello All,

After having a support ticket open for 2 weeks and still not getting any type of response or feed back from SAP I am again coming to the community for some insight.

background:    Because of multiple issues with IDM resources not cleaning injesting users from our backend repositories and merging them with the acccounts that were loaded via HCM during go-live as well as jobs being set up incorrectly that caused failures that crashed the provisioning process we have a large number of users that are constantly failing to be assigned privs and business roles because their identities do not match what is not only in the IDSTORE but also the backend.

I have had a couple of discussions here about how to update missing privs in the database and though those have been successful on the database side they still have not resolve what is displayed in the web gui for the user and thus items still fail to provision.

I have a couple of types of issues:

1.  When I Role or Privalage is removed via the web admin it does not fully remove the associated privs on the users table associated with the mskey and thus does not remove the backend access.   Even though the process states completed items are still there.      This causes issue when we try to re-apply the acccess it fails as accounts or pivs are already there.

2.  backend access is not correct with whats in the users table and also whats listed in the user admin screen.   When you go to remove assocaited Business roles and readd to see if it will correct itself the business role shows completed but if you go to the change screen of the user the BR is there but none of the sub roles  are listed (failed, pending, ok...nothing is listed)

I have tried running the reconcile and repairentry stored procedures but so far they just run for hours, start up multiple Runtime tasks that dedlock each other and crash the dispatchers and then finally fail and don't fix the users.     (note the msdirty key table has been filling up and we have a nightly job that is sceduled to run but nothing happens and yes we have a housekeeping dispatcher set up)

We are now in a position where we have 100's of users in PRD that can't get access because of provisioning problems from the go-live and with SAP Support ignoring us I am not sure what else to do.

If the reconcile and repair jobs are not actually cleaning the entries from a user, is there anyway to remove all entries from a users table and have that reflect to the web admin screen.     Also, is there a change that can be made on the provisioning jobs that if an account already exists in the backend that it will not through the whole provisioning process into a failed state and just over write the account or just continue on with the rest of the tasks (this is our biggest problem by far).

I thought we had a solution in a previous topic for the users having an account in the ECC system but not listed in IDM failed (updated the ECC only and system only objects on the MSKEY) but even with that being updated for all users that had the issue, IDM still will not process fully when I perform a retry.

Not sure where to start but looking for some input from the community on what to provide from my end to help me work through this situation.   (please note I have no SQL and Java scripting background)

post screen shots next

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hello Michael,

for your questions, below is my reply. 🙂

If the reconcile and repair jobs are not actually cleaning the entries from a user, is there anyway to remove all entries from a users table and have that reflect to the web admin screen.

-> All required user/privilege/role entries can be removed from IDM table MXI_LINK .

-> Can you tell what status privileges/roles(which need to be removed) have for the users.

you can get the list using below query

select mcthismskeyvalue, mcothermskeyvalue, mcorphan, mcexecstate, mcassigneddirect, mclinkstate from idmv_link_ext where mcthismskeyvalue in ('<user1mskeyvalue>', '<user1mskeyvalue>',...)

you can narrow down list by putting more into where condition.

Normally entries with mcexectstate = 1/2/1026/4 and mcorphan = 0 can be removed via UI or custom job.

other mcexecstate (ex. 1536/1537/1025 etc.) can be removed after changing their status to 1026 and then removing it.

Also, is there a change that can be made on the provisioning jobs that if an account already exists in the backend that it will not through the whole provisioning process into a failed state and just over write the account or just continue on with the rest of the tasks (this is our biggest problem by far).

-> I think this you would mean for user creation provisioning job.

-> If so, then kindly check if createabapuser prov job does not have "changetype" attribute in its pass.

Regards,

Pradeep

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi, Michael

Did you resolve the issue? I had exactly the same problem when uIS_RepairEntry is not doing anything to the backend.

Thanks,

Jonathan.

Former Member
0 Kudos

No we have not gotten an answer on this.     SAP American is working on a fix but it has been over a month now with no response.

I ended up copying the old Java script back into our PRD system that did it manually and doesn't call the uIS_RepairEntry stored procedure.

I am still waiting on a solution.

Former Member
0 Kudos

here is a typical issue:

IDM Admin screen error:

error log of user job:

IDSTore showing it thinks all privs are assigned:

sometimes if I delete the user in the backend it works...sometimes it doesn't.  When it doesn't my everybody business role assigns OK but if you open it on the user it contains no associated privs (even though if you open the business role itself you see all privs)

RepairIdentify:  below is image of log on job, but I have also attached DSE file.    

For the clean dirty MSKey's job I don't know what to provide, but if I run it manually via the dispatcher tab it just starts multiple Runtime engines and runs for 12+ hours and either crashes or removes an MSKey entry..but when you do validate an entry that it did finally clean it still doesn't have the right assignments.

I have a couple of changes that I need to make to major business roles because of our ongoing ECC FI and MM, SD implementation and right now I cannot update anyones roles with out causing all users to get stuck inthe Dirty key table and never cleaned and provisioned the new access.

Former Member
0 Kudos

second exampl of IDM not removing access from users in WebAdmin:

Screen shot 1:   Log showing seccussful removal of Business Role:

Screen Shot 2:  view of user showing the original Business role did remove but all of the associated privs to that business role are still showing as being applied.  (please note that everybody BR and privs in red box should have been only items still assigned)

Screen shot 3:  final backend view of users UME acct with no access and last change by IDM back on 23rd.

Screenshot 4:   IDStore shows the users still retained all privs in backend even though BR removed

stored procedures "reconcile priv and repairEntry" do not seem to fix a user like this.     So for something like this is there a remove all job to clear out all assignments.

Former Member
0 Kudos

Since I don't have access to sourcecodes, changelogs or any other relevant information anymore this is strictly from memory and might be a bit off the mark. I hope some of it might be useful though.

Many issues here. The DSE log you attached shows version 7.2.8.0 on SQL Server, for reference to whoever else might want to chime in on this.

1) is the cookie does not match, indicating that jobs are running for so long without reporting back to the database that they are considered dead by the dispathcers and restarted. This requires the processing of a single entry to take more than 300 seconds if default values are used. When the job finally wakes up (See deadlock below) it tries to update its state and is rejected (cookie does not match) and is terminated. Usually happens when source tab statement is bad in jobs, or db locks (not deadlock) are blocking a query from completing.

2) User XYZ exists probably means that the entry exists in the backend but that IdM doesn't realize it because the master priv/account values are not set properly on the user. This causes the provisioning framework to go into the create user workflow where the connector then reports the already existing with the requested userid as an error. The only privilege is listed as failed, the privilige is listed as pending waiting for the master privilege to be assigned. Alternative is to verify/fix the masterpriv/account attribute values on the user OR add an On Error script on the create user pass that sets provision to OK if the error is user already exists (but that would be extremenly bad if you have an even minimal risk of duplicate userids as an existing user would be overwritten and "taken over")

3)

The logfile shows that you have a problem with deadlocks during the repair and 10 minute gaps between log-entries:


28.10.2014 11:21:33 :D:Ignoring changetype in com.sap.idm.ic.ToGeneric

28.10.2014 11:21:33 :D:Trying operation 3 on 121824

28.10.2014 11:21:33 :T:Executing script function z_repairIdentity(121824 - {MSKEY=121824})

28.10.2014 11:21:33 :S:Executing z_repairIdentity(121824 - {MSKEY=121824})

28.10.2014 11:31:19 :D:execute:{call mxi_repair_entry(121824,0,@PAR3 OUTPUT(INTEGER),@PAR4 OUTPUT(VARCHAR))} - got exception:  Error code 1205 SQL state:40001(!) - com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 101) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

28.10.2014 11:31:19 :D:execute:{call mxi_repair_entry(121824,0,@PAR3 OUTPUT(INTEGER),@PAR4 OUTPUT(VARCHAR))} - time used: 585957 ms

28.10.2014 11:31:19 :T:mc_write_syslog Exception in isRepairEntry - Exception:com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 101) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

28.10.2014 11:31:19 :D:execute:{call mc_write_syslog('Exception in isRepairEntry - Exception:com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 101) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.',NULL(String),2,'idmdispatcher7',1004684)} - time used: 5 ms

28.10.2014 11:31:19 :T:mc_job_iam_running 1004684,2014-10-28 11:21:32.977,0

This could be a problem in the procedure mxi_repair_entry or any of the procedures it calls, or they are simply blocked by something else in the system.

Use the queries from "2. Finding problems using database queries" in to find out what is running during those 10 minutes the repair process is running, or look at the built in database reports.

I'd also (ask SAP) to look at the releaselogs/changelogs to see if there's been any patches or changes to the reconciliation/repair functions after 7.2p8

Br,

Per "Not SAP" Krabsetsve

Former Member
0 Kudos

Thanks for your response Per.

For 1 and 3 they are both the same types of stored procedures that are timing out, and I am working with a DBA to run through the queries now and will provide him the document it links.    I do agree with you that it is running longer then it should and thus probably starting another query and another after that and running into the Deadlock issue.

We are going to see if there is something we can do outside of SAP to fix the procedure to run faster.    Either way though, I need to understand where is the TTL settings for these procedures kept at?   Is this the dispatcher time-out settings?    If it is then increasing this on the dispatcher could cause our other normal jobs to then wait excessively long. 

When the Reconcile and repair was a job at least it sat in the Provisioning queue and the dispatcher could keep track of it...not that these are stored procedures I think it is causing some confusion.

For item 2:    As you can see in the screen shot (and my comments) we have already added the missing priv to the users account in the ID Store per my initial provisioning discussion on this issue.   I had thought that would resolve the issue, but in fact IDM still errors out with the same error that the user already exists.    after that 2 things usually happen:

1.  I have to delete the user and then retry the BR and it works.

2.  I delete the user retry the BR, it says it works but doesn't.    Then if I try to remove everything it says it removes everything but if you look at the users MSKEY entry all the privs are still there and are even sometimes still listed in the Admin view even though the user doesn't have the Business Role that those privs are assigned to.      (see screen shots)

At this point I am looking for some insite on a job that I can create that would remove ALL privs from a users ID Store record (which I am hoping removes it from the web admin view) so that I can start from scratch.

I would like to hear more about the error script idea of yours, as we have a very minimal if non-existant chance of having duplicate ID's in our systems since we base our ID's off of the users Personnel number in HCM and those are always unique.

Former Member
0 Kudos

as a side note to this issue we currently have been trying to run the IS_repairEntry proceedure to clean some of this up and with no success.

the job I created for this is:

which calls the following script per SAP documentation:

// Main function: z_repairIdentity

function z_repairIdentity(Par){

//Example calling DSE internal function

//uStop("Terminated by user");

var usermskey=Par.get("MSKEY");

//var output=uIS_RepairEntry(163082);

var output=uIS_RepairEntry(usermskey);

uWarning(output);

return "";


}

The job runs and the store procedure triggers multiple runtimes that lock the database for hours, causes cookie errors and then fails.    No users are cleaned.