Email notification to manager

[Ekata]

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

[smitg]

Hi @Ekata ,

This can be achieved via analytics.

https://forums.saviynt.com/t5/identity-governance/need-to-send-emails-to-managers-who-reportees-have...

Thanks,
Smitha

[naveenss]

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

[Ekata]

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?

[pmahalle]

Hi @Ekata ,

That's how it works. Can you share you analytics query.

[Ekata]

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';

[pmahalle]

Hi @Ekata which expression you are using to get email in email template under To?

[Ekata]

Its ${userEmail}

[pmahalle]

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.

[dgandhi]

Can you enable below config and try?

[DixshantValecha]

Hi @Ekata,

As @pmahalle told that's how it works. but if possible you can share you analytics query.

[naveenss]

@Ekata  please share the analytics query you're using. 

[Ekata]

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?

[naveenss]

@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!

[Ekata]

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.

[Ekata]

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?