Incorrect column data loaded after initial load

[Former Member]

Hello,

I recently completed a move to SP9 and came across something I can't see to figure out.

I was validating user data loaded from an ECC6 repository after an initial load. In the browser I didn't see a value for MX_ADMIN_UNIT I was expecting. So I checked the database and found the MX_ADMIN_UNIT values loaded into the column called COMPANY_ID. This didn't make any sense so I checked the following:

*Notes

- this is a newly created identity store.

- I did run a Read Help Values job first.

Then

- I checked the attributes in this Identity Store and don't see MX_COMPANY_ID

- I checked the attribute MX_ADMIN_UNIT and it's properly setup to load values from the mxi_AttrValueHelp

- I checked the initial load pass ReadABAPUsers does have a source and target of Company_ID

- I checked the initial load pass WriteABAPUsers and it does use the MX_ADMIN_UNIT attribute with a value of $usergroups%

I don't understand how the user group values, through attribute MX_ADMIN_UNIT were populated into the CompanyID column. Are the columns dynamically created per repository? Does anyone know how I can remove/replace them? Can I just rename the column?

Thanks, Paul

[terovirta · Accepted solution]

Did the earlier SPs work differently? And with user groups you mean the user group in Logon Data-tab of SU01?

So you see the expected value in companyId field in the temp table but not in the mx_admin_unit-attribute of the user?  Do you see the expected user group value in the mxi_attValueHelp-table? Is setting the value then rejected due to missing value in the Value Help?

In "my" Sp9p1 the source for mx_admin_unit is the companyId column of the sap[REP]user table. In provisioning the mx_admin_unit has been mapped to the companyId field of plugins 1&2 and the values end up in the user group field of the SU01 Logon Data-tab.

The groups in User Groups tab of SU01 go to table sap[REP]group in the Initial Load and to the mx_user_category from there on..

regards, Tero

[Former Member · Accepted solution]

Hi Tero,

Thanks for the feedback, here are some answers to your questions:

- yes, SP8 correctly loaded a user's 'usergroup' information into the MX_ADMIN_UNIT attribute, and it displayed in the browser for each user.

*Note: it's an important attribute that I use for controlling provisioning authorization, so I need it to load correctly.

- correct, in the temporary table I see for each user the 'usergroup' that relates to them.

- I do see the expected user group values in the mxi_attValueHelp table. They are loaded for both MX_ADMIN_UNIT and MX_uSER_CATEGORY. So it's not rejected.

*In the 'Modify Identity' ordered task group, the attribute MX_ADMIN_UNIT is available, but MX_USER_CATEGORY is not. So although the value help data is loaded for MX_USER_CATEGORY it's not availble in this task.

While I want to understand how the usergroup data got loaded into CompanyID, I also want to know how to correct this. Right now, this is just a sandbox system I'm working with so I get delete the data, but I want to make sure the next initial load works correctly. So help is appreciated.

Cheers, Paul

[terovirta · Accepted solution]

Hi Paul,

how the data got into CompanyId-field is how the connector exposes it.

I am little bit puzzled as I thought the mapping has always been like this. Looked at my notes from 7.0-based config from 2009 and CompanyId was mapped to mx_admin_unit. The previous projects have used the user group to group/separate the users, like in your SU01 screen shot.

Do you have production or Q/A system that's on Sp8 where you can compare the mappings in the initial load? The names of the ABAP connector fields don't have 1:1 match with the IdM-attributes.

If you query for the user with SQL what does the sap[REP]user-table have for the user group in companyId? And if you query for the user from idmv_vallink_basic what value does the mx_admin_unit have?

If you need to change how the values are stored, simply remap the fields according to your spec.

In the Initial Load either:

a) change what field returned by the connector goes to which field in sap[REP]user-table in the pass that reads users from ABAP to the table

b) change what field from sap[REP]user-table goes to which attribute in the pass that creates/modifys users

regards, Tero

[Former Member · Accepted solution]

Hi Tero,

When I do a query in the sap[REP]user-table under the CompanyID field I do see the user group values as I would in SU01. I don't know where the table 'idmv_vallink_basic' is to query those results. Is this in the temporary DB?

Unfortunately I don't have SP8 in any other system right now. I'm only working with the sandbox.

So just to be clear, you're saying that the SU01 'usergroup' value should be mapped to the IDM CompanyID field?

I'll work on your suggestions for changes in the Initial Load and let you know how it works.

Thanks again for your help.

Paul

[terovirta · Accepted solution]

Paul Vipond wrote:

When I do a query in the sap[REP]user-table under the CompanyID field I do see the user group values as I would in SU01. I don't know where the table 'idmv_vallink_basic' is to query those results. Is this in the temporary DB?

idmv_vallink_basic is one of the basic views that exposes the IdM data to SQL queries. See this help link for more info: Using views to access identity store information

The query below would return the mx_admin_unit in IdM per the given logon id, if the ABAP logon id is the same as the mskeyvalue of the user:

select mcAttrName, mcSearchValue, mcModifyTime

from idmv_vallink_basic with (nolock)

where mcAttrName = 'MX_ADMIN_UNIT'

and MSKEY in (

   select MSKEY

   from idmv_vallink_basic with (nolock)

   where mcAttrName = 'MSKEYVALUE'

   and mcSearchValue = '[the logon id in question]'

)

regards, Tero

[Former Member · Accepted solution]

Thanks again Tero,

I've tried the query above but am getting an 'invalid object name' error. Have I missed something?

Thanks,

Paul

[Steffi_Warnecke · Accepted solution]

Hello Paul,

where are you trying to run the query?

Regards,

Steffi.

[Former Member · Accepted solution]

Hi Steffi,

I'm running this from within SQL Server. I ran it against the mxmc_db database. Right place?

Cheers, Paul

[Steffi_Warnecke · Accepted solution]

I'm working with a connection to both IdM databases (prod & test) from my SQL developer, so I don't know if you need to configure something else, if you start from the sql server.

Have you been using other SQL statements to IdM views yet, that worked? Like a query for idmv_vallink_basic? Or maybe the user and its permissions is the problem. Are you using the mxmc_rt-account?

[Former Member · Accepted solution]

Hi Steffi,

Thanks for the additional info. This is my first time working with SQL statements to IDM views.

I'll be away for the next two weeks so I'll have to pick this up when I return.

Cheers, Paul

[Former Member · Accepted solution]

Hi Tero,

I've continue to investigate why the MX_ADMIN_UNIT isn't populating in the manage tab for user.

I did manage to get the query to work. Here are a couple of points, screenshots and questions I still have on this. Perhaps something stands out.

*Notes:

- initial load added value help to data to load admin units

- validation showed this was loaded into COMPANYID column when checking the repository user table.

- validation query was taken from the repository user table

- mxmc_rt_u.sapR3D100user

[screenshot]

- yet, query with idmv_vallink_basic returns correct values

- this I think is as expected.

[screenshot]

- added MX_ADMIN_UNIT (user group in ABAP)column in manage tab to see if correct 'user group = MX_ADMIN_UNIT' value populate, but they don't.

[screenshot]

Questions:

- if the ABAP user group does map to CompanyId, then why doesn't a users user group value populate.

- if the query in the idmv_vallink_basic view returns expected results then why don't they populate in the browser display?

- what else can I do to trouble shoot?

Really appreciate your help.

Cheers, Paul

[terovirta · Accepted solution]

Hi Paul,

since the idmv_vallink_basic shows the user is in two Id Stores and the Ids of the stores are "2" and "4", do you have more than those two Id Stores in your system/MMC?

If you have more than two Id Stores make sure that the pass that writes the user in Initial Load points to the same Id Store that the UI is using. (either select the Id Store name from the drop down in the pass or maintain the SAP Master Id Store constant)

If you have only two Id Stores, can you take a screen shot of the attribute MX_ADMIN_UNIT definition and try also to make the attribute visible on the UI where you can edit the attribute (just to eliminate something in the attribute definition makes the date not visible in the search results, I am guessing here with the UI but have seen stranger stuff over the years and it takes only couple of mins to check).

regards, Tero

[Former Member · Accepted solution]

Hi Tero,

You're right about the multiple ID stores. I had a problem with a previous store, and since it was a sandbox I decided to create a new one. The ID store I'm using now is #3.

I did try re-running the pass with the current ID stores specifically chosen, but there was no change.

As you can see in the query from the previous ID stores, the user group/MX_ADMIN_UNIT did populate. I'm still puzzled as to why this attribute value is absent from the current ID store (#3).

Here are screenshots of the MX_ADMIN_UNIT.

I did make the attribute visible on the UI so that I could see which users had the attribute. Here's a screenshot of that.

I'm wondering if it's worth removing this repository and re-adding it, followed by another initial load. Make sense?

Cheers, Paul

[terovirta · Accepted solution]

Paul,

your initial load is not writing to the Id Store (#3) you're using. The user and the user group assignment only exists in Id Stores 2 and 4, that's why you don't see the values in UI.

Make sure that the initial load passes that write to Id Store have the correct Id Store selected, select it either from the drop down, or use the SAP_MASTER_IDS_ID global constant and make sure constant has value 3.

regards, Tero

[terovirta · Accepted solution]

Also take the value help away from the attribute when you run the Initial Load next and see if the user group values pop-up to the UI.

I guess the value help in Id Store #3 doesn't have the correct values and the update is rejected.

regards, Tero

[Former Member · Accepted solution]

Thanks again Tero.

I'm working through your comments and will let you know the results.

Paul