use OOTB report and configuration.
Query :
select u.username, u.firstname, u.lastname, ss.alias, ss.valid_to, ec.connectionname from users u, externalconnection ec, sav_sslcert ss, user_savroles us, savrole_permission sp where DATEDIFF(VALID_TO, CURRENT_TIMESTAMP()) < (select configdata from configuration where name='DAYS_BEFORE_CERTIFICATE_EXPIRY') AND DATEDIFF(VALID_TO, CURRENT_TIMESTAMP()) > 0 AND ec.sslcertificate=ss.cert_key AND sp.accesskey=ec.externalconnectionkey AND us.rolekey=sp.savrolekey AND u.userkey=us.userkey UNION select DISTINCT u.username, u.firstname, u.lastname, ss.alias, ss.valid_to, '' as connectionname from users u, sav_sslcert ss, externalconnection ec where userkey in (select userkey from user_savroles where rolekey in (select rolekey from savroles where rolename='ROLE_ADMIN')) AND cert_key NOT IN ( select cert_key from sav_sslcert ss, externalconnection ec where ss.cert_key=ec.sslcertificate ) AND DATEDIFF(VALID_TO, CURRENT_TIMESTAMP()) < (select configdata from configuration where name='DAYS_BEFORE_CERTIFICATE_EXPIRY') AND DATEDIFF(VALID_TO, CURRENT_TIMESTAMP()) > 0;
Subject : Expiration Notification for SSL Certificate(s) ${ANALYTICSDATA.alias}
Body :
<!DOCTYPE html> <html> <head> <style> table { font-family: arial, sans-serif; border-collapse: collapse; width: 100%; } td, th { border: 1px solid #dddddd; text-align: left; padding: 8px; } tr:nth-child(even) { background-color: #dddddd; } </style> </head> <body> Hi ${ANALYTICSDATA.firstname[0]} ${ANALYTICSDATA.lastname[0]},<br><br> The below mentioned SSL Certificates are going to expire soon. Please update the certificates to aviod any connection disruption:<br><br> <table> <tr><th>Certificate Name</th><th>Certificate Expiry Date</th><th>Connection Name</th></tr> <% int count=Integer.parseInt("${ANALYTICSDATA.alias.size()}"); for(int i=0;i<count;i=i+1){%><tr><td>${ANALYTICSDATA.alias[i]}</td><td>${ANALYTICSDATA.valid_to[i]}</td><td>${ANALYTICSDATA.connectionname[i]}</td></tr> <% } %> </table> <br><br> Regards,<br> Saviynt Admin Team <br> </body> </html>
