Emergency Access Role Expiry Notifiction

[Kavya_C]

Hi Team , 

I have a requirement where I need to trigger the email notification for Role User for Role Expiration . 

Suppose the Emergency access Role is getting expired in next 10 days can we have notification email triggered to Role User  to indicate that Role is getting expired.

How to achieve that ?

Thanks 
Kavya

[pmahalle]

Hi @Kavya_C ,

yes it is achievable, you can create analytics report with query to get all the users whose role getting expired in 10 days.

Make sure to schedule analytics report everyday.

[Kavya_C]

Hi pmahalle

Thank you for your response

Yes, via analytical report we can achieve but all the users will be receiving other user role access information as well, but we need to send notification to individual user with their role access and there end date only in the email 

Is this possible?

[pmahalle]

@Kavya_C , Yes it’s possible.

1. Make sure to select username in the analytics query

2. In email template user ${userName} in To field.

3. Enable the configuration Group emails by username in Global configurations

[Kavya_C]

Hi

I followed the above steps but how ever  ${userName} is giving an error in the  logs  and also I tried 

${user?.email} ${user?.email} ${users.email}

2023-07-27/14:51:12.202 [{}] [https-jsse-nio-443-exec-38]  DEBUG analytics.AnalyticsESService - Error in notifyEmailToOwner::
groovy.lang.MissingPropertyException: No such property: userName for class: SimpleTemplateScript918
    at SimpleTemplateScript918.run(SimpleTemplateScript918.groovy:1)
    at com.saviynt.ecm.analytics.AnalyticsESService.notifyEmailToOwner(AnalyticsESService.groovy:2875)
    at com.saviynt.ecm.analytics.AnalyticsESService.emailAction(AnalyticsESService.groovy:15788)
    at com.saviynt.ecm.analytics.AnalyticsESService.runControl(AnalyticsESService.groovy:14010)
    at

[pmahalle]

@Kavya_C , Share your analytics query. Also you attached email template in analytics, right?

[Kavya_C]

Hi

Please find the query below used in analytical report

select u.username, u.firstname, u.lastname, a.name as 'Account Name',r.role_name as 'Role Name', r.description as 'Description', rua.enddate as 'Role End Date'
from users u, roles r, role_user_account rua, accounts a
where u.USERKEY=rua.USERKEY
and r.ROLEKEY=rua.ROLEKEY
and a.ACCOUNTKEY=rua.ACCOUNTKEY
and rua.ENDDATE is not null
and r.STATUS=1
and u.STATUSKEY=1
and a.STATUS in ('1','Active','Manually Provisioned')
and DATEDIFF(ae1.ENDDATE,sysdate()) between 0 AND (select configdata from configuration where name = 'NUMBEROFDAYSBEFOREROLEEXPIRYDATE\r
order by username;

Attached Email : Yes

Enabled in Global Config : Yes

[pmahalle · Accepted solution]

@Kavya_C ,

Is this query working fine and you are getting desired result while you preview the analytics report? 

One another thing, whatever users you are getting the report result should have proper working email ids, because email send on the same email id.

Also try below query once:
select u.username as username, u.firstname, u.lastname, a.name as 'Account Name',r.role_name as 'Role Name', r.description as 'Description', rua.enddate as 'Role End Date'

from users u, roles r, role_user_account rua, accounts a
where u.USERKEY=rua.USERKEY
and r.ROLEKEY=rua.ROLEKEY
and a.ACCOUNTKEY=rua.ACCOUNTKEY
and rua.ENDDATE is not null
and r.STATUS=1
and u.STATUSKEY=1
and a.STATUS in ('1','Active','Manually Provisioned')
and DATEDIFF(ae1.ENDDATE,sysdate()) between 0 AND (select configdata from configuration where name = 'NUMBEROFDAYSBEFOREROLEEXPIRYDATE' order by username;

[rushikeshvartak · Accepted solution]

select u.username as USERNAME, u.firstname, u.lastname, a.name as 'Account Name',r.role_name as 'Role Name', r.description as 'Description', rua.enddate as 'Role End Date'
from users u, roles r, role_user_account rua, accounts a
where u.USERKEY=rua.USERKEY
and r.ROLEKEY=rua.ROLEKEY
and a.ACCOUNTKEY=rua.ACCOUNTKEY
and rua.ENDDATE is not null
and r.STATUS=1
and u.STATUSKEY=1
and a.STATUS in ('1','Active','Manually Provisioned')
and DATEDIFF(ae1.ENDDATE,sysdate()) between 0 AND (select configdata from configuration where name = 'NUMBEROFDAYSBEFOREROLEEXPIRYDATE\r
order by username;

make sure your report query having USERNAME as column name case matters.

and email To should only have ${userEmail}

if you need additional email keep in cc / bcc

[naveenss · Accepted solution]

@Kavya_C  the TO field in the email template should be ${userEmail}