How to select status of Provisioning from tables?

[Former Member]

Hello Guru!

Please help me to understand how can I select from tables information about status of Provisioning?

Example,

I assigned role to user and see that it's in 'Pending' status now:

I want to find the reason of this from tables of IDM. And execute the following request:

select * from idmv_linkaudit_basic where usermskey in (select mcmskey from idmv_entry_simple where mcmskeyvalue='TEST')

assignmentmskey='169856' order by auditdate desc

Then I check table

select * from mxp_audit where auditid ='348048'

and see that status of Audit is 1100 = "Ok".

What I did wrong and how to select status and reason of "Pending" from tables?

Best regards,

Natalia.

[terovirta · Accepted solution]

If you select mcExecStateHierarchy from link tables what do you see then for the assignment?

My notes show me that the value is 512 for "pending add" and 1536 "pending remove". (I don't have access to system where I could verify these.)

Hopefully either of the Chris from Ireland or Chris from Norway will let us know if these are reliable values.

[Former Member · Accepted solution]

Hello Tera Virta,

thanks for answer!

In  mcExecStateHierarchy I have value 512 "Pending add".

select mcexecstatehierarchy from idmv_link_ext2 where mcthismskey='16762' and mcothermskey='16680'

And to met my goal could you please advice where can I get information about why this assignment is in Pending state?

Best regards,

Natalia.

[terovirta · Accepted solution]

Is the user missing Account Privilege to that repository? What value does the column

mcAssignedMasterPrivilege have?

I think you need to a bit of "CSI-stuff" to figure out the failures in IdM, I don't think there is an easy way, like one single table/db view, to see what's it's pending for.

regards, Tero

[Former Member · Accepted solution]

We've tried to add some CSI stuff in SP8 and later releases. Currently there's the mc_analyze_assignments & mc_repair_assignments procedures that will look for stuck assignments and try to find out why they're stuck and how to get them going again. We also have a package that we can send to customers with problems with pre-sp8 versions. Currently usability is a  challenge because the UI on top of it has continuosly been pushed back by other requests with higher priority. So go forth and feature request 🙂

Krishna Kumar Duddu did a nice writeup on his experience with it:

Anyway, to the original question on how to find this manually... It would probably take a blogpost or two to go through all the links and values that the analyze assignment procedure does. But a quick check on the following columns should give you a decent overview of what the entry has:

select

  mcThisMskey,mcThisMSKEYVALUE,mcOtherMSKEY,mcOtherMSKEYVALUE,mcLinkState,

  mcAssignedDirect,mcAssignedInheritCount,

  --mcMasterPrivMSKEY,mcMasterPrivLinkId,mcNoMasterAudit,

  mcExecState,mcExecStateHierarchy,

  mcAddAudit,mcModifyAudit,mcDelAudit,mcGroupGUID

from idmv_link_ext where mcThisMskey = 23

The columns in bold are new in sp9 and makes resolving master privilege states much easier.

There is some logic in the procedures around the add/del/mod audits where it checks which is the highest or lowest number to find out if the the add/mod is done before a delete or the other way around, but thats usually not important to you. The highest auditid is the latest and the one that usually will tell you the current state. Once you have it you can look it up in mxp_audit, mxp_ext_audit & the link audit.

Br,

Per "Norway Chris" Krabsetsve

[former_member2987 · Accepted solution]

Good info Chris! I'll be putting that query into my "snippets" file.

I wonder if we could start a Query repository here on SCN?

Anyone interested?

Matt

[Steffi_Warnecke · Accepted solution]

HERE! ME! ^^ I'm also collecting all sorts of snippets. But wouldn't the Wiki be a better place for such a collection?

[former_member2987 · Accepted solution]

I don't know Steffi.  Sounds logical.  I'm sorry to say I haven't done much with the Wiki...

[Steffi_Warnecke · Accepted solution]

Maybe this could be a way to brush the dust off of it. *g*

[Steffi_Warnecke]

Hello Natalia,

I see you marked your thread as "Assumed answered". Could you please provide the solution for your question, so that other can find it, too?

Regards,

Steffi.

[Former Member]

Steffi,

I don't know why my thread marked as "Assumed answered" , I didn't do it.

Unfortunatelly I have no answer for my question yet.

Best regards,

Natalia.

[Steffi_Warnecke]

Ah, very confusing. ^^ Maybe one of the SCN team like or can de-select that again, so that it's visible as open again and will attract the IDM crowd to help.

I had a problem with pending provisioning some time ago and got the following SQL-statements from the support to search for and deal with them:

select * from mxp_provision where msg like 'Wait for%'

>> This looks for everything, that is waiting for another task to finish successfull. In the MSG-column you can see which one and get an auditid.

The ID you use in the following statement to look for the problem in the hindering task:

Select * From Mxp_Audit Where Auditid= <auditid>

>>Maybe it's failed or something is stopping it's execution.

I also got this statement:

update mxp_audit set provstatus=1100 where auditid = <auditid>

>> So I could set the hindering task (after checking it) on OK, so that the waiting task can finally be executed. This way I worked my way through the different waiting processes.

Maybe this helps you, too, on your way through this. If there are easier ways, I'm also eager to learn them. ^^

Regards,

Steffi.

[ChrisPS]

Hi Natalia,

               as moderator I tried reverting the assumed answered status but was unable. I'll lock this thread and could you open a new thread with the issue so that maye you can get a solution :-).

Thanks

Chris

SCN IdM Moderator

[JasonLax]

I hope I'm not too late but I've reset this thread.

The trick is to mark one of the replies as correct (while it's still unlocked) and then un-mark that reply as correct and voila: the thread is reset.

[Former Member]

Hello Steffi,

I tried to execute your query "select * from mxp_provision where msg like 'Wait for%'", but I 've got null result. There is 0 entry in this table.

I know that I have 'Pending' entry, I see it via web-interface. But in mxp_audit I have mxp_audit=1100, looks like task is finished...

In this case I understand that reason of Pending status is no Master role assigned to user (PRIV_<SYSTEM>_ONLY), so IDM is waiting for this privilege assignment.

But my dream is to be able understand it via Tables.

Besr regards,

Natalia.

[Former Member]

Jason,

Thanks!

[Steffi_Warnecke]

Hello Natalia,

But my dream is to be able understand it via Tables.

that's a pretty big dream.

Personally I find the IdM-tables not very accessible when it comes to understanding the different columns and values dealing with status of provisioning etc. To many numbers and not enough letters for my taste. *g*

Regards,

Steffi.