Actionable Analytics Query - Add Provisioning

[void0703]

Hi Team,

          I am trying to a analytics query using which I will add entitlements to the users account. What I want to achieve here is I have a CP57 property and using which we are assigning the users the entitlement , Ex - CP57 (city name) -  CN=Sec/# (city name) / 629792983 . We are assigning the users entitlement in this way but due to some reason there are some users for whom it was not assigned. So for that reason I want to write a query to - 

1. First find the users who are not assigned with this entitlement.

2. Assigned Entitlement for only those users 

I want to achieve these 2 scenarios using actionable analytics. 

Thanks,

[rushikeshvartak]

Share current query you have built

[void0703]

This is the query that i have used to find duplicate groups assigned to the user similarly i want to find user who are not assigned with correct group and assign them entitlement using actionable analytics query to save my manual work.

Query to find wrong assigned group to user :-

SELECT U.USERNAME, U.FIRSTNAME, U.LASTNAME, U.EMAIL, A.NAME ACCOUNTNAME, A.STATUS ACCOUNTSTATUS, E.ENDPOINTNAME, U.CUSTOMPROPERTY57, EV.ENTITLEMENT_VALUE , U.STATUSKEY , EV.ENTITLEMENT_VALUEKEY AS entvaluekey, AE1.ACCOUNTKEY AS acctKey 
FROM ACCOUNTS A
JOIN USER_ACCOUNTS UA ON A.ACCOUNTKEY = UA.ACCOUNTKEY
JOIN USERS U ON UA.USERKEY = U.USERKEY
JOIN ENDPOINTS E ON A.ENDPOINTKEY = E.ENDPOINTKEY
INNER JOIN ACCOUNT_ENTITLEMENTS1 AE1 ON A.ACCOUNTKEY = AE1.ACCOUNTKEY
LEFT JOIN ENTITLEMENT_VALUES EV ON AE1.ENTITLEMENT_VALUEKEY = EV.ENTITLEMENT_VALUEKEY
WHERE E.ENDPOINTNAME IN ('Active Directory')
AND U.STATUSKEY = 1
AND A.STATUS IN ('1', 'Manually Provisioned','Active')
AND EV.ENTITLEMENT_VALUE LIKE 'CN=ORG%'
AND EV.ENTITLEMENT_VALUE NOT LIKE CONCAT('%', U.CUSTOMPROPERTY57, '%')

So for example :

customproperty52 of user is abcd we will assign a entitlement such as CN=ORG/abcd likewise

[rushikeshvartak]

SELECT u.username,
       u.firstname,
       u.lastname,
       u.email,
       a.NAME   accountname,
       a.status accountstatus,
       e.endpointname,
       u.customproperty57,
       ev.entitlement_value ,
       u.statuskey ,
       (
              SELECT ev.entitlement_valuekey
              FROM   entitlement_values
              WHERE  Substring_index(Substring_index(ev1.entitlement_value, '/', -1), ',', 1)=u.cuscustomproperty57 limit 1) AS entvaluekey
FROM   accounts a,
       endpoints e,
       user_accounts ua,
       users u,
WHERE  a.accountkey = ua.accountkey
AND    a.endpointkey = e.endpointkey
AND    u.userkey = ua.userkey
AND    u.statuskey = 1
AND    a.status IN ('1',
                    'Manually Provisioned',
                    'Active')
AND    e.endpointname = 'Active Directory'
AND    a.accountkey NOT IN
       (
              SELECT accountkey
              FROM   account_entitlements1 ae1,
                     entitlement_values ev1
              WHERE  ev1.entitlement_valuekey=ae1.entitlement_valuekey
              AND    u.customproperty57= substring_index(substring_index(ev1.entitlement_value, '/', -1), ',', 1))

[void0703]

Getting this error : 

Error : Query has been terminated due to longer execution time

[rushikeshvartak]

Add limit and check from data analyzer

[void0703]

Limit is present still facing same error 

[rushikeshvartak]

Please share screenshot and logs

[void0703]

logs :-

'Statement cancelled due to timeout or client request at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:113) at com.mysql.cj.jdbc.StatementImpl.checkCancelTimeout(StatementImpl.java:2191) at com.mysql.cj.protocol.a.NativeProtocol.sendQueryPacket(NativeProtocol.java:1020) at com.mysql.cj.protocol.a.NativeProtocol.sendQueryString(NativeProtocol.java:887) at com.mysql.cj.NativeSession.execSQL(NativeSession.java:1073) at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1168) at com.saviynt.DataAnalyzerService.get_data(DataAnalyzerService.groovy:125) at com.saviynt.ecm.identitywarehouse.controllers.DataAnalyzerController$_closure5.doCall(DataAnalyzerController.groovy:141) at grails.plugin.springsecurity.web.filter.GrailsAnonymousAuthenticationFilter.doFilter(GrailsAnonymousAuthenticationFilter.java:53) at com.saviynt.webservice.SaviyntRestAuthenticationFilter.doFilter(SaviyntRestAuthenticationFilter.groovy:155) 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)

[void0703]

@rushikeshvartak  can you check the logs and let me know what's wrong ?

[CR]

@void0703  please refer below links for actionable report design.

https://docs.saviyntcloud.com/bundle/EIC-Admin-v24x/page/Content/Chapter17-EIC-Analytics/Managing-An...

https://forums.saviynt.com/t5/identity-governance/deprovision-role-if-expiration-date-is-passed/m-p/...

Sample query:

select distinct a.accountkey AS 'acctKey',
'1234' AS 'entvaluekey',
a.accountid,
a.comments as "Addl Info",
u.userkey as 'userKey',
u.username AS USER,
a.name as accName,
u.FIRSTNAME,
u.LASTNAME,
u.EMAIL AS USER_EMAIL,
SYSDATE() AS RUNTIME,
'provisionaccess' as 'Default_Action_For_Analytics',
a.endpointkey
from
users u,
user_accounts ua,
accounts a,
account_entitlements1 ae
where
a.status in ('ACTIVE','Manually Provisioned','1')
and
u.userkey = ua.userkey
and
ua.accountkey = a.accountkey
and a.accountkey=ae.accountkey
and u.userkey in
(select userkey from arstasks where endpoint = '111' AND TASKTYPE = 6 AND STATUS in ('1'));