cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

MS ACCESS QUERY FOR SSL CERTIFICATE EXPIRATION

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:
pastedImage_0.png pastedImage_1.png

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;

pastedImage_0.png

Labels (3)
Comments

Its not working.

I have rewritten the code, please see the revision and test. It should be in working conditions now. Please let me know, thank you !

Version history
Revision #:
1 of 1
Last update:
‎08-08-2018 06:24 PM
Updated by: