Need help on one query in new DB application integration

[Rajesh_IAM]

Hi Everyone,

I am integrating one DB application with Saviynt, which contains some DAs (refer screen shots). Based on the selected values, I am pushing the same values to application through Create account JSON (insert query).

Group ID depend on Group Name

Country code depend on Country Name

Organization ID depend on Organization Name

However now application wants to remove dynamic attributes Group ID,Country Code and Organization ID and they want us based on selected values in Group Name, Country Name and 
Organization Name DAs, we need to push respective Group ID, Country code and Organization ID values directly into insert query in background (without displaying Group ID,Country Code and Organization ID filters to end users).
Insert Query:

{
"createaccountqry":["insert into [xxxxx].[xxxxxx] (ID,CountryCode,OrganizationName,OrganizationID,GroupName,GroupID,TagName,TagId) values ('${user.username}','${Country_Code}','${Org_Name}','${Org_ID}','${Group_Name}','${Group_ID}','${Tag_Name}','${Tag_Id}');"]
}

Ex: If user select "India" in country name filter then how can I push country code "IN" in this insert query without displaying country code filter for end-user.?

Is it possible to write select statement query inside in insert query to get required details like Group ID, Country Code and Organization ID values.?

Please review my issue/query and try to help to resolve this issue.

[Dhruv_S]

Hi @Rajesh_IAM 

When you create a DA named Group ID, do you select the Group ID value from the dropdown, or it get auto populated based on Group Name? If it is selected by user at the time of request, then it is not achievable as user won't be providing any input now.

If it gets auto populated, then it must be fetching that value based on the value of Group Name from DB using a query. Can you please share the query you are using for this attribute? 

If you want to use the inner query inside the insert query mentioned above, you need to check first if you can achieve the same in DB directly then only DB connector can handle that. 

Regards,

Dhruv Sharma

[Rajesh_IAM]

Hi @Dhruv_S  Thank you for your response.
Group ID value will auto populate based on Group name selection. Below is the query used in DA.
SELECT distinct ATTRIBUTE1 as ID FROM DATASET_VALUES WHERE DATASETNAME like 'TestLMSAdminGroup' and ATTRIBUTE2=${Group_Name}

If you want to use the inner query inside the insert query mentioned above, you need to check first if you can achieve the same in DB directly then only DB connector can handle that.   >> I tried inner query like select statement inside insert query, getting error. If you have any sample query syntax for this usecase, please share with me. I will test in test DB directly.

[rushikeshvartak]

Select Query are possible but it will do select on target application. Does mapping is stored in target database ?

If not then use if else logic in connection.

[Rajesh_IAM]

@rushikeshvartak Sorry, I did not understand your statement. can you please little bit elaborate it.?
Select Query are possible but it will do select on target application  >> Could you please share sample syntax with me.?

[rushikeshvartak]

Does mapping is stored in target database For Example India = IN ?

[Rajesh_IAM]

@rushikeshvartak  Yes. Country name and country code mapping is there in target database.

[rushikeshvartak]

That's great news 

{
"createaccountqry":["insert into [xxxxx].[xxxxxx] (ID,CountryCode,OrganizationName,OrganizationID,GroupName,GroupID,TagName,TagId) values ('${user.username}',(select CountryCode from contrymap where countrycode='${Country_Code}'),'${Org_Name}','${Org_ID}','${Group_Name}','${Group_ID}','${Tag_Name}','${Tag_Id}');"]
}

[Rajesh_IAM]

@rushikeshvartak  Thank you for sharing sample query syntax. I have tried this syntax with one country code value and its worked. However when I tried with more than one country code (as end user may select more than one country), SQL query is generating error message "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Below is the tried SQL query:
insert into [xxx].[xxxx] (ID,CountryCode,OrganizationName,OrganizationID,GroupName,GroupID,TagName,TagId) values ('${user.username}',(select CountryCode from [xxx].[xxxx] where CountryName IN '${Country_Name}'),'${Org_Name}',(select OrganizationID from [xxxx].[xxxxx] where OrganizationName IN '${Org_Name}'),'${Group_Name}',(select GroupID from [xxxx].[xxx] where GroupName IN '${Group_Name}'),'${Tag_Name}',(select TagId from [xxxx].[xxx] where TagName IN '${Tag_Name}'))

[rushikeshvartak]

insert into [xxx].[xxxx] (ID,CountryCode,OrganizationName,OrganizationID,GroupName,GroupID,TagName,TagId) values ('${user.username}',(select CountryCode from [xxx].[xxxx] where CountryName IN ('${Country_Name}'),'${Org_Name}'),(select OrganizationID from [xxxx].[xxxxx] where OrganizationName IN '${Org_Name}'),'${Group_Name}',(select GroupID from [xxxx].[xxx] where GroupName IN '${Group_Name}'),'${Tag_Name}',(select TagId from [xxxx].[xxx] where TagName IN '${Tag_Name}'))

[Rajesh_IAM]

@rushikeshvartak  I have tried with above mentioned syntax, but still getting error.
Below is the sample task details and error message:

In the request, I have slected 2 countries as below:

Below is Create Account JSON:
{
"createaccountqry":["insert into [xxx].[xxx] (HEID,CountryCode,OrganizationName,OrganizationID,GroupName,GroupID,TagName,TagId) values ('${user.username}',(select CountryCode from [xxx].[xxx] where CountryName IN '${Country_Name}'),'${Org_Name}',(select OrganizationID from [xxx].[xxxx] where OrganizationName IN '${Org_Name}'),'${Group_Name}',(select GroupID from [xxx].[CustomAttributeAdminGroup] where GroupName IN '${Group_Name}'),'${Tag_Name}',(select TagId from [xxx].[xxxx] where TagName IN '${Tag_Name}'));"]
}

[rushikeshvartak]

{
"createaccountqry":["insert into [xxx].[xxx] (HEID,CountryCode,OrganizationName,OrganizationID,GroupName,GroupID,TagName,TagId) values ('${user.username}',(select CountryCode from [xxx].[xxx] where CountryName IN '${Country_Name.replaceAll(",", "','")}'),'${Org_Name}',(select OrganizationID from [xxx].[xxxx] where OrganizationName IN '${Org_Name}'),'${Group_Name}',(select GroupID from [xxx].[CustomAttributeAdminGroup] where GroupName IN '${Group_Name}'),'${Tag_Name}',(select TagId from [xxx].[xxxx] where TagName IN '${Tag_Name}'));"]
}

[Rajesh_IAM]

Hi @rushikeshvartak  I have tried with above mentioned JSON , but its showing error in JSON format.

JSON:
{
"createaccountqry":["insert into [SVNT_LMS].[UserCustomAccess] (HEID,CountryCode,OrganizationName,OrganizationID,GroupName,GroupID,TagName,TagId) values ('${user.username}',(select CountryCode from [SVNT_LMS].[CustomAttributeGeography] where CountryName IN '${Country_Name.replaceAll(",","','")}'),'${Org_Name}',(select OrganizationID from [SVNT_LMS].[CustomAttributeOrganization] where OrganizationName IN '${Org_Name.replaceAll(",","','")}'),'${Group_Name}',(select GroupID from [SVNT_LMS].[CustomAttributeAdminGroup] where GroupName IN '${Group_Name.replaceAll(",","','")}'),'${Tag_Name}',(select TagId from [SVNT_LMS].[CustomAttributeTag] where TagName IN '${Tag_Name.replaceAll(",","','")}'));"]
}

Then I have updated double quotes with single quotes and tried below JSON. This is there is no error in JSON, but getting error while executing tasks.
Error: SAV - Error while creating account - 22253183-No signature of method: java.lang.String.replace() is applicable for argument types: (java.lang.String, java.lang.String, java.lang.String) values: [,, , ] Possible solutions: replace(char, char), replace(java.lang.CharSequence, java.lang.CharSequence), replaceAll(java.lang.String, java.lang.String), reverse(), reverse(), replaceAll(java.util.regex.Pattern, java.lang.String)
JSON:
{
"createaccountqry":["insert into [SVNT_LMS].[UserCustomAccess] (HEID,CountryCode,OrganizationName,OrganizationID,GroupName,GroupID,TagName,TagId) values ('${user.username}',(select CountryCode from [SVNT_LMS].[CustomAttributeGeography] where CountryName IN '${Country_Name.replaceAll(',','','')}'),'${Org_Name}',(select OrganizationID from [SVNT_LMS].[CustomAttributeOrganization] where OrganizationName IN '${Org_Name.replaceAll(',', '','')}'),'${Group_Name}',(select GroupID from [SVNT_LMS].[CustomAttributeAdminGroup] where GroupName IN '${Group_Name.replaceAll(',', '','')}'),'${Tag_Name}',(select TagId from [SVNT_LMS].[CustomAttributeTag] where TagName IN '${Tag_Name.replaceAll(',', '','')}'));"]
}

[rushikeshvartak]

{
"createaccountqry": [
"insert into [SVNT_LMS].[UserCustomAccess] (HEID,CountryCode,OrganizationName,OrganizationID,GroupName,GroupID,TagName,TagId) values ('${user.username}',(select CountryCode from [SVNT_LMS].[CustomAttributeGeography] where CountryName IN '${Country_Name.replaceAll(\",\",\"''\")}'),'${Org_Name}',(select OrganizationID from [SVNT_LMS].[CustomAttributeOrganization] where OrganizationName IN '${Org_Name.replaceAll(\",\",\"''\")}'),'${Group_Name}',(select GroupID from [SVNT_LMS].[CustomAttributeAdminGroup] where GroupName IN '${Group_Name.replaceAll(\",\",\"''\")}'),'${Tag_Name}',(select TagId from [SVNT_LMS].[CustomAttributeTag] where TagName IN '${Tag_Name.replaceAll(\",\",\"''\")}'));"
]
}

[Rajesh_IAM]

Hi @rushikeshvartak  Thank you for the update.  I have tried with above JOSN, but still am getting error and tasks are failing. Below are the error logs.

Start createAccountDB

Connection is LMS Permission Management

Start getConnection

Fetch the connection from security system

Connection is LMS Permission Management

url before binding .. jdbc:sqlserver://xxxxxxt:xxxx;databaseName=xxxxxxxx

calling templateUtilityService.getTemplateString for binding

Connection created...:

End getConnection

got SQL Connection

Create account JSON String is {

createaccountqry": ["

insert into [SVNT_LMS].[UserCustomAccess] (HEID

]

}

Error occured in creating account

groovy.lang.GroovyRuntimeException: Failed to parse template script (your template may contain an error or be trying to use expressions not currently supported): startup failed:

SimpleTemplateScript79692.groovy: 3: unexpected char: '\' @ line 3, column 266.

IN '${Country_Name.replaceAll(\

^

1 error

at com.saviynt.utility.TemplateUtilityService$_addTemplateCheck_closure2.doCall(TemplateUtilityService.groovy:263)

at com.saviynt.provisoning.DBProvisioningService.createAccount(DBProvisioningService.groovy:331)

at com.saviynt.ecm.services.ArsTaskService.createAccountTarget(ArsTaskService.groovy:11805)

at com.saviynt.ecm.services.ArsTaskHelperService$_whenTaskTypeIsThreeNewAccountAccess_closure50.doCall(ArsTaskHelperService.groovy:3079)

at com.saviynt.ecm.services.ArsTaskHelperService.whenTaskTypeIsThreeNewAccountAccess(ArsTaskHelperService.groovy:3070)

at com.saviynt.ecm.services.ArsTaskHelperService$_completeAutoProvTasksUpgraded_closure1.doCall(ArsTaskHelperService.groovy:175)

at com.saviynt.ecm.services.ArsTaskHelperService.completeAutoProvTasksUpgraded(ArsTaskHelperService.groovy:160)

at MultipleProvisioningJob.execute(MultipleProvisioningJob.groovy:222)

at org.quartz.core.JobRunShell.run(JobRunShell.java:199)

at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:546)

End createAccountDB Here Country_Name is DA name and we are passing DA value selected by end user

[rushikeshvartak]

checking

[Rajesh_IAM]

Hi @rushikeshvartak  Any update.?

[rushikeshvartak]

{
"createaccountqry": [
"insert into [SVNT_LMS].[UserCustomAccess] (HEID,CountryCode,OrganizationName,OrganizationID,GroupName,GroupID,TagName,TagId) values ('${user.username}',(select CountryCode from [SVNT_LMS].[CustomAttributeGeography] where CountryName IN '${Country_Name.replaceAll(',',"','")}'),'${Org_Name}',(select OrganizationID from [SVNT_LMS].[CustomAttributeOrganization] where OrganizationName IN '${Org_Name.replaceAll(',',"','")}'),'${Group_Name}',(select GroupID from [SVNT_LMS].[CustomAttributeAdminGroup] where GroupName IN '${Group_Name.replaceAll(',',"','")}'),'${Tag_Name}',(select TagId from [SVNT_LMS].[CustomAttributeTag] where TagName IN '${Tag_Name.replaceAll(',',"','")}'));"
]
}

[Rajesh_IAM]

Hi @rushikeshvartak  Thank you for the response.
I have tried above mentioned JSON, still getting error.
Attached error logs details.

[rushikeshvartak]

"Error in create account - com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'United States'. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515) at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:792) at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:689) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180) at

[Rajesh_IAM]

Hi @rushikeshvartak  When I tried with above mentioned am getting same syntax error only. 

[vm]

@Rajesh_IAM Please try the below script and check if it's working or not-

{
	"createaccountqry": [
		"insert into [SVNT_LMS].[UserCustomAccess] (HEID,CountryCode,OrganizationName,OrganizationID,GroupName,GroupID,TagName,TagId) values ('${user.username}',(select CountryCode from [SVNT_LMS].[CustomAttributeGeography] where CountryName IN ('${Country_Name.replaceAll(",","','")}')),'${Org_Name}',(select OrganizationID from [SVNT_LMS].[CustomAttributeOrganization] where OrganizationName IN ('${Org_Name.replaceAll(",","','")}')),'${Group_Name}',(select GroupID from [SVNT_LMS].[CustomAttributeAdminGroup] where GroupName IN ('${Group_Name.replaceAll(",","','")}')),'${Tag_Name}',(select TagId from [SVNT_LMS].[CustomAttributeTag] where TagName IN ('${Tag_Name.replaceAll(",","','")}')));"
	]
}

[Rajesh_IAM]

Hi @vm  Thank you for the update. I have tried with above mentioned JSON, but its giving error message.
Error: SAV - Error while creating account - 22253183- Exception occured in Create Account Query - Cannot insert the value NULL into column 'TagId', table 'xxxxxxxxx_LMS.UserCustomAccess'; column does not allow nulls. INSERT fails.
Attached error logs.

[vm]

@RajeshA This means the last query - 

select TagId from [SVNT_LMS].[CustomAttributeTag] where TagName IN ('${Tag_Name.replaceAll(",","','")}')

is not returning any value and this table has a not null constraint on the column - TagId.
Please check in your target database that whatever Tag_Name you are passing from Saviynt, has a corresponding TagId. Also, double check if the Tag_Name does not have any leading or trailing whitespaces.

[Rajesh_IAM]

Hi @vm  Thank for the response. 
Is it possible to add if - else condition in create account JSON like if access type - group the we will exclude TagId column in insert statement and if access type is Tags the we will include TagId column in insert statement .? Because in some cases end users no need to select TagName, in this case it giving null value in insert statement.

[vm]

@Rajesh_IAM what is the exact condition where we do not want to send TagId column?
Also, as what should be the default value of the the TagId column in the table - UserCustomAccess?
Is it the case that if do not send TagId as part of insert statement, some default value will be populated in the target table? I am asking this because the SQL error clearly suggests that TadId cannot be null. 

[Rajesh_IAM]

Hi @vm  If end user not select any value for Tag Name then we need to insert default value "All" into TagId columns. In case user select any value for Tag Name then we need push respective TagId value. 

[rushikeshvartak]

{
"createaccountqry": [
"insert into [SVNT_LMS].[UserCustomAccess] (HEID, CountryCode, OrganizationName, OrganizationID, GroupName, GroupID, TagName, TagId) values ('${user.username}', (select CountryCode from [SVNT_LMS].[CustomAttributeGeography] where CountryName IN ('${Country_Name.replaceAll(\",\",\"','\")}')), '${Org_Name}', (select OrganizationID from [SVNT_LMS].[CustomAttributeOrganization] where OrganizationName IN ('${Org_Name.replaceAll(\",\",\"','\")}')), '${Group_Name}', (select GroupID from [SVNT_LMS].[CustomAttributeAdminGroup] where GroupName IN ('${Group_Name.replaceAll(\",\",\"','\")}')), '${Tag_Name}'," +
" case when '${Tag_Name}' = '' then (select TagId from [SVNT_LMS].[CustomAttributeTag] where TagName = 'All') else (select TagId from [SVNT_LMS].[CustomAttributeTag] where TagName = '${Tag_Name}') end);"
]
}

[Rajesh_IAM]

Hi @rushikeshvartak  I have tried with above mentioned JSON, still getting error while executing tasks.

createaccountqry": ["

insert into [SVNT_LMS].[UserCustomAccess] (HEID

case when '${Tag_Name}' = '' then (select TagId from [SVNT_LMS].[CustomAttributeTag] where TagName = 'All') else (select TagId from [SVNT_LMS].[CustomAttributeTag] where TagName = '${Tag_Name}') end);""

]

}

Error occured in creating account

groovy.lang.GroovyRuntimeException: Failed to parse template script (your template may contain an error or be trying to use expressions not currently supported): startup failed:

SimpleTemplateScript318950.groovy: 3: unexpected char: '\' @ line 3, column 275.

N ('${Country_Name.replaceAll(\

Attached full error logs for review.

[Rajesh_IAM]

Hi @vm  I have with below Create Account JSON along with all the required values (menas no null values) and could see only one value inserted into CountryCode, OrganizationID even I have selected multiple values.
JSON:
{
"createaccountqry": [
"insert into [SVNT_LMS].[UserCustomAccess] (HEID,CountryCode,OrganizationName,OrganizationID,GroupName,GroupID,TagName,TagId) values ('${user.username}',(select CountryCode from [SVNT_LMS].[CustomAttributeGeography] where CountryName IN ('${Country_Name.replaceAll(",","','")}')),'${Org_Name}',(select OrganizationID from [SVNT_LMS].[CustomAttributeOrganization] where OrganizationName IN ('${Org_Name.replaceAll(",","','")}')),'${Group_Name}',(select GroupID from [SVNT_LMS].[CustomAttributeAdminGroup] where GroupName IN ('${Group_Name.replaceAll(",","','")}')),'${Tag_Name}',(select TagId from [SVNT_LMS].[CustomAttributeTag] where TagName IN ('${Tag_Name.replaceAll(",","','")}')));"
]
}
DB Screen Shot:

ARS Page Screen shot:

[This message has been edited by moderator to mask sensitive information]

[rushikeshvartak]

Why don’t you go with entitlement type instead of dynamic attributes 

[Rajesh_IAM]

@rushikeshvartak  This DB application does not have Groups/Entitlements and they want only account management (create account and update account) through Saviynt. They need some filters for end users while raising request in ARS. So I have created dataset with all the required filters values and created dynamic attributes in endpoint, calling dataset attributes in these dynamic attributes.

[rushikeshvartak]

I will propose to go with entitlements it will be easy and clean solution 

[Rajesh_IAM]

@rushikeshvartak  I agree with you.  I have created 4 datasets as application team requested filters are in four tables. Here how can I manage 4 tables with single entitlement type.?
Here are the 4 tables.

And below are the 4 datasets I have configured in Saviynt.

And below is the table, where we need to insert data.