Hi Team,
We need to send monthly email notifications to the individual disconnected application owner so that they can upload the account import.csv.
what is the variable to fetch the individual application owner's name along with the Application name also we need to send individual emails to all owners with the application name
Below is the email template format
As part of the regular reconciliation between your disconnected application <APP NAME> , disconnected application account data should be shared with XXXX on a monthly basis. This is your reminder to share the latest account import feed file for your disconnected application.
Please share the account data from <APP Name> in the Account Import Feed.csv.
Thanks & Regards
XXXXXX
I tried with below Query and below email templets it gives all disconnected application data in query as well as in the email body but i need to send induvial owners their application name in the email body as mentioned in above .
Analytics' Query :
SELECT
ROW_NUMBER() OVER (ORDER BY SS.DISPLAYNAME) AS 'SI NO',
ss.DISPLAYNAME AS 'APPLICATION',
ep.ENDPOINTNAME,
ep.OWNERTYPE,
ep.OWNERKEY,
ug.USER_GROUPNAME,
GROUP_CONCAT(CONCAT(u.FIRSTNAME, ' ', u.LASTNAME, ' (', u.USERNAME, ')') ORDER BY u.LASTNAME SEPARATOR ', ') AS 'USERNAMES'
FROM
securitysystems ss
JOIN
endpoints ep ON ss.SYSTEMKEY = ep.SECURITYSYSTEMKEY
LEFT JOIN
user_groups ug ON ep.OWNERKEY = ug.USERGROUPKEY
LEFT JOIN
usergroup_users uu ON ug.USERGROUPKEY = uu.USER_GROUPKEY
LEFT JOIN
users u ON uu.USERKEY = u.USERKEY
WHERE
AUTOMATEDPROVISIONING = 'false'
GROUP BY
ss.DISPLAYNAME, ep.ENDPOINTNAME, ep.OWNERTYPE, ep.OWNERKEY, ug.USER_GROUPNAME;
Email templet format :
<!DOCTYPE html>
<html>
<head>
<title>Company</title>
<style> table { border-collapse: collapse; border: 1px solid black; width: 200%; } th, td { border: 1px solid black; padding: 10px; } th { background-color: #87CEFA; text-align: center; color: black; font-size: 18px } td { text-align: center; } </style>
</head>
<body> <p>Hello Team,
<br></br> This is the reminder email to uplaod accounts for the dissconnecte application for the month ${Calendar.getInstance().getTime().format('yyyy-MM-dd')}.<br> </p>
<table>
<tr>
<th>SI No</th>
<th>Application</th>
</tr>
<%int cnt=Integer.parseInt("${ANALYTICSDATA.USERNAME.size()}"); for(int i=0;i<cnt;i=i+1){%> <tr>
<td >${ANALYTICSDATA.'SI No'[i]}</td>
<td>${ANALYTICSDATA.'Application'[i]}</td>
</td></tr><%}%> </table> <br><p><b>Note:</b> If there is any issue observed on any of the above listed KPI it will be fixed as part of standard Change process and communicated on Support teams group. </p> <p>Regards,<br> XXXX Team</p> </body> </html>
Using above Query & Email template it's giving all disconnected application names and Owners but i need to send separate email to application owners along with their application name to print .
Please provide any possible solution.
Thanks & Regards,
Nikesh Polineni
