How do I exclude entitlements with no accounts in Entitlement Owner campaign?

[johnson]

I am attempting to set up a Entitlement Owner campaign and I have a query to pull in entitlements A, B, and C but I want to exclude any of these entitlements if they do not have any accounts associated with them. I have tested my query in Data Analyzer and it retrieves the correct results, but not sure how I would apply this to the Advanced Campaign Configurations.

SELECT e.entitlement_value, a.name
FROM entitlement_values e
JOIN account_entitlements1 m ON e.entitlement_valuekey = m.entitlement_valuekey
JOIN Accounts a ON m.accountkey = a.accountkey
WHERE e.entitlement_value IN ('A', 'B', 'C')

[dgandhi]

Have you tried without adding any condition and launching a campaign. If the entitlement doesnt have any accounts associated then it wont be included in the campaign.

[johnson]

That didn't work for me. I am still seeing entitlements with 0 accounts. The only configurations I have set is the default certifier, decommission action, and approval type = account. Is it because I set a default certifier? In the environment I am working with, none of the entitlements have an owner assigned so I need to set a default certifier just to have results pulled into a campaign. To me it makes sense why entitlements with zero accounts show up by default because they will still need to be part of the Entitlement Owner verification step, but I would like to configure a campaign to exclude these entitlements.

[dgandhi · Accepted solution]

Add below in Advance condition and try.

[johnson]

I missed this comment but this was exactly what I needed. Such a simple solution. Thank you!!

[rushikeshvartak]

You can find using below query Please change query as per your need

select * from(

select ev.entitlement_valuekey ,ev.entitlement_value,
(select count(* ) from account_entitlements1 ae1 ,accounts a where ae1.entitlement_valuekey=ev.entitlement_valuekey and 
a.accountkey=ae1.accountkey and a.status in (1,'Manually Provisioned') and a.accounttype ='A

)noofaccountsassignedwithent
from entitlement_values ev where 
ev.customproperty30 Like 'True' and ev.status=1
and entitlementtypekey=121 )data where noofaccountsassignedwithent=0

[johnson]

How would I apply this to a campaign? Would I add this query to the Account Entitlements1 Query box?

[pmahalle]

Hi @johnson ,

Try below query inside Entitlement Query under Advanced Campaign Config

ENTITLEMENT_VALUE IN (SELECT DISTINCT EV.ENTITLEMENT_VALUE FROM ACCOUNT_ENTITLEMENTS1 AE, ENTITLEMENT_VALUES EV WHERE AE.ENTITLEMENT_VALUEKEY = EV.ENTITLEMENT_VALUEKEY)

[johnson]

Thank you. This works to help me get the entitlements that have accounts associated with them and exclude entitlements that do not have accounts. My question is how do I combine this with a filter that only pulls in certain entitlements? I am using a query to filter and pull in entitlements if they start with a certain string. Would it look something like this in the Entitlements Query box?

ENTITLEMENT_VALUE IN (SELECT DISTINCT EV.ENTITLEMENT_VALUE FROM ACCOUNT_ENTITLEMENTS1 AE, ENTITLEMENT_VALUES EV WHERE AE.ENTITLEMENT_VALUEKEY = EV.ENTITLEMENT_VALUEKEY) AND ENTITLEMENT_VALUE LIKE 'READONLY_' AND ENTITLEMENT_VALUE LIKE 'WRITE_'

[pmahalle]

Hi @johnson ,

Use below query which will only pick entitlements start with string READONLY or WRITE and having associated accounts.

ENTITLEMENT_VALUE IN (SELECT DISTINCT EV.ENTITLEMENT_VALUE FROM ACCOUNT_ENTITLEMENTS1 AE, ENTITLEMENT_VALUES EV WHERE AE.ENTITLEMENT_VALUEKEY = EV.ENTITLEMENT_VALUEKEY AND (EV.ENTITLEMENT_VALUE LIKE 'READONLY_%'  OR EV.ENTITLEMENT_VALUE LIKE 'WRITE_%'))

[rushikeshvartak]

You should filter entitlements in entitlement filter query

entitlement_value not in ('Admin','PQR')

With filters 

[rushikeshvartak]

You can take out entitlement_Valuekey and put in certification config 

and ae1.entitlement_Valuekey not ( output from query)

[johnson]

Just for clarification, you are saying to take out entitlement_valuekey from the query you provided and add "and ae1.entitlement_Valuekey not ( output from query)" into certification config? More specifically in the Accounts Entitlements1 query?