Certificate Expiration Notification v8.1.02

The following article covers how to setup notifications for upcoming certificate expirations.  This solution will report which client certificates are due to expire in the next 30 days and the FIP in which they reside as well as which trusted certificates (under tasks > manage certificates) are due to expire in the same time frame.

Step 1
The below link to GitHub provides the two files you will need.  The first is a Layer 7 policy (xml file) for you to import into your gateway at the service url /parse_certs .  This service will accept the data pulled from the database by the shell script and craft the email message to be sent to the gateway administrators.

https://github.com/Benjaneer/Layer7

Step 2
Once you have the policy in your service it needs to be updated on line 43 Send Email Alert assertion.  The host, from, and to need to be set according to you environment.  Nothing else in the policy should require any changes for this solution to work.

Step 3

The shell script (also on GitHub) should be setup on your primary or backup database gateway node and can be scheduled using crontab (either one, not both).  The request is routed into the gateway via localhost port 8080.  This requires that port 8080 not be disabled, though it need not be accessible from anywhere else.  If you prefer you can route to 8443 but will have to resolve the server certificate hostname mismatch.  For other versions of the gateway the database schema may have changed and therefore the query in this script will need to be changed accordingly.  You should also ensure that the 'maximum message size' limit for the 8080 listen port advanced setting is not preventing the message from being received.  To change the port or hostname used by the shell script to send the data into the policy update the curl command url.

Step 4
Use chmod to make the shell script executable on the gateway.

Step 5

Test!

Step 6

Setup a cron job to periodically run the script and alert you of upcoming certificate expirations.  In the shell script (shown below) you can change the days parameter to adjust how far out expirations should be reported on.

mysql -X -e "select c.user_id as uid,concat('FIP:',p.name) as repository,case when u.name is null then c.login else u.name end as name,c.cert as cert from ssg.client_cert as c left join ssg.identity_provider as p on c.provider = p.goid left join ssg.fed_user as u on unhex(c.user_id) = u.goid union select hex(goid) as uid,'Trusted Certificate' as repository,name,cert_base64 as cert from ssg.trusted_cert;" > check.xml
curl -X POST -d @check.xml -H "Content-Type: application/xml; charset=utf-8" http://localhost:8080/parse_certs?days=30
rm -f check.xml