Exclude Entitlements from Entitlement Owner certification that only have orphan accounts

[kfoley1]

We currently have an entitlement owner certification that has both entitlement and access verification of all users (does not include orphan accounts) with access to the entitlements. 

In Advanced Campaign Configs, we use the Accounts Query to exclude orphan accounts through the following query: status in ('Manually Provisioned','1') and accountkey in (select accountkey from user_accounts) - this works as expected on the access verification step of the cert. 

In the Entitlements Query we exclude entitlements that do not have accounts in them: entitlement_valuekey in (select entitlement_valuekey from account_entitlements1)

However, this query is only checking on accounts, so the output is that in the Entitlement Verification step the certifier can still see the entitlements that have an orphan account in them but the count shows 0.

How can we exclude these entitlements that only have orphan accounts and do not have users from the Entitlement Verification step the campaign?

[rushikeshvartak]

Share logs when you preview/launch campaign 

[kfoley1]

Attaching campaign logs

[rushikeshvartak]

Validated this in 24.4 and its working as expected 

[kfoley1]

Hi Rushikesh - this is not working, in your first screenshot I can see the entitlement with zero accounts - this should not be showing in Step 1, Ownership Verification, if there are zero accounts. That is my question, how do I get those entitlements not to show in Step 1 when there are only Orphan accounts there - we don't want the certifier to see those entitlements in Step 1 or 2 if there are no Users with the entitlement. 

[rushikeshvartak]

Yes its not working  use below workaround

• Run below enhanced query before launching campiagn  

SELECT
ev.ENTITLEMENT_VALUEKEY as entitlement_values__primarykey,
'Entitlement With Accounts' as entitlement_values__customproperty1

FROM entitlement_values ev
INNER JOIN account_entitlements1 ae
ON ev.ENTITLEMENT_VALUEKEY = ae.ENTITLEMENT_VALUEKEY
INNER JOIN accounts a
ON ae.ACCOUNTKEY = a.ACCOUNTKEY
INNER JOIN endpoints e
ON a.ENDPOINTKEY = e.ENDPOINTKEY
WHERE e.ENDPOINTNAME = 'ABCD'
AND a.STATUS IN (1, 'Active');

• And during campaign configuration based on use as 
  • customproperty1 in ('Entitlement with Accounts')

[dgandhi]

You can achieve this use case using below:

1. Identity all the entitlement which doesn't have any account using analytics query. Below for your reference, this should only list entitlement with active accounts.

2. Once the entitlement are identified, use one CP and add something like WithAccounts for entitlements having accounts

WithoutAccounts for entitlement without accounts.

3. Launch EO campaign, below query

The campaign will list only those entitlement with accounts