Saviynt Forums

Ekata · ‎09-19-2022

Hi Team,

We have a client requirement to create an analytics for sending password expiry notification.

Below is the template they want to update:

"SUBJECT:

Your privileged account password is expiring soon

Hello,

The password for your privileged account

<ADM_CADM_samaccountname>

will expire on

mm/dd/yy

. To avoid disruption in using the account please change the password as soon as

possible.

As a reminder, your new password must be at least 16 characters and pass phrases are encouraged

(ex. “hammer rain pronto”).

Further guidance on privileged account password best practices can be found

here.

Thank you,

TSG Identity Center of Excellence"

I have updated the email template however the account name is not getting updated and also the password expiry date which should be last password change+90 days.

Kindly assist.

Regards,

Ekata Bharati

rushikeshvartak · ‎09-19-2022

Example 3: Email to inform control owner that account password is about to expire.

You receive a single email with the details of all the accounts and the applications to which those belong.

To: ${userEmail}

CC: ${ownerEmail}

Subject: Your password is about to expire

Body: ${ANALYTICSDATA.'First Name'[0]} ${ANALYTICSDATA.'Last Name'[0]},

The password for the following accounts is going to expire soon.

<table>

<tr><th>Application</th><th>Account name</th><th>Password expiry date</th></tr>

<%int count=Integer.parseint(“${ANALYTICSDATA.Application.size()}”); for(int i=0;i<count;i=i+1){%>

<tr><td>${ANALYTICSDATA.'Application'[i]}</td><td>${ANALYTICSDATA.'Account Name'[i]}</td><td>${ANALYTICSDATA.'Password Expiration Date'[i]}</td></tr><%}%>

</table>

Analytics name: ${analyticsConfig.analyticsName}

Analytics category: ${analyticsConfig.category}

Refer Example 3 : https://saviynt.freshdesk.com/support/solutions/articles/43000622153-managing-email-templates

Regards,

Rushikesh Vartak

Ekata · ‎09-19-2022

Hi Rushikesh,

Thanks for the response, however I just need to add the account name in between and the password expiry date which I am unable to get from this email template. I have already checked it. It is not working as expected. But I can get the report in excel having the details.

Can you please help me out for the same.

Regards,

Ekata

rushikeshvartak · ‎09-19-2022

Can you share analytics query you are using

Regards,

Rushikesh Vartak

rushikeshvartak · ‎09-19-2022

select u.username,u.firstname, u.lastname, e.endpointname as 'Application', ac.name as 'Account Name', date_add(ac.LASTPASSWORDCHANGE,Interval p.expireafter day) as 'Password Expiration Date' from users u, accounts ac, user_accounts ua, securitysystems ss, endpoints e, policyrule p where ac.status in ("Manually Provisioned",1,'Active') and (ac.ACCOUNTTYPE is null or e.ACCOUNTTYPEFORSERVICEACCOUNT not like concat('%',ac.ACCOUNTTYPE,'%')) and ac.LASTPASSWORDCHANGE is not null and u.USERKEY=ua.USERKEY and u.STATUSKEY=1 and ac.ACCOUNTKEY=ua.ACCOUNTKEY and ac.ENDPOINTKEY = e.ENDPOINTKEY and e.SECURITYSYSTEMKEY = ss.SYSTEMKEY and ss.POLICYRULE=p.POLICYRULEKEY and ss.POLICYRULE not in ('') and ss.POLICYRULE is not null and ss.STATUS=1 and e.status=1 and DATEDIFF(date_add(ac.LASTPASSWORDCHANGE,Interval p.expireafter day),sysdate()) BETWEEN 0 and (select configdata from configuration where name='DAYS_TO_NOTIFY_BEFORE_ACC_PWD_EXPIRE');

Regards,

Rushikesh Vartak

Ekata · ‎09-19-2022

Hi,

I am using this:

SELECT
a.name as 'Account Name',
u.username,
u.firstname,
u.lastname,
u.email,
a.lastpasswordchange as 'Last password Change Date',
e.displayname as 'Application Name',
CASE WHEN a.status = 1 THEN 'Active' ELSE a.status END AS 'AccountStatus'
FROM users u, accounts a,user_accounts ua,endpoints e where u.userkey=ua.userkey and ua.accountkey=a.accountkey and a.endpointkey=e.endpointkey and a.name like 'ADM%'
and datediff(date_add(a.LASTPASSWORDCHANGE, interval 90 day),sysdate()) between 0 and 7;

Regards,

Ekata

rushikeshvartak · ‎09-19-2022

Email Template ?

Regards,

Rushikesh Vartak

Ekata · ‎09-19-2022

Hello,

The password for your privileged account ${ANALYTICSDATA.'Account Name'[i]} will expire soon. To avoid distruption in using the account please change the password as soon as possible. As a reminder, your new password must be at least 16 characters and pass phrases are encouraged (ex."hammer rain pronto"). 
Report - ${analyticsConfig.analyticsName} has been generated.

${ANALYTICSDATA}Further guidance on privileged account password best practices can be found <a href="https://XXXXXXXXXXXXXX.sharepoint.com/sites/GlobalTSGSecurity/SitePages/Administrator-Password-Polic..." target="" rel="">(here) Thank you, TSG Identity Center of Excellence </a>

[This post has been edited by a moderator to remove personally identifiable information to abide by the Saviynt Community Terms of Use and Participation Guidelines.]

Ekata · ‎09-19-2022

But ${ANALYTICSDATA.'Account Name'[i]} also does not work

rushikeshvartak · ‎09-19-2022

Hello,

The password for your privileged account <%int count=Integer.parseint(“${ANALYTICSDATA.Application.size()}”); for(int i=0;i<count;i=i+1){%>

${ANALYTICSDATA.'Account Name'[i]}}<%}%> will expire soon. To avoid distruption in using the account please change the password as soon as possible. As a reminder, your new password must be at least 16 characters and pass phrases are encouraged (ex."hammer rain pronto"). 
Report - ${analyticsConfig.analyticsName} has been generated.

${ANALYTICSDATA}Further guidance on privileged account password best practices can be found <a href="https://company.sharepoint.com/sites/GlobalTSGSecurity/SitePages/Administrator-Password-Polic..." target="" rel="">(here) Thank you, TSG Identity Center of Excellence </a>

if ANALYTICSDATA does not work then try ANALYTICSDATAMAP

Regards,

Rushikesh Vartak