and more in a single search tool across platforms. Read the announcement here. |
03/19/2024 04:22 AM
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.
03/21/2024 06:35 AM
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
03/21/2024 06:44 AM
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.
03/21/2024 07:41 PM
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.
03/22/2024 06:12 AM
@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.?
03/24/2024 07:53 PM
Does mapping is stored in target database For Example India = IN ?
03/24/2024 10:58 PM - edited 03/24/2024 10:59 PM
03/25/2024 08:05 PM
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}');"]
}
03/26/2024 03:20 AM
@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}'))
03/26/2024 08:59 PM
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}'))
03/27/2024 03:16 AM - edited 03/27/2024 03:29 AM
@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}'));"]
}
03/27/2024 08:16 PM
{
"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}'));"]
}
03/28/2024 01:36 AM
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(',', '','')}'));"]
}
03/28/2024 05:48 AM
{
"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(\",\",\"''\")}'));"
]
}
03/28/2024 07:05 AM - edited 03/28/2024 07:06 AM
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 |
03/28/2024 07:08 AM - edited 03/28/2024 07:08 AM
checking
03/29/2024 05:14 AM
Hi @rushikeshvartak Any update.?
04/01/2024 11:23 AM
{
"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(',',"','")}'));"
]
}
04/04/2024 06:40 AM
Hi @rushikeshvartak Thank you for the response.
I have tried above mentioned JSON, still getting error.
Attached error logs details.
04/04/2024 11:49 AM
"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
04/04/2024 10:50 PM
Hi @rushikeshvartak When I tried with above mentioned am getting same syntax error only.
04/05/2024 02:58 AM
@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(",","','")}')));"
]
}
04/05/2024 04:09 AM
Hi @vivek9237 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.
04/05/2024 04:21 AM
@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.
04/07/2024 10:42 PM
Hi @vivek9237 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.
04/07/2024 11:16 PM
@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.
04/08/2024 01:14 AM
Hi @vivek9237 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.
04/08/2024 08:11 PM
{
"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);"
]
}
04/09/2024 02:02 AM - edited 04/09/2024 02:24 AM
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.
04/09/2024 02:22 AM - last edited on 04/09/2024 04:37 AM by Sunil
Hi @vivek9237 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]
04/09/2024 07:24 PM
Why don’t you go with entitlement type instead of dynamic attributes
04/09/2024 08:46 PM
@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.
04/09/2024 08:48 PM
I will propose to go with entitlements it will be easy and clean solution
04/09/2024 08:52 PM - edited 04/09/2024 09:53 PM
@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.