Preprocessor query to check entitlement assignment

[HarishG]

Hi,

We have a usecase that we need to check if the user has a specific entitlement or not. If it exist, then the user should not be updated.

We configured accountName rule as : user.username=account.name

So we are trying to get the account names(same as usernames) from that particular endpoint, we need to avoid updating that user.

I have tried using the delete statement to avoid those users, but it didnt work.

{
"ADDITIONALTABLES": {
"USERS": "SELECT userkey, username from users"
},
"COMPUTEDCOLUMNS": [
"customproperty60"

],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET customproperty60= 'test'",
"DELETE NU FROM NEWUSERDATA NU WHERE NU.USERNAME in (SELECT acc.name from Accounts acc,Account_entitlements1 ac1 where acc.id=ac1.id and ac1.entitlement_valuekey=139233)"
]
}

Attached the logs for reference.

any suggestions achieving this usecase?

Thanks in advance

Best regards

Harish

[rushikeshvartak]

Refer https://forums.saviynt.com/t5/application-access-governance/error-in-users-import-error-while-proces...

[Raghu]

@HarishG  try below

{
"ADDITIONALTABLES": {
"USERS": "SELECT userkey, username FROM users"
},
"COMPUTEDCOLUMNS": [
"customproperty60"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA NU SET customproperty60 = 'test' WHERE NOT EXISTS ( SELECT 1 FROM Accounts acc JOIN Account_entitlements1 ac1 ON acc.id = ac1.id
WHERE acc.name = NU.USERNAME AND ac1.entitlement_valuekey = '139233')"
]
}

[HarishG]

Thanks @Raghu and @rushikeshvartak ,

i have tried the above query, but still getting the same error.

error:

2024-07-25T09:12:45+02:00-ecm--null-vq6s6--java.sql.SQLSyntaxErrorException: Table 'ssminlp.accounts' doesn't exist at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1335) at com.mysql.cj.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2108) at com.mysql.cj.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1245) at com.saviynt.ecm.services.ImportSAvDataUserService$_doImportDataPreprocessing_closure10.doCall(ImportSAvDataUserService.groovy:416) at com.saviynt.ecm.services.ImportSAvDataUserService.doImportDataPreprocessing(ImportSAvDataUserService.groovy:405) at com.saviynt.ecm.services.ImportSAvDataUserService.importDataFromFile(ImportSAvDataUserService.groovy:733) at com.saviynt.ecm.services.ImportSAvDataUserService.importDataFromFile(ImportSAvDataUserService.groovy:686) at com.saviynt.ecm.services.UsersService.uploadUserFinalStep(UsersService.groovy:8713) at com.saviynt.ecm.identitywarehouse.controllers.UsersController$_closure26.doCall(UsersController.groovy:1720) at grails.plugin.springsecurity.web.filter.GrailsAnonymousAuthenticationFilter.doFilter(GrailsAnonymousAuthenticationFilter.java:53) at com.saviynt.webservice.SaviyntRestAuthenticationFilter.doFilter(SaviyntRestAuthenticationFilter.groovy:159) at grails.plugin.springsecurity.web.authentication.logout.MutableLogoutFilter.doFilter(MutableLogoutFilter.java:62) at grails.plugin.springsecurity.web.SecurityRequestHolderFilter.doFilter(SecurityRequestHolderFilter.java:59) at com.mrhaki.grails.plugin.xframeoptions.web.XFrameOptionsFilter.doFilterInternal(XFrameOptionsFilter.java:69) at com.brandseye.cors.CorsFilter.doFilter(CorsFilter.java:82) at java.lang.Thread.run(Thread.java:750)

Best regards

Harish

[pmahalle]

@HarishG ,

Try below Pre-Processor query json:

{
"ADDITIONALTABLES": {
"USERS": "SELECT USERKEY, USERNAME FROM USERS",
"ACCOUNTS": "SELECT * FROM ACCOUNTS",
"ACCOUNT_ENTITLEMENTS1": "SELECT * FROM ACCOUNT_ENTITLEMENTS1"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY60"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA NU SET CUSTOMPROPERTY60 = 'TEST' WHERE NOT EXISTS (SELECT 1 FROM CURRENTACCOUNTS ACC JOIN CURRENTACCOUNT_ENTITLEMENTS1 AC1 ON ACC.ID = AC1.ID
WHERE ACC.NAME = NU.USERNAME AND AC1.ENTITLEMENT_VALUEKEY = '139233')"
]
}

[PremMahadikar]

Hi @HarishG ,

The below script is the right format and works perfectly!

{
"ADDITIONALTABLES": {
"USERS": "SELECT USERKEY, USERNAME FROM USERS",
"ACCOUNTS": "SELECT * FROM ACCOUNTS",
"ACCOUNT_ENTITLEMENTS1": "SELECT * FROM ACCOUNT_ENTITLEMENTS1"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY60"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET customproperty60= 're-test'",
"DELETE FROM NEWUSERDATA NU WHERE NU.USERNAME in (SELECT ca.name from CURRENTACCOUNTS ca,CURRENTACCOUNT_ENTITLEMENTS1 cae1 where ca.accountkey=cae1.accountkey and ca.name=nu.username and cae1.entitlement_valuekey=<ENTITLEMENT KEY>)"
]
}

FYI: My csv file has 3 columns - username,customproperty60,statuskey

If this solves your question, please consider selecting Accept As Solution and hit Kudos