Click HERE to see how Saviynt Intelligence is transforming the industry. |
07/26/2023 04:19 AM
Hi Team,
Is it possible to send an email to manager in a bulk. For example, if we have a case where 10 users have their end date expiration and the end date expiration email needs to be notified to the manager, where 4 users are having same manager. So can we send only one email to manager having all the 4 users instead of 4 separate emails.
Do we have any query which can help in achieving this.
Thanks, Ekata
07/26/2023 04:30 AM - edited 07/26/2023 04:32 AM
07/26/2023 04:31 AM
Hi @Ekata you can use analytics to achieve this use case. Please refer to the below forum post
Solved: Need to send emails to managers who reportees have... - Saviynt Forums - 23989
07/26/2023 04:39 AM
Hi,
Thanks for the response. This solution is for sending email to manager which is working for me but the manager gets separate emails for each user. I need to send only one email in case manager is same. for example if it is triggered for 5 users and 3 users having manager as Ekata so it should send only 1 email to Ekata with the users details in the file.
Is that possible?
07/26/2023 04:50 AM
Hi @Ekata ,
That's how it works. Can you share you analytics query.
07/26/2023 05:16 AM
HI @pmahalle ,
Below is the analytic I have used which send the emails separately for each user :
SELECT u.USERNAME as 'User Name', u.statuskey, u.firstname as 'First Name', u.lastname as 'Last Name', u.enddate as 'End Date',m.username as username ,m.email AS 'Manager Email' FROM users u INNER JOIN users m ON m.userkey = u.manager WHERE u.statuskey = 1 AND DATE_FORMAT(U.ENDDATE, "%Y-%m-%d") = date_add(CURDATE(),INTERVAL 14 DAY) and u.customproperty25='Global Regional CSS';
07/26/2023 05:37 AM
Hi @Ekata which expression you are using to get email in email template under To?
07/26/2023 05:44 AM
Its ${userEmail}
07/26/2023 06:04 AM
Hi @Ekata ,
Query looks good but can you make sure below configuration is enabled under Global configurations --> Analytics. If not, enabled it and try again.
07/26/2023 05:57 AM
Can you enable below config and try?
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.
07/26/2023 05:17 AM
07/26/2023 05:50 AM
@Ekata please share the analytics query you're using.
08/02/2023 05:34 AM
Hi Team,
The response regarding the Global config worked, however I just wanted to know if we can use the query instead on the global config. setting as it will be applicable for all cases.
Below is the query I used:
SELECT DISTINCT (u.username) as 'User Name', u.statuskey, u.firstname as 'First Name', u.lastname as 'Last Name',u.country, u.enddate as 'End Date',m.username as Managerusername ,m.email AS 'Manager Email' FROM users u INNER JOIN users m ON m.userkey = u.manager WHERE u.statuskey = 1 AND DATE_FORMAT(U.ENDDATE, "%Y-%m-%d") = date_add(CURDATE(),INTERVAL 2 DAY) and u.customproperty25='Global Regional CSS' GROUP BY m.username;
But the issue is, it only picks the 1st user and not all users.
Do we have any syntax error which makes this happen?
08/02/2023 05:48 AM
@Ekata add u.username in the group by condition to fetch all the users for a manager like below.
SELECT DISTINCT (u.username) as 'User Name', u.statuskey, u.firstname as 'First Name', u.lastname as 'Last Name',u.country, u.enddate as 'End Date',m.username as Managerusername ,m.email AS 'Manager Email' FROM users u INNER JOIN users m ON m.userkey = u.manager WHERE u.statuskey = 1 AND DATE_FORMAT(U.ENDDATE, "%Y-%m-%d") = date_add(CURDATE(),INTERVAL 2 DAY) and u.customproperty25='Global Regional CSS' GROUP BY m.username,u.username;
Let me know if this helps!
08/02/2023 06:11 AM
Hi @naveenss thanks for the response. I can see the missing users with this, but email trigger is not happening. I don't get any notification if I use this query. I received it if I do not add u.username but that is not accurate.
08/03/2023 03:46 AM
Hi,
Can we really use GROUP BY SQL condition for the above case. I guess there is conflict if we use both m.username and u.username in query. Due to which I do not receive the email.
Do we have any other option to achieve it?