Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

Exlcuding accounts in the Entitlement Owner Campaign

AravindK
Regular Contributor
Regular Contributor

Hi Team,

We have requirement in the Entitlment owner campaign to include only the accounts whose accountname is same as username.

Ex: User(71682906) have two accounts like below: 
       1)71682906   2)9D71640906.

While launching the campaign it should bring only  one account which account name is same as username i.e 71682906 and exclude 9D71640906.

In the Account_entitlments1 query in Advanced configurations , we have setup like below :

ae1.entitlement_valuekey.entitlement_value IN ('ROLE_ADMIN')

 This is bringing both two accounts  but we need to bring only account which is accountname=username.

We have prepared a query to check in data analyzer and  it is fetching the data as expected :

select a.accountkey from accounts a ,account_entitlements1 ae1,users u where ae1.accountkey=a.accountkey and u.username=a.name and ae1.ENTITLEMENT_VALUEKEY=16539404  .

But in the campaign we tried like below but it is giving Incorrect SQL syntax error:
1)
ae1.entitlement_valuekey.entitlement_value IN ('ROLE_ADMIN') and ae1.accountkey in (select a.accountkey from accounts a ,account_entitlements1 ae1,users u where ae1.accountkey=a.accountkey and u.username=a.name)
 2)
ae1.entitlement_valuekey='16539404' and ae1.accountkey in (select a.accountkey from accounts a ,account_entitlements1 ae1,users u where ae1.accountkey=a.accountkey and u.username=a.name)

Please help us on this use case.


8 REPLIES 8

dgandhi
All-Star
All-Star

Try below in Accounts query placeholder

name in (select a.name from accounts a,user_accounts ua,users u where a.accountkey=ua.accountkey and ua.userkey=u.userkey and a.name=u.username)

dgandhi_0-1692890480477.png

If you want to include active accounts then include below

name in (select a.name from accounts a,user_accounts ua,users u where a.accountkey=ua.accountkey and ua.userkey=u.userkey and a.name=u.username and a.status in (1,'Active','Manually Provisioned'))

Thanks,
Devang Gandhi
If this reply answered your question, please Accept As Solution and give Kudos to help others who may have a similar problem.

AravindK
Regular Contributor
Regular Contributor

Hi @dgandhi ,

Thanks for the suggestion. 
Trying like below but it is not pulling the any accounts.

AravindK_0-1692941167622.png

Accounts query :

name in (select a.name from accounts a,user_accounts ua,users u where a.accountkey=ua.accountkey and ua.userkey=u.userkey and a.name=u.username)

Account_Entitlement Query:
ae1.entitlement_valuekey.entitlement_value IN ('ROLE_ADMIN')

Hi @AravindK ,

You want to include only accounts whose name is same as username and having ROLE_ADMIN entitlement?


Pandharinath Mahalle(Paddy)
If this reply helps your question, please consider selecting Accept As Solution and hit Kudos 🙂

Remove below

Account_Entitlement Query:
ae1.entitlement_valuekey.entitlement_value IN ('ROLE_ADMIN')

and add in Entitlement query

entitlement_value IN ('ROLE_ADMIN')

Above accounts query worked perfectly in my case.

 

Thanks,
Devang Gandhi
If this reply answered your question, please Accept As Solution and give Kudos to help others who may have a similar problem.

AravindK
Regular Contributor
Regular Contributor

Hi @pmahalle ,
Correct. Need to pull the only accounts whose account name same as username and having ROLE_ADMIN entitlement?

Hi @AravindK ,

Can you use below query under Accounts Query for you campaign and check

name in (select ac.name from users us, user_accounts ua, accounts ac, account_entitlements1 ae, entitlement_values ev where us.userkey=ua.userkey and
us.username=ac.name and ua.accountkey=ae.accountkey and ae.entitlement_valuekey = ev.entitlement_valuekey and ev.entitlement_value='ROLE_ADMIN')


Pandharinath Mahalle(Paddy)
If this reply helps your question, please consider selecting Accept As Solution and hit Kudos 🙂

AravindK
Regular Contributor
Regular Contributor

Hi @pmahalle I have tried the above query under accounts query but it is pulling all the entitlements of the Application which is selected in the campaign configuration. It should fetch one entitlement and associated accounts.

Hi @AravindK ,

Along  with above account query use below query in entitlement query and check 

entitlement_value in ('ROLE_ADMIN')


Pandharinath Mahalle(Paddy)
If this reply helps your question, please consider selecting Accept As Solution and hit Kudos 🙂