How to be notified of devices SSL cert expiry in Device42 using Insights+ reporting engine

Insights+ alerts can be used to notify you via email when SSL certificates are about to expire.

Here are the details steps required:

• You need to create an Insights+ Dataset 

• using this DOQL query:

select d.name "Device Name", dc.fqdn "FQDN",  dc.ssl_version "SSL Version", dc.valid_from "Valid From",  dc.valid_to "Valid To"
 
from view_devicecertificate_v1 dc
left join view_device_v2 d on d.device_pk = dc.device_fk

where dc.valid_to  BETWEEN now() AND (now() + '30 days'::interval)

This will notify you of certificates that are set to expire within the next 30 days. The number of days can be adjusted as needed.

• Then save with any required name

• Create a Chart using that dataset with any desired name
  

and here is the final view

• Create an alert using that Chart

• here you need to select the chart and insert the email addresses needed to have the alert sent to and a condition to trigger the alert:

• the alert query:

select count (*)
 
from view_devicecertificate_v1 dc
left join view_device_v2 d on d.device_pk = dc.device_fk

where dc.valid_to  BETWEEN now() AND (now() + '30 days'::interval)

If the result is greater than 0, it means there are certificates set to expire within the next 30 days, and the report will be sent to the configured email addresses.