Update User Attribute Via REST Conenctor in Saviynt

[Manu269]

Hello Team,

Please refer the below scenario and help with your inputs :

Background :

1. As a part of user onboarding we have created a create user form where user is getting onboarded.

2. There could be a situation where the user onbaorded may have start date as current day or future date.

3. We need to create the user in Saviynt in both the cases as 'Inactive'.

Assistance Requrired : How can we create the user in In Active State. As its creating everytime in Active State.

4. Based on start date we need to mark the user status as active and provision some access.

To handle this we came up with below query which we are generating it via Runtime Analytics and calling that analytics in Sav4Sav Rest Connection , user import json.

Similarly on End Date we need to trigger the disable account and access

Runtime Analytics Query (analyticsid :1621)

SELECT
u.username as USER_ID,
u.firstname as FIRSTNAME,
u.lastname as LASTNAME,
u.enddate as ENDDATE,
u.startdate as STARTDATE,
u.email as EMAIL,
u1.email as MANAGEREMAIL,
u1.username as USERNAME,
u.customproperty25
FROM
USERS u
JOIN
USERS u1 on u.manager = u1.userkey
WHERE
DATE(u.enddate) = CURDATE()
OR DATE(u.startdate) = CURDATE()
ORDER BY
USERNAME;

User Import Json

{
"connection": "userAuth",
"url": "https://XXXXX/ECM/api/v5/fetchRuntimeControlsDataV2",
"httpMethod": "POST",
"httpHeaders": {
"Authorization": "${access_token}",
"contentType": "application/json"
},
"httpParams": "{\"analyticsid\":\"1621\"}",
"httpContentType": "application/json",
"colsToPropsMap": {
"username": "USER_ID~#~char",
"customproperty25": "#CONST#Terminated~#~char"
},
"userResponsePath": "results",
"pagination": {
"offset": {
"offsetParam": "offset",
"batchParam": "max",
"batchSize": 10000,
"totalCountPath": "completeResponseMap.total"
}
}
}

Assistance Required :

As you can see In the above json I am hardcoding the value customproperty25 as Terminated.

I need help if in via same User Import Json I can handle both the scenario.

i.e. if the query returns the startdate then update the CP25 as Active (for that user) and if for the user end date crietria matches then update Cp25 as terminated. 

[naveenss]

Hi @Manu269  Regarding #3, have you created an attribute in the create user request form as a "NUMBER" and the default value for this would be "0". Map this attribute to statuskey of the user table. 

If this is not done, can you add this attribute? Please see screenshot below.

[Manu269]

Thanks for the information.

validating #3

[naveenss]

@Manu269  can you try the below JSON to compare the end date and populate the value?

{
    "connection": "userAuth",
    "url": "https://XXXXX/ECM/api/v5/fetchRuntimeControlsDataV2",
    "httpMethod": "POST",
    "httpHeaders": {
        "Authorization": "${access_token}",
        "contentType": "application/json"
    },
    "httpParams": "{\"analyticsid\":\"1621\"}",
    "httpContentType": "application/json",
    "colsToPropsMap": {
        "username": "USER_ID~#~char",
        "customproperty25": "#CONST#${Calendar userEndDate = Calendar.getInstance();userEndDate.setTime(response.results.ENDDATE); def finEndDate = userEndDate.getTime().format(\"yyyy-MM-dd\"); Calendar today = Calendar.getInstance(); def curDate = today.getTime().format(\"yyyy-MM-dd\");if(finEndDate==curDate){return 'Terminated'}else{return 'Active'}}~#~char"
    },
    "userResponsePath": "results",
    "pagination": {
        "offset": {
            "offsetParam": "offset",
            "batchParam": "max",
            "batchSize": 10000,
            "totalCountPath": "completeResponseMap.total"
        }
    }
}

Let me know if this helps!

[Manu269]

It did not work

[naveenss]

Are you seeing any error? It should ideally work. It worked for me. 

[Manu269]

@naveenss 

Can you please assist here......

refer the below error log :

"2024-03-08T11:06:26.045+00:00","ecm-worker","services.ImportSAvDataUserService","quartzScheduler_Worker-2-lk2bk","DEBUG","Proceed to Import for records - > [Tuser2, ${Calendar userEndDate = Calendar.getInstance();userEndDate.setTime(response.results.ENDDATE); def finEndDate = userEndDate.getTime().format("yyyy-MM-dd"); Calendar today = Calendar.getInstance(); def curDate = today.getTime().format("yyyy-MM-dd");if(finEndDate==curDate){return 'Terminated'}else{return 'Active'}}] : true"
"2024-03-08T11:06:26.045+00:00","ecm-worker","services.ImportSAvDataUserService","quartzScheduler_Worker-2-lk2bk","DEBUG","user not found in map.. fetching.."
"2024-03-08T11:06:26.045+00:00","ecm-worker","services.ImportSAvDataUserService","quartzScheduler_Worker-2-lk2bk","DEBUG","Executing query select userkey as userkey from users where username = :reconvalue , reconvalue=TUSER2"
"2024-03-08T11:06:26.047+00:00","ecm-worker","services.ImportSAvDataUserService","quartzScheduler_Worker-2-lk2bk","DEBUG","found userkey = 644 for TUSER2"
"2024-03-08T11:06:26.047+00:00","ecm-worker","services.ImportSAvDataUserService","quartzScheduler_Worker-2-lk2bk","DEBUG","reconcillationValue during updation ::: TUSER2"
"2024-03-08T11:06:26.047+00:00","ecm-worker","services.ImportSAvDataUserService","quartzScheduler_Worker-2-lk2bk","DEBUG","User with username 'TUSER2' found."
"2024-03-08T11:06:26.047+00:00","ecm-worker","services.ImportSAvDataUserService","quartzScheduler_Worker-2-lk2bk","DEBUG","Adding user TUSER2 to the batch"
"2024-03-08T11:06:26.047+00:00","ecm-worker","services.ImportSAvDataUserService","quartzScheduler_Worker-2-lk2bk","DEBUG","This is update user Tuser2 before setting the dirtyMap"
"2024-03-08T11:06:26.047+00:00","ecm-worker","services.ImportSAvDataUserService","quartzScheduler_Worker-2-lk2bk","DEBUG","propNamesTemp = [customproperty25, jobID, savUpdateDate, savUpdateDate]"
"2024-03-08T11:06:26.047+00:00","ecm-worker","services.ImportSAvDataUserService","quartzScheduler_Worker-2-lk2bk","DEBUG","#1 :: Tuser2 :: #Adding update user to the batch"
"2024-03-08T11:06:26.060+00:00","ecm-worker","util.JDBCExceptionReporter","quartzScheduler_Worker-2-lk2bk","ERROR","Data truncation: Data too long for column 'CUSTOMPROPERTY25' at row 1"
"2024-03-08T11:06:26.060+00:00","ecm-worker","events.PatchedDefaultFlushEventListener","quartzScheduler_Worker-2-lk2bk","ERROR","Could not synchronize database state with session"
"2024-03-08T11:06:26.586+00:00","ecm-worker","","null-lk2bk","","org.hibernate.exception.DataException: Could not execute JDBC batch update at com.saviynt.ecm.services.ImportSAvDataUserService$_importDataFromFile_closure24.doCall(ImportSAvDataUserService.groovy:2255) at org.grails.datastore.gorm.GormStaticApi.withTransaction(GormStaticApi.groovy:814) at org.grails.datastore.gorm.GormStaticApi.withTransaction(GormStaticApi.groovy:714) at com.saviynt.ecm.services.ImportSAvDataUserService.importDataFromFile(ImportSAvDataUserService.groovy:2159) at com.saviynt.provisoning.rest.RestProvisioningService.processUsers(RestProvisioningService.groovy:3090) at com.saviynt.provisoning.rest.RestProvisioningService.importUsers(RestProvisioningService.groovy:2942) at com.saviynt.ecm.integration.ExternalConnectionCallService.importUserUsingExternalConnection(ExternalConnectionCallService.groovy:1111) at UserImportJob.execute(UserImportJob.groovy:108) at org.quartz.core.JobRunShell.run(JobRunShell.java:199) at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:546)Caused by: java.sql.BatchUpdateException: Data truncation: Data too long for column 'CUSTOMPROPERTY25' at row 1 at com.mysql.cj.util.Util.handleNewInstance(Util.java:192) at com.mysql.cj.util.Util.getInstance(Util.java:167) at com.mysql.cj.util.Util.getInstance(Util.java:174) at com.mysql.cj.jdbc.exceptions.SQLError.createBatchUpdateException(SQLError.java:224) at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchSerially(ClientPreparedStatement.java:853) at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchInternal(ClientPreparedStatement.java:435) at com.mysql.cj.jdbc.StatementImpl.executeBatch(StatementImpl.java:796) ... 10 moreCaused by: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Data too long for column 'CUSTOMPROPERTY25' at row 1 at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:104) at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953) at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1092) at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchSerially(ClientPreparedStatement.java:832) ... 12 more"
"2024-03-08T11:06:26.061+00:00","ecm-worker","services.ImportSAvDataUserService","quartzScheduler_Worker-2-lk2bk","DEBUG","Exception in importDataFromFile ::"
"2024-03-08T11:06:26.586+00:00","ecm-worker","","null-lk2bk","","org.hibernate.exception.DataException: Could not execute JDBC batch update at com.saviynt.ecm.services.ImportSAvDataUserService$_importDataFromFile_closure24.doCall(ImportSAvDataUserService.groovy:2255) at org.grails.datastore.gorm.GormStaticApi.withTransaction(GormStaticApi.groovy:814) at org.grails.datastore.gorm.GormStaticApi.withTransaction(GormStaticApi.groovy:714) at com.saviynt.ecm.services.ImportSAvDataUserService.importDataFromFile(ImportSAvDataUserService.groovy:2159) at com.saviynt.provisoning.rest.RestProvisioningService.processUsers(RestProvisioningService.groovy:3090) at com.saviynt.provisoning.rest.RestProvisioningService.importUsers(RestProvisioningService.groovy:2942) at com.saviynt.ecm.integration.ExternalConnectionCallService.importUserUsingExternalConnection(ExternalConnectionCallService.groovy:1111) at UserImportJob.execute(UserImportJob.groovy:108) at org.quartz.core.JobRunShell.run(JobRunShell.java:199) at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:546)Caused by: java.sql.BatchUpdateException: Data truncation: Data too long for column 'CUSTOMPROPERTY25' at row 1 at com.mysql.cj.util.Util.handleNewInstance(Util.java:192) at com.mysql.cj.util.Util.getInstance(Util.java:167) at com.mysql.cj.util.Util.getInstance(Util.java:174) at com.mysql.cj.jdbc.exceptions.SQLError.createBatchUpdateException(SQLError.java:224) at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchSerially(ClientPreparedStatement.java:853) at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchInternal(ClientPreparedStatement.java:435) at com.mysql.cj.jdbc.StatementImpl.executeBatch(StatementImpl.java:796) ... 10 moreCaused by: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Data too long for column 'CUSTOMPROPERTY25' at row 1 at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:104) at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953) at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1092) at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchSerially(ClientPreparedStatement.java:832) ... 12 more"
"2024-03-08T11:06:26.061+00:00","ecm-worker","rest.RestProvisioningService","quartzScheduler_Worker-2-lk2bk","DEBUG","End Process Users"
"2024-03-08T11:06:26.062+00:00","ecm-worker","rest.RestUtilService","quartzScheduler_Worker-2-lk2bk","DEBUG","Writing job history to import logs."
"2024-03-08T11:06:26.062+00:00","ecm-worker","rest.RestUtilService","quartzScheduler_Worker-2-lk2bk","DEBUG","Number of log entries to be written : 1"
"2024-03-08T11:06:26.068+00:00","ecm-worker","rest.RestProvisioningService","quartzScheduler_Worker-2-lk2bk","DEBUG","End Import Users"
"2024-03-08T11:06:26.068+00:00","ecm-worker","integration.ExternalConnectionCallService","quartzScheduler_Worker-2-lk2bk","DEBUG","END IMPORT USER USING EXTERNAL CONNECTION"
"2024-03-08T11:06:26.068+00:00","ecm-worker","jobs.UserImportJob","quartzScheduler_Worker-2-lk2bk","DEBUG","End call externalConnectionCallService"
"2024-03-08T11:06:26.068+00:00","ecm-worker","jobs.UserImportJob","quartzScheduler_Worker-2-lk2bk","DEBUG","Setting ecmImportJob enddate: Fri Mar 08 11:06:26 UTC 2024"

[Manu269]

@naveenss also note that I have other update rules to be triggered based on cp25 which is getting updated here.

Hence if you can assist would be greatly appreciated.

[Manu269]

Refer As below :

It is not working........

[Manu269]

@naveenss i fixed this using single select from SQL query.

added default query to show inactive and non editable.

[naveenss]

@Manu269 I believe since you have made it as hidden attribute, its unable to map the value.