Hi everyone,
I've found that the default SSL Certificate monitoring is not very good. The information gathered was not very useful when trying to work out which certificate was assigned to which website.
Here is my PowerShell script that gathers some information which I think help identify what is being used for a particular website. Can be used for both Internal and External sites.
In SolarWinds Application Monitoring create a application monitoring template and assign a PowerShell Monitoring component. I've set the monitoring template to poll every day (Polling Frequency: 14400)
Settings Information :-
Component Description:
*******
Checks for the expiry of the SSL Certificates for Sites bound to HTTPS.
Poll Systems for Certificates to alert (Static Threshold) "WARNING" if they will expires in 35 days time and "CRITICAL" if they will expire within 21 days. Leave Response Time Threshold values blank.
Enter the HTTPS: URL (exclude https://)
ie. www.solarwinds.com
or if not the default SSL port 443 add the port number to the end of the main url
www.solarwinds.com:8443
*******
Credential for monitoring: <Inherit from template> or <None> as long as the SolarWinds Monitoring account has external Internet Browsing rights
Script Arguments:
<Site URL> or < Site URL including port num >
as per the description,
Execution Mode: I've got mine set to Local Host
Run the script under specified account: option ticked.
# Version: 1.0.
# PowerShell exit codes to sure they are the correct ones used in SolarWinds. 0 = UP, 1 = Down, 2 = Warning, 3 = Critical, 8 = Unknown
$url = "https://{0}" -f $args
#disabling the cert validation check. This is what makes this whole thing work with invalid certs…
[System.Net.ServicePointManager]::ServerCertificateValidationCallback = {$False}
[System.Net.ServicePointManager]::DefaultConnectionLimit = 1024
# set the default exit code to UP
$iExitCode = "0"
try
{
$objWebReq = [Net.HttpWebRequest]::Create($url)
$objWebReq.Timeout = 1000
$objWebReq.AllowAutoRedirect = $true
try
{
$objWebReq.GetResponse() |Out-Null
}
catch{}
$objRawCert = $objWebReq.ServicePoint.Certificate.Export('cert')
$objCert = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2
$objCert.Import($objRawCert)
[int]$CertExpiresDays = (($objCert.notAfter) - (Get-Date)).Days
$CertExpiryDate = (Get-Date).AddDays($CertExpiresDays).ToString('dd/MM/yyyy')
if([string]::IsNullorEmpty($objCert.FriendlyName.Trim()))
{
$CertFriendlyName = ""
}
else
{
$CertFriendlyName = $objCert.FriendlyName.Trim()
}
if([string]::IsNullorEmpty($objCert.Subject))
{
$CertSubject = ""
}
else
{
$CertSubject = $objCert.Subject
}
if ([system.string]::IsNullorEmpty(([system.string]::Join(", ", $objCert.DnsNameList.Unicode))))
{
$CertSANNames = ""
}
else
{
$CertSANNames = [system.string]::Join(", ", $objCert.DnsNameList.Unicode)
}
$CertThumbprint = $objCert.Thumbprint
$CertIssuer = $objCert.Issuer
$CertPort = $objWebReq.ServicePoint.Address.Port
$CertSiteName = $objWebReq.Address.Host
if ($CertExpiresDays -le 21)
{
# set exit code to Critical
$iExitCode = "3"
}
elseif ($CertExpiresDays -le 35)
{
# set exit code to Warning
$iExitCode = "2"
}
Write-Host ("Message.Web_SiteID: {0}" -f $CertSiteName)
Write-Host ("Statistic.Web_SiteID: {0}" -f "1")
Write-Host ("Message.Site_Port_Binding: {0}" -f $CertPort)
Write-Host ("Statistic.Site_Port_Binding: {0}" -f $CertPort)
Write-Host ("Message.Cert_Expires_Days: {0}" -f $CertExpiresDays)
Write-Host ("Statistic.Cert_Expires_Days: {0}" -f $CertExpiresDays)
Write-Host ("Message.Cert_Expiry_Date: {0}" -f $CertExpiryDate)
Write-Host "Statistic.Cert_Expiry_Date: 0"
Write-Host ("Message.Cert_Friendly_Name: {0}" -f $CertFriendlyName)
Write-Host "Statistic.Cert_Friendly_Name: 0"
Write-Host ("Message.Cert_Subject: {0}" -f $CertSubject)
Write-Host "Statistic.Cert_Subject: 0"
Write-Host ("Message.Cert_SAN_Names: {0}" -f $CertSANNames )
Write-Host "Statistic.Cert_SAN_Names: 0"
Write-Host ("Message.Cert_Thumbprint: {0}" -f $CertThumbprint)
Write-Host "Statistic.Cert_Thumbprint: 0"
Write-Host ("Message.Cert_Issuer: {0}" -f $CertIssuer)
Write-Host "Statistic.Cert_Issuer: 0"
}
Catch
{
$strMessage= "Unable to detect any SSL certificate."
Write-Host ("Message.Site: {0}" -f $strMessage)
$iSWStatistic = 0
Write-Host ("Statistic.Site: {0}" -f $iSWStatistic)
# set exit code to Down
$iExitCode = "1"
}
Exit $iExitCode
Now for the fun part.
here is the SWQL code to gather the information in a report.
SELECT [Node].Caption,
[Node].DetailsUrl,
[Node].CustomProperties.Environment,
[Web_Site_ID].WebSiteID as [Web Site Name],
[Web_Cert_Expiry_Days].WebCertExpiryInDays as [Cert Expires In Days],
CertExpiryDate.ExpiryDate as [Cert Expiry Date],
CertThumbprint.SSLThumbPrint as [Cert Thumbprint],
CertSubject.CSubject As [Cert Subject]
FROM Orion.APM.GenericApplication AS [APM_GA]
JOIN Orion.Nodes [Node] ON [APM_GA].NodeID = [Node].NodeID
JOIN ( Select DISTINCT [APM_WebSite_ID].Component.Application.NodeID,
[APM_WebSite_ID].StringData As [WebSiteID]
From Orion.APM.MultipleStatisticData AS [APM_WebSite_ID]
Where [APM_WebSite_ID].Name = 'Web_SiteID') [Web_Site_ID] on [Web_Site_ID].NodeID = [APM_GA].NodeID
JOIN ( Select DISTINCT [APM_Website_Cert_ExpiryDays].Component.Application.NodeID,
[APM_Website_Cert_ExpiryDays].NumericData As [WebCertExpiryInDays]
From Orion.APM.MultipleStatisticData AS [APM_Website_Cert_ExpiryDays]
Where [APM_Website_Cert_ExpiryDays].Name = 'Cert_Expires_Days') [Web_Cert_Expiry_Days] on [Web_Cert_Expiry_Days].NodeID = [APM_GA].NodeID
JOIN ( Select DISTINCT [APM_Website_Cert_ExpiryDate].Component.Application.NodeID,
[APM_Website_Cert_ExpiryDate].StringData As [ExpiryDate]
From Orion.APM.MultipleStatisticData AS [APM_Website_Cert_ExpiryDate]
Where [APM_Website_Cert_ExpiryDate].Name = 'Cert_Expiry_Date') CertExpiryDate on CertExpiryDate.NodeID = [APM_GA].NodeID
JOIN ( Select DISTINCT [APM_Website_Cert_Thumbprint].Component.Application.NodeID,
[APM_Website_Cert_Thumbprint].StringData As [SSLThumbprint]
From Orion.APM.MultipleStatisticData AS [APM_Website_Cert_Thumbprint]
Where [APM_Website_Cert_Thumbprint].Name = 'Cert_Thumbprint') CertThumbprint on CertThumbprint.NodeID = [APM_GA].NodeID
JOIN ( Select DISTINCT [APM_Website_Cert_Subject].Component.Application.NodeID,
[APM_Website_Cert_Subject].StringData As [CSubject]
From Orion.APM.MultipleStatisticData AS [APM_Website_Cert_Subject]
Where [APM_Website_Cert_Subject].Name = 'Cert_Subject') CertSubject on CertSubject.NodeID = [APM_GA].NodeID
WHERE ([APM_GA].Name = '')
and [Web_Cert_Expiry_Days].WebCertExpiryInDays <= 60
WITH NOLOCK</pre>
