Need help with Actionable analytics for provisioning user to a dynamic AD group

[BT]

Need help with Actionable analytics for provisioning user to a dynamic AD group.

Can anyone please share a sample SQL query for adding a AD group to AD account , the name of the group will be dynamically decided based on users CP12 value 

cn=<content of user CP12>#Sec1,OU=Security,OU=Groups,DC=xyz,DC=local

We have a technical rule that does add the group, but every now and then sometimes the group is missed out on adding , that is why want to setup analytics.

[rushikeshvartak]

SELECT u.username,
       u.customproperty12,
       u.userkey               AS userKey,
       a.accountkey            AS acctKey,
       a.NAME                  AS accName,
       ev.entitlement_valuekey AS entvaluekey
FROM   users u,
       entitlement_values ev,
       entitlement_types et,
       accounts a,
       user_accounts ua
WHERE  u.customproperty12 IS NOT NULL
       AND ev.entitlement_value = u.customproperty12
       AND ev.entitlementtypekey = et.entitlementtypekey
       AND et.endpointkey = a.endpointkey
       AND a.accountkey = ua.accountkey
       AND ev.entitlement_valuekey NOT IN (SELECT ae.entitlement_valuekey
                                           FROM   account_entitlements1 ae
                                           WHERE  ae.accountkey = a.accountkey) 

[shefalipatidar]

Is this query adding the group (cn=<content of user CP12>#Sec1,OU=Security,OU=Groups,DC=xyz,DC=local) to the entitlement in the account?

[pmahalle · Accepted solution]

@BT , @shefalipatidar ,

Use below query for adding the group (cn=<content of user CP12>#Sec1,OU=Security,OU=Groups,DC=xyz,DC=local) to the entitlement in the account and try once.

SELECT u.username,
       u.customproperty12,
       u.userkey               AS userKey,
       a.accountkey            AS acctKey,
       a.NAME                  AS accName,
       ev.entitlement_valuekey AS entvaluekey,
'Provision Access' AS Default_Action_For_Analytics
FROM   users u,
       entitlement_values ev,
       entitlement_types et,
       accounts a,
       user_accounts ua
WHERE  u.customproperty12 IS NOT NULL
       AND ev.entitlement_value = concat('cn=',u.customproperty12,'#Sec1,OU=Security,OU=Groups,DC=xyz,DC=local')
       AND ev.entitlementtypekey = et.entitlementtypekey
       AND et.endpointkey = a.endpointkey
       AND a.accountkey = ua.accountkey
       AND ev.entitlement_valuekey NOT IN (SELECT ae.entitlement_valuekey
                                           FROM   account_entitlements1 ae
                                           WHERE  ae.accountkey = a.accountkey) 

[shefalipatidar]

Is this assigning or just matching the condition

[pmahalle]

@shefalipatidar It will assign the entitlement once you run the analytics and provisioning job.

[shefalipatidar]

When I am trying to execute this query in data analyzer it is saying no data found?

[pmahalle]

@shefalipatidar  Check user's cp21 has correct value or not

[shefalipatidar]

I has the value of CP21 assigned. But still showing no data found.

[rushikeshvartak]

Share output of previous query without concat

[BT]

Thank you @rushikeshvartak  @pmahalle @shefalipatidar  the above solution worked . I have marked it as accepted solution ... thank you once again team for all the help.