NPM: 12.2
For those of you who want to know node SSL Certificate expiration dates in your environment, this is how I found SSL Certificate expiration dates for nodes using Microsoft Access. This assumes you have set up SSL Certificate Application/Component monitoring already on nodes of your choice. The table names and columns should be the same across SQL server, MS Acess SQL, and SWQL. To use it in the different areas, you will just need to modify the syntax.
Please have the following tables mapped in MS Acess:
In this example, I have only set up SSL Monitoring on Cisco devices so only Cisco devices are showing.
SELECT dbo_Nodes.NodeID, dbo_Nodes.IP_Address, dbo_Nodes.Caption, dbo_APM_AlertsAndReportsData.ComponentStatus, IIf(dbo_APM_AlertsAndReportsData.StatisticData,dbo_APM_AlertsAndReportsData.StatisticData,Null) AS [Days Remaining], IIf(dbo_APM_AlertsAndReportsData.ComponentMessage,Mid(dbo_APM_AlertsAndReportsData.ComponentMessage,InStr(dbo_APM_AlertsAndReportsData.ComponentMessage,":")+1,InStr(dbo_APM_AlertsAndReportsData.ComponentMessage,"/")),Null) AS [Expiration Date], dbo_Nodes.Vendor, dbo_Nodes.MachineType, dbo_Nodes.IOSImage, dbo_Nodes.IOSVersion, dbo_APM_AlertsAndReportsData.ComponentName
FROM dbo_Nodes LEFT JOIN dbo_APM_AlertsAndReportsData ON dbo_Nodes.NodeID = dbo_APM_AlertsAndReportsData.NodeId
WHERE (((dbo_APM_AlertsAndReportsData.ComponentName) Like "*SSL Cert*"))
ORDER BY IIf(dbo_APM_AlertsAndReportsData.StatisticData,dbo_APM_AlertsAndReportsData.StatisticData,Null), IIf(dbo_APM_AlertsAndReportsData.StatisticData,dbo_APM_AlertsAndReportsData.StatisticData,dbo_APM_AlertsAndReportsData.ComponentStatus) DESC;
