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')
Solved! Go to Solution.
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.
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.
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
from entitlement_values ev where
ev.customproperty30 Like 'True' and ev.status=1
and entitlementtypekey=121 )data where noofaccountsassignedwithent=0
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)
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_'
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_%'))
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?