Saviynt Forums

Deepu · ‎04/25/2024

Hello,

We are getting error when we try to send emails to managers about their subordinates through analytics

below query iam using

SELECT
mgr.username AS username,
mgr.firstname as 'ManagerFirstName',
mgr.email AS ManagerEmail,
usr.username AS UserUsername,
usr.DISPLAYNAME AS UserDisplayName,
usr.enddate AS UserEndDate
FROM USERS mgr
INNER JOIN USERS usr ON mgr.USERKEY = usr.MANAGER
WHERE usr.EMPLOYEETYPE = 'contractor'
AND usr.STATUSKEY = 1
AND usr.enddate IS NOT NULL
AND DATEDIFF(usr.enddate, CURDATE()) IN (14, 7, 6, 5, 4, 3, 2 ,1)
AND NOT EXISTS (
SELECT 1
FROM user_accounts UA
JOIN accounts A ON UA.accountkey = A.accountkey
JOIN endpoints E ON A.endpointkey = E.endpointkey
WHERE UA.userkey = usr.userkey
AND E.DISPLAYNAME = 'Active Directory'
)

below email template iam using

To:-${userEmail}
Body:-

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Contract Extension or Termination Instructions</title>
<style>
body {
font-family: Arial, sans-serif;
font-size: 14px;
}
table {
border-collapse: collapse;
width: 100%;
}
th, td {
border: 1px solid #dddddd;
text-align: left;
padding: 8px;
}
th {
background-color: #f2f2f2;
}
a {
color: #007bff;
text-decoration: none;
}
a:hover {
text-decoration: underline;
}
</style>
</head>
<body>
<p>Hello ${ANALYTICSDATAMAP.get("ManagerFirstName")},</p>
<p>You are listed as the manager for the user below. The Saviynt profile will expire on ${ANALYTICSDATAMAP.get("UserEndDate")}. Once the Saviynt profile expires, the user will no longer be able to log into Saviynt profile and underlying access will be removed.</p>
<table>
<tr>
<th colspan="2">Reportee Details</th>
</tr>
<tr>
<td>Display Name:</td>
<td>${ANALYTICSDATAMAP.get("UserDisplayName")}</td>
</tr>
<tr>
<td>Username:</td>
<td>${ANALYTICSDATAMAP.get("UserUsername")}</td>
</tr>
<tr>
<td>Saviynt Profile End Date:</td>
<td>${ANALYTICSDATAMAP.get("UserEndDate")}</td>
</tr>
</table>

<p>Please follow the instructions below to extend or Terminate the user.</P>
<table>
<tr>
<th>Step</th>
<th>Action</th>
<th>Details</th>
</tr>
<tr>
<td>1</td>
<td>Navigate to Saviynt Portal</a></td>
<td>Click on the 3 lines menu at the top left.</td>
</tr>
<tr>
<td>2</td>
<td>Select User Management</td>
<td>Choose "User Management" from the menu options.</td>
</tr>
<tr>
<td>3</td>
<td>Update User Request</td>
<td>Select "Update User Request" from the available options.</td>
</tr>
<tr>
<td>4</td>
<td>Search for User</td>
<td>Search the user using their username or Last Name and click next.</td>
</tr>
<tr>
<td>5</td>
<td>Select User</td>
<td>Choose the relevant user from the search results and click next.</td>
</tr>
<tr>
<td>6</td>
<td>Choose Action</td>
<td>From the dropdown, select what you would like to do:<br>
- <strong>Extend:</strong> Enter the extension date, no more than 90 days from the current date.<br>
- <strong>Terminate:</strong> click on this option to terminate the user.
</td>
</tr>
<tr>
<td>7</td>
<td>Submit</td>
<td>Click on "Submit" to finalize the update.</td>
</tr>
</table>
<br>
<p>For more detailed information, refer to our Knowledge Base Article</a>.</p>
<p>Best regards,<br>
RCP Identity and Access Management Team<br>

</p>
</body>
</html>

Error:-

2024-04-25T08:51:00-05:00-ecm-worker-services.EmailerService-quartzScheduler_Worker-7-DEBUG-delegatesOfUser---[]

2024-04-25T08:51:00-05:00-ecm-worker-services.EmailerService-quartzScheduler_Worker-7-DEBUG-assigneeDelegateEmailQry---select u from Users u, Delegates del where u.id=del.delegateuserkey and del.userkey=2203 and del.startdate <= '2024-04-25 13:51:00' and del.enddate >= '2024-04-25 13:51:00' and u.statuskey = 1

2024-04-25T08:51:00-05:00-ecm-worker-services.EmailerService-quartzScheduler_Worker-7-DEBUG-delegatesOfUser---[]

2024-04-25T08:51:00-05:00-ecm-worker-services.EmailerService-quartzScheduler_Worker-7-DEBUG-assigneeDelegateEmailQry---select u from Users u, Delegates del where u.id=del.delegateuserkey and del.userkey=15240 and del.startdate <= '2024-04-25 13:51:00' and del.enddate >= '2024-04-25 13:51:00' and u.statuskey = 1

2024-04-25T08:51:00-05:00-ecm-worker-services.EmailerService-quartzScheduler_Worker-7-DEBUG-delegatesOfUser---[]

2024-04-25T08:51:00-05:00-ecm-worker-services.EmailerService-quartzScheduler_Worker-7-DEBUG-assigneeDelegateEmailQry---select u from Users u, Delegates del where u.id=del.delegateuserkey and del.userkey=15493 and del.startdate <= '2024-04-25 13:51:00' and del.enddate >= '2024-04-25 13:51:00' and u.statuskey = 1

2024-04-25T08:51:00-05:00-ecm-worker-services.EmailerService-quartzScheduler_Worker-7-DEBUG-delegatesOfUser---[]

2024-04-25T08:51:00-05:00-ecm-worker-services.EmailerService-quartzScheduler_Worker-7-DEBUG-jasperFilePath-/saviynt_shared/reports

2024-04-25T08:51:00-05:00-ecm-worker-services.EmailerService-quartzScheduler_Worker-7-DEBUG-Getting null from address, skipping send email

2024-04-25T08:51:00-05:00-ecm-worker-services.EmailerService-quartzScheduler_Worker-7-DEBUG-inide getEmailData

2024-04-25T08:51:00-05:00-ecm-worker-services.EmailerService-quartzScheduler_Worker-7-DEBUG-Exit getEmailData

2024-04-25T08:51:00-05:00-ecm-worker-services.EmailerService-quartzScheduler_Worker-7-DEBUG-Called sendEmail without the MailConfig, finding the MailConfig

2024-04-25T08:51:00-05:00-ecm-worker-services.EmailerService-quartzScheduler_Worker-7-DEBUG-Before calling getLimiter

2024-04-25T08:51:00-05:00-ecm-worker-services.EmailerService-quartzScheduler_Worker-7-DEBUG-Picking email.delay.seconds from externalconfig[:]

2024-04-25T08:51:00-05:00-ecm-worker-services.EmailerService-quartzScheduler_Worker-7-DEBUG-Rate Limit final value: null

2024-04-25T08:51:00-05:00-ecm-worker-services.EmailerService-quartzScheduler_Worker-7-DEBUG-After calling getLimiter

2024-04-25T08:51:00-05:00-ecm-worker-services.EmailerService-quartzScheduler_Worker-7-DEBUG-Calling the sendEmail with the mailConfig - <smtpconfig>
2024-04-25T08:35:20-05:00-ecm-worker-analytics.AnalyticsESService-Thread-903-DEBUG-Column 'USERNAME' Not Found

Thanks,
Deepu

EDIT TO ADD:

2024-04-25T10:24:00-05:00-ecm-worker-services.EmailerService-quartzScheduler_Worker-7-DEBUG-Before calling getLimiter

2024-04-25T10:24:00-05:00-ecm-worker-services.EmailerService-quartzScheduler_Worker-7-DEBUG-Picking email.delay.seconds from externalconfig[:]

2024-04-25T10:24:00-05:00-ecm-worker-services.EmailerService-quartzScheduler_Worker-7-DEBUG-Rate Limit final value: null

2024-04-25T10:24:00-05:00-ecm-worker-services.EmailerService-quartzScheduler_Worker-7-DEBUG-After calling getLimiter

2024-04-25T10:24:00-05:00-ecm-worker-services.EmailerService-quartzScheduler_Worker-7-DEBUG-Enter initializeEmailOauth

2024-04-25T10:24:00-05:00-ecm-worker-services.EmailerService-quartzScheduler_Worker-7-DEBUG-Entered initializeAuthForEmail

2024-04-25T10:24:00-05:00-ecm-worker-services.EmailerService-quartzScheduler_Worker-7-DEBUG-Setting content type as HTML

2024-04-25T10:24:00-05:00-ecm-worker-services.EmailerService-quartzScheduler_Worker-7-DEBUG-Getting null from address

2024-04-25T10:24:00-05:00-ecm-worker-services.EmailerService-quartzScheduler_Worker-7-DEBUG-Send to delegate flag = true

2024-04-25T10:24:00-05:00-ecm-worker-services.EmailerService-quartzScheduler_Worker-7-DEBUG-jasperFilePath-/saviynt_shared/reports

2024-04-25T10:24:00-05:00-ecm-worker-services.EmailerService-quartzScheduler_Worker-7-DEBUG-Getting null from address, skipping send email

2024-04-25T10:24:00-05:00-ecm-worker-services.EmailerService-quartzScheduler_Worker-7-DEBUG-inide getEmailData

2024-04-25T10:24:00-05:00-ecm-worker-services.EmailerService-quartzScheduler_Worker-7-DEBUG-Exit getEmailData

2024-04-25T10:24:00-05:00-ecm-worker-services.EmailerService-quartzScheduler_Worker-7-DEBUG-Called sendEmail without the MailConfig, finding the MailConfig

2024-04-25T10:24:00-05:00-ecm-worker-services.EmailerService-quartzScheduler_Worker-7-DEBUG-Before calling getLimiter

2024-04-25T10:24:00-05:00-ecm-worker-services.EmailerService-quartzScheduler_Worker-7-DEBUG-Picking email.delay.seconds from externalconfig[:]

2024-04-25T10:24:00-05:00-ecm-worker-services.EmailerService-quartzScheduler_Worker-7-DEBUG-Rate Limit final value: null

2024-04-25T10:24:00-05:00-ecm-worker-services.EmailerService-quartzScheduler_Worker-7-DEBUG-After calling getLimiter

2024-04-25T10:24:00-05:00-ecm-worker-services.EmailerService-quartzScheduler_Worker-7-DEBUG-Calling the sendEmail with the mailConfig - <smtpconfig>

Raghu · ‎04/25/2024

@Deepu

try like below

sql:

SELECT
mgr.username AS username,
mgr.firstname as 'ManagerFirstName',
mgr.email AS ManagerEmail,
usr.username AS UserUsername,
usr.DISPLAYNAME AS UserDisplayName,
usr.enddate AS UserEndDate,
mgr.usernname
FROM USERS mgr
INNER JOIN USERS usr ON mgr.USERKEY = usr.MANAGER
WHERE usr.EMPLOYEETYPE = 'contractor'
AND usr.STATUSKEY = 1
AND usr.enddate IS NOT NULL
AND DATEDIFF(usr.enddate, CURDATE()) IN (14, 7, 6, 5, 4, 3, 2 ,1)
AND NOT EXISTS (
SELECT 1
FROM user_accounts UA
JOIN accounts A ON UA.accountkey = A.accountkey
JOIN endpoints E ON A.endpointkey = E.endpointkey
WHERE UA.userkey = usr.userkey
AND E.DISPLAYNAME = 'Active Directory' '
)

To:-${userEmail}

cc: youmail xyz@gmail.com

body:

!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Contract Extension or Termination Instructions</title>
<style>
body {
font-family: Arial, sans-serif;
font-size: 14px;
}
table {
border-collapse: collapse;
width: 100%;
}
th, td {
border: 1px solid #dddddd;
text-align: left;
padding: 8px;
}
th {
background-color: #f2f2f2;
}
a {
color: #007bff;
text-decoration: none;
}
a:hover {
text-decoration: underline;
}
</style>
</head>
<body>
<p>Hello ${ANALYTICSDATAMAP.'ManagerFirstName'[0]}</p>
<p>You are listed as the manager for the user below. The Saviynt profile will expire on ${ANALYTICSDATAMAP.'UserEndDate[0]}. Once the Saviynt profile expires, the user will no longer be able to log into Saviynt profile and underlying access will be removed.</p>
<table>
<tr>
<th colspan="2">Reportee Details</th>
</tr>
<tr>
<td>Display Name:</td>
<td>${ANALYTICSDATAMAP.'UserDisplayName'[0])}</td>
</tr>
<tr>
<td>Username:</td>
<td>${ANALYTICSDATAMAP.'UserUsername'[0]}</td>
</tr>
<tr>
<td>Saviynt Profile End Date:</td>
<td>${ANALYTICSDATAMAP.'UserEndDate'[0]}</td>
</tr>
</table>

<p>Please follow the instructions below to extend or Terminate the user.</P>
<table>
<tr>
<th>Step</th>
<th>Action</th>
<th>Details</th>
</tr>
<tr>
<td>1</td>
<td>Navigate to Saviynt Portal</a></td>
<td>Click on the 3 lines menu at the top left.</td>
</tr>
<tr>
<td>2</td>
<td>Select User Management</td>
<td>Choose "User Management" from the menu options.</td>
</tr>
<tr>
<td>3</td>
<td>Update User Request</td>
<td>Select "Update User Request" from the available options.</td>
</tr>
<tr>
<td>4</td>
<td>Search for User</td>
<td>Search the user using their username or Last Name and click next.</td>
</tr>
<tr>
<td>5</td>
<td>Select User</td>
<td>Choose the relevant user from the search results and click next.</td>
</tr>
<tr>
<td>6</td>
<td>Choose Action</td>
<td>From the dropdown, select what you would like to do:<br>
- <strong>Extend:</strong> Enter the extension date, no more than 90 days from the current date.<br>
- <strong>Terminate:</strong> click on this option to terminate the user.
</td>
</tr>
<tr>
<td>7</td>
<td>Submit</td>
<td>Click on "Submit" to finalize the update.</td>
</tr>
</table>
<br>
<p>For more detailed information, refer to our Knowledge Base Article</a>.</p>
<p>Best regards,<br>
RCP Identity and Access Management Team<br>

</p>
</body>
</html>

Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

rushikeshvartak · ‎04/25/2024

Its seems manager email is not valid.
Please add your email in CC / BCC and validate

Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

Deepu · ‎05/01/2024

Thanks @Raghu & @rushikeshvartak for you response.

My Query is working when I unchecked the option in Global configuration.

Thanks,

Deepu

Raghu · ‎05/01/2024

@Deepu if going to use usercontext object it will separate mail sent every users based on above enable option.

anyway its working right ,Great please accept and close if dont have any issue.

Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

Saviynt Forums

Analytics email to managers about their subordinates