Analytics - Provision Access

[ejeong]

Hi,

I am trying to add C-Level people into one group and make sure not create "add access" task for people who are already in the group. Here is query I made

select '274568' as entvaluekey, a.accountkey as acctKey, a.name as accName,u.userKey, 'Provision Access' as 'Default_Action_For_Analytics'
from users u,accounts a,endpoints e,user_accounts ua
where u.userkey = ua.userkey and a.accountkey = ua.accountkey and a.endpointkey = e.endpointkey
and e.displayname like 'Corp AD%'
and u.userkey in (SELECT u.userkey
FROM users u
LEFT JOIN users um ON u.manager = um.userkey
WHERE u.customproperty22 IS NOT NULL
AND u.statuskey = 1
AND ((
um.customproperty15 LIKE 'L9'
OR um.customproperty15 LIKE 'L10'
OR um.customproperty15 LIKE 'L11'
OR um.customproperty15 LIKE 'L12'
)
OR (
u.customproperty15 LIKE 'L9'
OR u.customproperty15 LIKE 'L10'
OR u.customproperty15 LIKE 'L11'
OR u.customproperty15 LIKE 'L12'
)))
AND u.userkey NOT IN (
		SELECT ua.userkey
		FROM ACCOUNTS A
			,USER_ACCOUNTS UA
			,USERS U
			,ENDPOINTS E
			,Entitlement_values ev
			,account_entitlements1 ae1
			,entitlement_types et
		WHERE A.ACCOUNTKEY = UA.ACCOUNTKEY
			AND U.USERKEY = UA.USERKEY
			AND A.ENDPOINTKEY = E.ENDPOINTKEY
			AND U.STATUSKEY = 1
			AND e.displayname LIKE 'Corp AD - Stg'
			AND ae1.accountkey = a.accountkey
			AND e.endpointkey = et.endpointkey
			AND et.entitlementtypekey = ev.entitlementtypekey
			AND ae1.entitlement_valuekey = ev.entitlement_valuekey
			AND ev.entitlement_value = 'CN=RBAC_L9,OU=Role Based Groups,OU=Global IT Support Groups,DC=corpstg,DC=oncoupangstg,DC=net'
		)

I dont see any issue when saving report with allowed action but when trying to create tasks. our tenant is being stuck... and server restart is required.. 

can you please let me know what's wrong with this?

[ejeong]

looks like this is error related. could you please let me know how to fix this? can't we hardcode entitlement_valuekey?

2022-07-08/07:17:57.761 [{}] [https-jsse-nio-443-exec-563] DEBUG controllers.DataAnalyzerController - action id : 7865300456
2022-07-08/07:17:57.762 [{}] [https-jsse-nio-443-exec-563] DEBUG services.SaviyntCommonUtilityService - Enter fetchExternalConnectionFromConfig
2022-07-08/07:17:57.762 [{}] [https-jsse-nio-443-exec-563] DEBUG services.SaviyntCommonUtilityService - dataAnalyzerURL:true
2022-07-08/07:17:57.762 [{}] [https-jsse-nio-443-exec-563] DEBUG services.SaviyntCommonUtilityService - URL::jdbc:mysql://coupangdevrds.c8fn3w1jfiun.ap-northeast-2.rds.amazonaws.com:3306/saviyntaws?serverTimezone=UTC&useUnicode=yes&characterEncoding=UTF-8
2022-07-08/07:17:57.775 [{}] [https-jsse-nio-443-exec-563] DEBUG services.SaviyntCommonUtilityService - Exit fetchExternalConnectionFromConfig
2022-07-08/07:17:57.775 [{}] [https-jsse-nio-443-exec-563] DEBUG saviynt.DataAnalyzerService - in addtimeout
2022-07-08/07:17:57.780 [{}] [https-jsse-nio-443-exec-563] ERROR controllers.DataAnalyzerController - Error :
java.sql.SQLException: Operand should contain 1 column(s)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1094)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4208)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4140)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2597)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2758)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2820)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2769)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1569)
at com.saviynt.DataAnalyzerService.get_data(DataAnalyzerService.groovy:120)
at com.saviynt.ecm.identitywarehouse.controllers.DataAnalyzerController$_closure5.doCall(DataAnalyzerController.groovy:140)
at grails.plugin.springsecurity.web.filter.GrailsAnonymousAuthenticationFilter.doFilter(GrailsAnonymousAuthenticationFilter.java:53)
at com.saviynt.webservice.SaviyntRestAuthenticationFilter.doFilter(SaviyntRestAuthenticationFilter.groovy:145)
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.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)

[ejeong]

I tried like below as well but still same issue.. 

select u.username,ev.entitlement_valuekey as entvaluekey, a.accountkey as acctKey, a.name as accName,u.userKey, 'Provision Access' as 'Default_Action_For_Analytics',a.endpointkey
from users u,accounts a,endpoints e,user_accounts ua,entitlement_values ev
where u.userkey = ua.userkey and a.accountkey = ua.accountkey and a.endpointkey = e.endpointkey
and e.displayname like 'Corp AD%'
and a.status like '1'
and a.endpointkey = 2
and ev.entitlement_valuekey = 274568
and u.userkey in (SELECT u.userkey
FROM users u
LEFT JOIN users um ON u.manager = um.userkey
WHERE u.customproperty22 IS NOT NULL
AND u.statuskey = 1
AND ((
um.customproperty15 LIKE 'L9'
OR um.customproperty15 LIKE 'L10'
OR um.customproperty15 LIKE 'L11'
OR um.customproperty15 LIKE 'L12'
)
OR (
u.customproperty15 LIKE 'L9'
OR u.customproperty15 LIKE 'L10'
OR u.customproperty15 LIKE 'L11'
OR u.customproperty15 LIKE 'L12'
)))
AND u.userkey NOT IN (
		SELECT ua.userkey
		FROM ACCOUNTS A
			,USER_ACCOUNTS UA
			,USERS U
			,ENDPOINTS E
			,Entitlement_values ev
			,account_entitlements1 ae1
			,entitlement_types et
		WHERE A.ACCOUNTKEY = UA.ACCOUNTKEY
			AND U.USERKEY = UA.USERKEY
			AND A.ENDPOINTKEY = E.ENDPOINTKEY
			AND U.STATUSKEY = 1
			AND e.displayname LIKE 'Corp AD - Stg'
			AND ae1.accountkey = a.accountkey
			AND e.endpointkey = et.endpointkey
			AND et.entitlementtypekey = ev.entitlementtypekey
			AND ae1.entitlement_valuekey = ev.entitlement_valuekey
			AND ev.entitlement_value = 'CN=RBAC_L9,OU=Role Based Groups,OU=Global IT Support Groups,DC=corpstg,DC=oncoupangstg,DC=net'
		)