We are delighted to share our new EIC Delivery Methodology for efficiently managing Saviynt Implementations and delivering quick time to value. CLICK HERE.

D365 F&O Rest - Case sensitive account matching in ImportAccountEntJSON - acctEntParams

Paul_Meyer
Regular Contributor
Regular Contributor

For a D365 F&O integration, we've noticed that when using acctEntParams with processingType as "http", the account matching is case sensitive.

In the response from the SecurityUserRoles endpoint, the response entries has different case UserIds.

For example:

{
    "@odata.context": "https://hostname/data/$metadata#SecurityUserRoles",
    "@odata.count": 11,
    "value": [
        {
            "@odata.etag": "W/\"JzEsNTYzODA5ODc3OSc=\"",
            "UserId": "NSABA",
            "SecurityRoleIdentifier": "RTRFAVIEW",
            "AssignmentStatus": "Enabled",
            "AssignmentMode": "Manual",
            "SecurityRoleName": "RTR-FA_View",
            "UserLicenseType": "Enterprise"
        },
        {
            "@odata.etag": "W/\"JzIxMjU4ODEyNTgsNTYzODEwNjEzOCc=\"",
            "UserId": "NSaba",
            "SecurityRoleIdentifier": "SYSTEMUSER",
            "AssignmentStatus": "Enabled",
            "AssignmentMode": "Manual",
            "SecurityRoleName": "System user",
            "UserLicenseType": "Universal"
        },
...
}

The application user account response UserId is either in Mixed case ("NSaba") or all uppercase ("NSABA").

After access recon job, only the roles that case sensitive match on UserId is associated with the application account. The entries with all caps UserId's does not show as an entitlement on the user account in Saviynt.

The UserID attribute from the SystemUsers endpoint returns mixed case ("NSaba") type values for all accounts.

It is not clear yet why the D365 F&O SecurityUserRoles  endpoint has uppercase UserId values for some entries and we'll take it up with that application team.

From a Saviynt perspective, can the account matching from an access recon job be configured to be case insensitive?

 

8 REPLIES 8

Ishan
Saviynt Employee
Saviynt Employee

Hi @Paul_Meyer 

Ideally the user to account correlation should happen regardless as mysql is case insensitive but you can try using Advanced Config for User- Account correlation at Endpoint level with the following query:

UPPER(users.username)=UPPER(accounts.name) 

Ishan Kamat
Technical Architect, Professional Services
SaviyntLogo.png

sk
All-Star
All-Star

If I understand the issue correctly question is how to link entitlements to account without validating the case but not account to user.

If that is the correct understanding then try suggestion as per below thread. Not sure if that works for keyfields. But try to give it a try and use string function to convert the case 

https://forums.saviynt.com/t5/identity-governance/rest-string-functions/m-p/23721/emcs_t/S2h8ZW1haWx...

 

 


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

Paul_Meyer
Regular Contributor
Regular Contributor

@sk 

Your understanding is correct. Account correlation is based on the PartyNumber attribute that links the Worker and SystemUser entities.

I've tried with the keyfield, but that does not work.

I've tried using colsToPropsMap:

"acctEntParams": {
"entTypes": {
  "Roles": {
	"call": {
		"call1": {
			"connection": "userAuth",
			"callOrder": 0,
			"stageNumber": 0,
			"processingType": "http",
			"http": {
				"httpHeaders": {
					"Authorization": "${access_token}"
				},
				"url": "https://hostname/data/SecurityUserRoles?%24count=true",
				"httpContentType": "application/json",
				"httpMethod": "GET"
			},
			"listField": "value",
			"acctIdPath": "UserId",
			"entIdPath": "SecurityRoleIdentifier",
			"acctKeyField": "accountID",
			"entKeyField": "entitlementID",
			"colsToPropsMap": {
				"accountID": "#CONST#${String accID = response.UserId; String ret = accID.substring(0,2).toUpperCase() + accID.substring(2,accID.length()).toLowerCase(); return ret}~#~char"
			},
			"pagination": {
				"offset": {
					"offsetParam": "$skip",
					"batchParam": "$top",
					"batchSize": 10000,
					"totalCountPath": "completeResponseMap.@odata~dot#count"
				}
			}
		}
	}
			}

And get an error:

DEBUG rest.RestProvisioningService - INFO ErEx: processAccountEntitlementsCallByHttp:persistAccountEntObjects : Unknown column 'accountID' in 'field list'

Not sure if the statement "String accID = response.UserId;" is wrong. It is not clear if you can access the response and what the data structure looks like during the iterations.

Or whether I'm doing something wrong in the colsToPropsMap.

Any recommendations?

 

Don't add colsToPropsMap, I don't think it is supported in acctEntParams, Instead I am suggesting to  use CONST logic in acctKeyField and see it that works


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

Paul_Meyer
Regular Contributor
Regular Contributor

@sk

If I put anything other than a accounts db table column name such as "accountID" for example:

"acctKeyField": "#CONST#${String accID = accountID; String ret = accID.substring(0,2).toUpperCase() + accID.substring(2,accID.length()).toLowerCase(); return ret}~#~char",

It get interpreted as an accounts table column in the select statement.

It results in errors:

DEBUG rest.RestProvisioningService - Exception in processAccountEntitlementsCallByHttp:
org.springframework.orm.hibernate3.HibernateQueryException: unexpected char: '#' [select new Map(acct.#CONST#${String accID = accountID; String ret = accID.substring(0,2).toUpperCase() + accID.substring(2,accID.length()).toLowerCase(); return ret}~#~char as #CONST#${String accID = accountID; String ret = accID.substring(0,2).toUpperCase() + accID.substring(2,accID.length()).toLowerCase(); return ret}~#~char, acct.id as id) from com.saviynt.ecm.identitywarehouse.domain.Accounts acct where acct.endpointkey.id=99 and (acct.status = '1' or acct.status='Manually Provisioned' or acct.status='PROCESSING')]; nested exception is org.hibernate.QueryException: unexpected char: '#' [select new Map(acct.#CONST#${String accID = accountID; String ret = accID.substring(0,2).toUpperCase() + accID.substring(2,accID.length()).toLowerCase(); return ret}~#~char as #CONST#${String accID = accountID; String ret = accID.substring(0,2).toUpperCase() + accID.substring(2,accID.length()).toLowerCase(); return ret}~#~char, acct.id as id) from com.saviynt.ecm.identitywarehouse.domain.Accounts acct where acct.endpointkey.id=99 and (acct.status = '1' or acct.status='Manually Provisioned' or acct.status='PROCESSING')]
at

 

I've also tried switching to a httpAcctoEnt config instead, but also ran into an issue with the "${id}" parameter not working. Posted that issue in a separate thread.

 

 

 

 

rushikeshvartak
All-Star
All-Star

try toUppercase or toLowercase in api while mapping in json


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

@rushikeshvartak 

I'm not quite understanding your response. Could you please elaborate?

 

You have already changed case of account Name


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.