Provisioning Queue: How to clear specific entries?

[jon_pryor]

We have several entries in the provisioning queue where we are trying to understand why they are not being processed or cleared from the queue successfully for specific mskey #'s.  I have read Steffi's blog post SAP IDM: Provisioning is stuck (yet again) - My checklist.  In particular, we executed step 3 'Check for waiting tasks via the SQL developer' with no success.

I'll try to explain the scenario here.  I want to get a 2nd opinion to see if this would negatively impact the mskey #/system that I'm not aware of before executing in production?  Or, are there other areas to investigate that I'm missing? We are on IdM 7.2 SP8 (one of the earlier patch levels) with DB2.

The entry is active within IdM (MX_INACTIVE = NULL). Here's the provisioning queue for the specific MSKEY # that I changed (i blocked out the MSKEYVALUE from MSG):

This has been in the provisioning queue for several months with nothing being done.  Here's a screenshot of the provisioning status of the auditid that it's waiting for (1100 = OK):

I continue to look at the REFAUDIT to see if I can see anything there.

The auditref has a status of 20 (Pending), which is the parent ordered task that started the chain of events.  However, not all of the tasks/jobs have finished as one of the tasks has a status of 5 (Waiting).  For some reason, the entry in the provisioning queue with a state of 24 is not finishing to kick off the task that has a status of 5.

At this point, I tried a few things since I'm working in a non production system.  This is where I landed by executing the following SQL statement.

update mxp_provision set state = '2' where mskey = 2282651 and actionid = 1003557

This updated the state from 24 (Wait for event task status) to 2 (Ready to Run).  This cleared the provisioning queue for the mskey #.  Looking at table MXP_AUDIT PROVSTATUS changed from 20 to 1101 for the parent ordered task.

MSG: Error in expanding linked tasks (3): -803:SQL0803N  One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint  Task:1003557

This accomplished my goal of cleaning up the provisioning queue for that specific MSKEY #. The attributes for the MSKEY # did not change (which was what I wanted as well).  And, nothing showed up in the system/dispatcher/job logs.

In addition, I tried variety of other direct database updates to mxp_provision, but didn't go as far as deleting anything.  Example, I changed the sate to 21 (Expanded OK), whic didn't do anything.

[former_member2987 · Accepted solution]

Hi JP,

You never seem to ask the easy questions, do you?

Removing entries from the provisioning queue is always a tricky issue.  As you know, best practices dictate that we should only remove items from the provisioning queue in Production when directed to do so by SAP.  So removing entries is always done at your own risk. That's not to say it's never done, however.

From what you're saying, there's a chain of events that has only partially happened, which is why there are these old entries in the queue.  Personally, I'd want to look at a few things that could be keeping them open.

1. Has the action been done manually either via IDM or back end direct assignment? This could easily be a reason it's hung up.

2. Are all of the systems up and connected? Could they have been bounced and timed out?

3. Can you re run the task?  Does it work then?

Now that we've thought about this and why it might have happened, I would start thinking about what do do about the provisioning queue itself. Before taking any corrective action, make sure that you have a good set of Identity Store backups.  (I know you know this, but I have to include it) I would also manually export the entry and assignments (you could do a quick sql query off of idmv_value_ext and and idmv_link_ext, and possible from the base tables as well)

My preference would be to remove the entry from the queue.  I do not believe that any additional actions will take place, but then again as I have said many times, my crystal ball is cracked, so I don't always see the future that clearly through it.  After you have done this, check the queue and see what's going on, then check the entry again, either through the IDM Web UI or the queries mentioned in the paragraph above. Then you will need to take whatever corrective actions are required.

I know you would probably want to know why things went the way they did, but I'm guessing you never will, so playing with additional testing and "what if" scenarios seems to be a waste of time to be, but your mileage may vary.  I also don't know what kind of requirements your organization might have on this as well.

I also don't think that changing the provisioning queue state for the entry will help either.  Actually, I think it will probably do more harm than good.  It's that kind of altering of the database that causes issues in the first place, and will likely cause many issues when talking to SAP Support.

Hope this helps somewhat.

Matt

PS -- When doing this, I would also recommend shutting down the dispatchers so that you don't inadvertently remove any new requests that are coming in.

[jon_pryor · Accepted solution]

Thanks for the quick reply.  You bring up some good points.

For this scenario, we "think" we know what caused it and how to prevent it from happening in the future.  We re-ran the task/jobs and the execution of the jobs a 2nd time runs successfully.   However, this does not remove the historical logs from the provisioning queue.

I like the idea of stopping the dispatchers, taking a copy of the database, and then deleting the records.

Or, we may clean the provisioning queue completely out as part of our support stack installation in a few months.  There's about 1,500 entries in the provisoining queue that never go away and have been sitting there for several months.