exclude entitlement from user manager campaign

[iam01]

Hi Team,

In user manager campagin i want to exclude the entitlements assgined from  "entitlement with new account" option on endpoints.

[Manu269]

Refer this doc : Creating, Launching, and Scheduling Campaigns (saviyntcloud.com)

Account Entitlements1 Query

[iam01]

@Manu269 your reply is not related to the question. The above doc didn't contain any information related to the question.

[rushikeshvartak]

entitlement_Valuekey in (select ENTSWITHNEWACCOUNT from endpoints where endpointkey=30)

[iam01]

@rushikeshvartak This won't work the reason being ENTSWITHNEWACCOUNT is an array. However is this working in your environment?

First step I would like to know if endpoints table is available for campaigns, 

[AmitM]

Hi @iam01 , you can update the CP for these entitlements and use that in Campaign Advance config --> Entitlements Query. 

Thanks, Amit

[rushikeshvartak]

I have validated and its working

[rushikeshvartak]

if you have multiple use below query [increase union as per your ents count]

entitlement_Valuekey in (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(ENTSWITHNEWACCOUNT , ',', 1), ',', -1) as ent
from endpoints where endpointkey=30 union SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(ENTSWITHNEWACCOUNT , ',', 2), ',', -1) as ent
from endpoints where endpointkey=30)

Multiple Testing :

Endpoint 

Certification 

[iam01]

@rushikeshvartak thanks, I have tested something similar sometime back and it was working.  However thinking of it be more dynamic.

The below query is working in data analyzer but not in advance section: 

Let me know if you see anything wrong:

SELECT ev2.entitlement_valuekey from entitlement_values ev2 JOIN endpoints ON FIND_IN_SET(ev2.entitlement_valuekey,endpoints.ENTSWITHNEWACCOUNT) > 0 where endpoints.endpointkey=1 and endpoints.ENTSWITHNEWACCOUNT is not null

[rushikeshvartak]

use HQL not SQL

SELECT ev2.entitlement_ValueKey
FROM Entitlement_Values ev2
JOIN Endpoints e
ON LOCATE(ev2.entitlement_ValueKey , e.entsWithNewAccount) > 0
WHERE e.endpointKey = 1
AND e.entsWithNewAccount IS NOT NULL

[iam01]

@rushikeshvartak  I will test and confirm.

Quick Question:  for all campaign advance query it's HQL right ? Is there any other module in saviynt using HQL?

[rushikeshvartak · Accepted solution]

Internal Query are HQL , Analytics/data analyzer/dynamic attribute is SQL

[iam01]

@AmitM indeed. However looking for any ootb options.

Is there any restrictions on endpoints table in campaigns advanced query??