Unable to Use JSON_arrayagg and JSON_object functions in Saviynt

[Mohit_Sanka]

Hi All,

We are unable to update the connection to add or modify existing SQL queries in the Sav for Sav DB Connection after the recent upgrade. This connection was using Json_arrayagg & Json_object functions in the account import query to gather the data and update it into an account custom property.

Can you let us know if anyone faced the same issue and how it can be resolved?

Thanks & Regards,

Mohit

[Rishi]

@Mohit_Sanka can you provide the JSON (after masking any customer specific information) and also confirm if you see any error in the logs when you try to update it.

Also confirm to which version Saviynt application was upgraded?

[Mohit_Sanka]

Hi Rishi,

This is a sample query of which we are using in the our connection. You can try similar one. This is not even working in Data analyzer or Analytics post 3.17 upgrade.

SELECT 'Ticketing' as sysname, 'Ticketing' as endpointname,'EntilementType' as attribute,'1' as status,CURDATE() as importdate,'Accounttype' as type,'ServiceNow' as accounttype,'Accountname' as accountcn, 'Accountname' as name,'Accountname' as appname, json_arrayagg(json_object('User',ars.ACCOUNTNAME)) as cp37 FROM ARSTASKS ars join users u ON (u.USERKEY=ars.userkey) join endpoints ep on ep.endpointkey = ars.endpoint WHERE ars.endpoint=endpointkey and ars.status=3 and ars.tasktype=2 and (datediff(curdate(),ars.updatedate) > 0 and datediff(curdate(),ars.updatedate) < 7)

This is the error we are seeing the logs

023-04-17 10:07:42,489 [https-jsse-nio-443-exec-32] DEBUG auth.LoginController - URL TO SAML LOGIN2=/ECM/login/index?login=true&idp=https://sts.windows.net/f66fae02-5d36-495b-bfe0-78a6ff9f8e6e/ JSESSIONID =4FBA71C1687D376E4744A71EF363110E

2023-04-17 10:07:43,718 [https-jsse-nio-443-exec-3] DEBUG println.PrintlnToLogger - Println :: SQLi-failure outer..failed due to pattern \bJSON_ARRAYAGG\b\s*\( , controllerAction = DATAANALYZER_FETCHRESULT , data = DATAANALYZER ###SPLITTER### 4578362059 ###SPLITTER### FALSE ###SPLITTER### SELECT 'TICKETING' AS SYSNAME, 'TICKETING' AS ENDPOINTNAME,'ENTILEMENTTYPE' AS ATTRIBUTE,'1' AS STATUS,CURDATE() AS IMPORTDATE,'Accounttype' AS TYPE,'SERVICENOW' AS ACCOUNTTYPE,'Accountname' AS ACCOUNTCN, 'Accountname' AS NAME,'Accountname' AS APPNAME, JSON_ARRAYAGG(JSON_OBJECT('USER',ARS.ACCOUNTNAME)) AS CP37 FROM ARSTASKS ARS JOIN USERS U ON (U.USERKEY=ARS.USERKEY) JOIN ENDPOINTS EP ON EP.ENDPOINTKEY = ARS.ENDPOINT WHERE ARS.ENDPOINT=150 AND ARS.STATUS=3 AND ARS.TASKTYPE=14 AND (DATEDIFF(CURDATE(),ARS.UPDATEDATE) < 7) ###SPLITTER### FETCHRESULT ###SPLITTER###

2023-04-17 10:07:45,634 [https-jsse-nio-443-exec-22] DEBUG log.LoggerService - logFileAndPath: /opt/sharedappdrive/saviynt/logs/error-10.112.3.68.log

2023-04-17 10:07:45,634 [https-jsse-nio-443-exec-22] DEBUG log.LoggerService - Entered getLogFiles.