We are delighted to share our new EIC Delivery Methodology for efficiently managing Saviynt Implementations and delivering quick time to value. CLICK HERE.

Unable to Use JSON_arrayagg and JSON_object functions in Saviynt

Mohit_Sanka
New Contributor II
New Contributor II

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

2 REPLIES 2

Rishi
Saviynt Employee
Saviynt Employee

@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
New Contributor II
New Contributor II

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.