list of member in entitlement and send the detail to owner via email

[Community_User]

Originally posted on November 17 2020 at 13:09 UTC

Hello,

I am trying to setup Analytic report to send member list to entitlement owner via email

Following is the SQL query I made but it doesn't give me any return...Could you please advise me what needs to be fixed?

SELECT

U.USERNAME,

U.FIRSTNAME,

U.LASTNAME,

U.departmentname as 'DEPARTMENT',

U1.username,

CASE

WHEN U.STATUSKEY IS NULL THEN 'NOT AVAILABLE'

WHEN U.STATUSKEY = 0 THEN 'INACTIVE'

WHEN U.STATUSKEY = 1 THEN 'ACTIVE'

ELSE U.STATUSKEY

END 'USER STATUS',

E.Endpointname AS 'Endpoint',

e.endpointKey,

ev.ENTITLEMENT_VALUE as 'Group Name',

A.NAME AS 'ACCOUNTNAME',

CASE

WHEN A.STATUS IS NULL THEN 'NOT AVAILABLE'

WHEN A.STATUS = 1 THEN 'ACTIVE'

ELSE A.STATUS

END 'ACCOUNT STATUS',

CASE

WHEN ev.STATUS IS NULL THEN 'NOT AVAILABLE'

WHEN ev.STATUS = 1 THEN 'ACTIVE'

WHEN ev.STATUS = 2 then 'INACTIVE'

ELSE ev.STATUS

END 'Entitlement STATUS'

FROM

ACCOUNTS A,

USER_ACCOUNTS UA,

USERS U,

ENDPOINTS E,

Entitlement_values ev,

account_entitlements1 ae1,

entitlement_owners EO,

USERS U1

WHERE

A.ACCOUNTKEY = UA.ACCOUNTKEY

AND U.USERKEY = UA.USERKEY

AND A.ENDPOINTKEY = E.ENDPOINTKEY

and ae1.accountkey = a.accountkey

AND EO.USERKEY = U1.USERKEY

AND EO.ENTITLEMENT_VALUEKEY = EV.ENTITLEMENT_VALUEKEY

and ae1.entitlement_valuekey = ev.entitlement_valuekey

and e.endpointKey = 3

and ev.status = 1

Also, I want to know what Attribute name to be used in 'To' for email template and list of account in email content.

Thank you

[Community_User · Accepted solution]

Originally posted on November 29 2020 at 15:39 UTC

Hello,

Your query has 2 columns called Username which needs to be corrected. Since your requirement is to send email to the entitlement owner, I would suggest you to keep the entitlement owners' username as the Username column, and change the other column's alias.

Try to format the query like this:

SELECT

U.USERNAME as 'USER USERNAME',

U.FIRSTNAME,

U.LASTNAME,

U.departmentname AS 'DEPARTMENT',

U1.username,

CASE

WHEN U.STATUSKEY IS NULL THEN 'NOT AVAILABLE'

WHEN U.STATUSKEY = 0 THEN 'INACTIVE'

WHEN U.STATUSKEY = 1 THEN 'ACTIVE'

ELSE U.STATUSKEY

END 'USER STATUS',

E.Endpointname AS 'Endpoint',

e.endpointKey,

ev.ENTITLEMENT_VALUE AS 'Group Name',

A.NAME AS 'ACCOUNTNAME',

CASE

WHEN A.STATUS IS NULL THEN 'NOT AVAILABLE'

WHEN A.STATUS = 1 THEN 'ACTIVE'

ELSE A.STATUS

END 'ACCOUNT STATUS',

CASE

WHEN ev.STATUS IS NULL THEN 'NOT AVAILABLE'

WHEN ev.STATUS = 1 THEN 'ACTIVE'

WHEN ev.STATUS = 2 THEN 'INACTIVE'

ELSE ev.STATUS

END 'Entitlement STATUS'

FROM

ACCOUNTS A,

USER_ACCOUNTS UA,

USERS U,

ENDPOINTS E,

Entitlement_values ev,

account_entitlements1 ae1,

entitlement_owners EO,

USERS U1

WHERE

A.ACCOUNTKEY = UA.ACCOUNTKEY

AND U.USERKEY = UA.USERKEY

AND A.ENDPOINTKEY = E.ENDPOINTKEY

AND ae1.accountkey = a.accountkey

AND EO.USERKEY = U1.USERKEY

AND EO.ENTITLEMENT_VALUEKEY = EV.ENTITLEMENT_VALUEKEY

AND ae1.entitlement_valuekey = ev.entitlement_valuekey

AND e.endpointKey = 3

AND ev.status = 1;

For the TO field of email template, use this variable '${userEmail}'

You can refer to the documentation here as well - https://saviynt.freshdesk.com/support/solutions/articles/43000431557-managing-email-templates >

Email Parameters required for Analytics

Screenshot of documentation for reference-

In order to fetch the list of accounts in this email, you will have to loop through the account list. In your case the column which refers to the account name is ACCOUNTNAME and so the usage in the email template should be:

<% int count=Integer.parseInt("${ANALYTICSDATA.ACCOUNTNAME.size()}");

for(int i=0;i<count;i=i+1) ${ANALYTICSDATA.ACCOUNTNAME[i]} %>

The screenshot of the email template attached in the document also shows an example of how to loop through the data of a particular column.

Regards,

Saparja