Please help on the following error, "Error during insert of sys_user"

[Rama Chandra D]

Hello,

I have just performed data import into an existing sys_user table using transform map. The transform is complete with errors. I have checked to log and here is what I have found,

"Error during insert of sys_user"

There is no error code mentioned in the log. Can someone help me understand why there has been an error during insert?

Regards,

Darshak

[jose_valle]

Hi Mittal,

If attempted to perform an insert instead of an update, then the coalesce must not have found the record. The u_objectguid --> u_ldap_uniqid should be easy enough to check, but have you looked at what value your script is returning for this particular record? For troubleshooting, might be a good Idea to add a gs.log() to your script to see what the value returned for your script is this case to confirm if it is what ou expected.

For troubleshooting, might be a good Idea to add a gs.log() statement to your script to see what the value is returned in this particular case to confirm if it is what ou expected.

Hope this helps!

[Mittal_M]

Hi Jose,

Thanks for reply. while troubleshooting I observed 2-3 different cases mentioned below.

Case 1. Different GUID for same userID.

For userID "ohmannb" it was earlier created on 2016-06-28 and last updated on 2016-08-19. when the user was created the Objectguid for that user was "s4ZIRM6A3UuHCtzVYoINoQ==" but yesterday when transform ran I see the new objectguid that is "HN5hyWqMbEy2HHkN5H/Rjg==", but the OU/source is same. Not sure what is the reason for different GUID.

There are not many such cases.

Case 2. GUID is same but Source/OU is changed.

For the userID "grooms" which was created on 2016-06-28 and last updated on 2016-06-29, but the ObjectGuid is the same i.e. "S8qJIphRb0+1gD8bSB76Qg==", but this time the OU/source is different. earlier Source/OU was "OU=VE,OU=DE,DC=xxxx,DC=xxxx,DC=org" and now the source/OU is "OU=ST,OU=DE,DC=xxxx,DC=xxxx,DC=org".

NOTE: This is the common case which I have observed for say 4-5 users.

Case 3. Same User_name with differences in User details and GUID.

For the userID "jaraz" which was created on 2016-06-28 and last updated on 2016-06-29 (it may happen that it was the last update and the user might be removed from AD), but yesterday when the transform ran I got an error for user_name i.e. "jaraz" but it was having the different ObjectGuid, to compare both the users record when I opened already existing user and the user from transform history I found that the "department, title, business phone, mobile phone" is the same as the existing user, the only difference was in "First name, Last name, Email, GUID".

both the record with user_name "jaraz" belongs to the same OU/Source.

This is only the one case.

I hope my understanding and observations are correct, Kindly share your views as well which will help me in case I am going wrong.

Thanks,

Mittal Modh.

[jose_valle]

Hi Mittal,

For Case#1, Is it possible that these users were recreated in AD?

Case#2 sounds like perhaps these are objects that were moved from one OU to another.

Case#3 sound like #1, but when it was recreated perhaps the information was not entered in exactly as it was before.

Ultimately, you need to review the fields that you use to coalesce to ensure that they will match existing users to update when appropriate.

Hope this helps!

[Mittal_M]

Hi Jose,

I checked with my AD team about Case 1 & 3, those users record were deleted and re-created. So from my side I have deleted those user records from SNOW and re-run the transform map, transform completed with no errors and the users are also created again in sys_user.

About Case 3, Could you please explain more in details as still I have one confusion that if the user is moved from one OU to another how does it make a difference, they still have the same GUID and also they belong to the same parent OU i.e. OU=DE. Its just the user is moved from "OU=VE,OU=DE" to "OU=ST,OU=DE".

I don't think there is any issue with the coalesce fields because as of now its running perfectly without any challenges, See below the transform map which is same in each OU transform map. Kindly let me know your views on this.

[Rama Chandra D]

Hey Jose,

My apologies for a delayed response. I was able to identify the issue and resolve it but I can not confidently say if it's correct or not. In my case, it so happened that, I have new records in which except the coalesce field value(user id), all the other fields are essentially the same. So, when an insert is being performed when no match to the coalesce field is found, SNOW in the background is checking for another field (Employee Number in my case) as a secondary coalesce value. So when it encounters a user with same employee number during insert, it is returning an error to avoid duplication(i assume).

Eg : I have a user whose details in the SNOW are UserID: xxxx Employee Number: 1234 and Username: Beth. If I run a transform map in which I have a record, UserID : yyyy,Employee Number: 1234 and Username: Beth and the coalesce is on UserID. SNOW tries to insert a new record because UserID 'yyyy' is not found however during insert it finds a match to Employee Number '1234' and then returns an error.

I hope I'm clear.

Darshak