Hi everyone,
I've had a request from our MS SQL Database support team, to be notified when a node's SQL Instance Certificate was about to expiry and email a report to them every weekly of pending certificate expiration's.
Here is my PowerShell script that gathers some information which I think help identify what is being used for a SQL Instance to enable you to monitor, alert and report upon.
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 SQL server certificate on database server.
Poll Systems for Certificates to alert SQL_Cert_Expires_Days "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 SQL Instance to check in the Script Arguments section.
i.e. MSSQLServer
*******
Credential for monitoring: <Inherit from template>
Script Arguments: <The Name of the SQL Server Instance>
Execution Mode: Remote Host
Run the script under specified account: option ticked.
$SQLInstance = $args
try
{
# Obtain the UK Culture\Language Setting
[System.Object]$objCulture = New-Object system.globalization.cultureinfo('en-GB')
# get the current UK Date time stamp
[System.Object]$dtFullUKDateTimeStamp = Get-Date -format ($objCulture.DateTimeFormat.FullDateTimePattern)
[System.Array]$certificates = Get-ChildItem -Path CERT:\LOCALMACHINE -Recurse
[System.String]$strMessage = ""
[System.Int32]$iSWStatistic = 0
[System.Object]$objSQLInstValue = Get-ItemPropertyValue -Path 'Registry::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL' -Name $SQLInstance
# DBA's set the registry key here for SQL to pick up the certificate we can use this to search the cert store
[System.String]$strRegSQLInstCert = 'Registry::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\{0}\MSSQLServer\SuperSocketNetLib' -f $objSQLInstValue.Trim()
[System.Object]$objSQLInstCertThumbprint = Get-ItemProperty -Path $strRegSQLInstCert -Name Certificate
If ($null -eq $objSQLInstCertThumbprint.Certificate)
{
Write-Host ("Message.SQL_Instance: Unable to find a validate SQL SSL certificate for instance {0}" -f $SQLInstance)
Write-Host "Statistic.SQL_Instance: 0"
Write-Host "Message.SQL_Cert_Expires_Days: 0"
Write-Host "Statistic.SQL_Cert_Expires_Days: 0"
}
else
{
# Match the thumbprint in the registry to the certificate in the store.
$objSQLCertDets = ($certificates | Where Thumbprint -eq $objSQLInstCertThumbprint.Certificate)
[System.DateTime] $expirydate = (Get-Date ($objSQLCertDets.NotAfter) -format ($objCulture.DateTimeFormat.FullDateTimePattern))
[int] $iDateDiff = (New-TimeSpan -End $expirydate).Days
$SQLCertExpiryDate = (Get-date $expirydate -format ($objCulture.DateTimeFormat.FullDateTimePattern))
# Match the thumbprint in the registry to the certificate in the store.
$objSQLCertDets = ($certificates | Where Thumbprint -eq $objSQLInstCertThumbprint.Certificate)
[System.DateTime] $expirydate = (Get-Date ($objSQLCertDets.NotAfter) -format ($objCulture.DateTimeFormat.FullDateTimePattern))
[int] $iDateDiff = (New-TimeSpan -End $expirydate).Days
$SQLCertExpiryDate = (Get-date $expirydate -format ($objCulture.DateTimeFormat.LongDatePattern))
if ([string]::IsNullorEmpty($objSQLCertDets.FriendlyName))
{
$SQLCertFriendlyName = "<None>"
}
else
{
$SQLCertFriendlyName = $objSQLCertDets.FriendlyName
}
if ([string]::IsNullorEmpty($objSQLCertDetsl.Subject))
{
$SQLCertSubject = "<None>"
}
else
{
$SQLCertSubject = $objSQLCertDetsl.Subject
}
$SQlCertIssuer = $objSQLCertDets.Issuer
$SQLCertThumbprint = $objSQLCertDets.Thumbprint
$SQlCertIssuer = $objSQLCertDets.Issuer
$SQLCertThumbprint = $objSQLCertDets.Thumbprint
Write-Host ("Message.SQL_Instance: {0}" -f $SQLInstance)
Write-Host "Statistic.SQL_Instance: 0"
Write-Host ("Message.SQL_Cert_Expires_Days: {0}" -f $iDateDiff)
Write-Host ("Statistic.SQL_Cert_Expires_Days: {0}" -f $iDateDiff)
Write-Host ("Message.SQL_Cert_Expiry_Date: {0}" -f $SQLCertExpiryDate)
Write-Host "Statistic.SQL_Cert_Expiry_Date: 0"
Write-Host ("Message.SQL_Cert_Friendly_Name: {0}" -f $SQLCertFriendlyName)
Write-Host "Statistic.SQL_Cert_Friendly_Name: 0"
Write-Host ("Message.SQL_Cert_Subject: {0}" -f $SQLCertSubject)
Write-Host "Statistic.SQL_Cert_Subject: 0"
Write-Host ("Message.SQL_Cert_Thumbprint: {0}" -f $SQLCertThumbprint)
Write-Host "Statistic.SQL_Cert_Thumbprint: 0"
Write-Host ("Message.SQL_Cert_Issuer: {0}" -f $SQLCertIssuer)
Write-Host "Statistic.SQL_Cert_Issuer: 0"
}
}
catch
{
Write-Host ("Message.SQL_Instance: Unable to find a validate SQL SSL certificate for instance {0}" -f $SQLInstance)
Write-Host "Statistic.SQL_Instance: 0"
Write-Host "Message.SQL_Cert_Expires_Days: 0"
Write-Host "Statistic.SQL_Cert_Expires_Days: 0"
}
When you insert the script, carry out at test on a node that you know has an IIS Cert installed.
You should then have 7x Script Outputs. I remove the "_" (underscores) in the display name but that is a personal preference.
Under the "SQL_Cert_Expires_Days" output set your threshold. we have warning at "Less than or equal to = 35" and critical set to 21.
The statistical data for "SQL_Cert_Expires_Days","SQL Cert FriendlyName","SQL Cert Issuer","SQL Cert Subject","SQL Cert Thumbprint","SQL Instance" is set to 0 (zero) as you are interested in the string values.
"SQL_Cert_Expires_Days"" is the amount of days before the Certificate will expire.
Now thats great, but we also needed a weekly report running, to be more pro-active and renew the certificates before the site expired.
Here is the SWQL code to generate the outputs for a custom table.
SELECT [Node].Caption,
[Node].DetailsUrl,
SQLInstance.InstanceName as [Instance Name],
CertExpiryDays.CertExpiryInDays as [Cert Expires Days],
CertExpiryDate.ExpiryDate as [Cert Expiry Date],
CertThumbprint.SSLThumbPrint as [Cert Thumbprint]
FROM Orion.APM.GenericApplication AS [APM_GA]
JOIN Orion.Nodes [Node] ON [APM_GA].NodeID = [Node].NodeID
JOIN ( Select DISTINCT [APM_MSD_Instance].Component.Application.NodeID,
[APM_MSD_Instance].StringData As [InstanceName]
From Orion.APM.MultipleStatisticData AS [APM_MSD_Instance]
Where [APM_MSD_Instance].Name = 'SQL_Instance') SQLInstance on SQLInstance.NodeID = [APM_GA].NodeID
JOIN ( Select DISTINCT [APM_MSD_ExpiryDays].Component.Application.NodeID,
[APM_MSD_ExpiryDays].NumericData As [CertExpiryInDays]
From Orion.APM.MultipleStatisticData AS [APM_MSD_ExpiryDays]
Where [APM_MSD_ExpiryDays].Name = 'SQL_Cert_Expires_Days') CertExpiryDays on CertExpiryDays.NodeID = [APM_GA].NodeID
JOIN ( Select DISTINCT [APM_MSD_Date].Component.Application.NodeID,
[APM_MSD_Date].StringData As [ExpiryDate]
From Orion.APM.MultipleStatisticData AS [APM_MSD_Date]
Where [APM_MSD_Date].Name = 'SQL_Cert_Expiry_Date') CertExpiryDate on CertExpiryDate.NodeID = [APM_GA].NodeID
JOIN ( Select DISTINCT [APM_MSD_Thumbprint].Component.Application.NodeID,
[APM_MSD_Thumbprint].StringData As [SSLThumbprint]
From Orion.APM.MultipleStatisticData AS [APM_MSD_Thumbprint]
Where [APM_MSD_Thumbprint].Name = 'SQL_Cert_Thumbprint') CertThumbprint on CertThumbprint.NodeID = [APM_GA].NodeID
WHERE [APM_GA].Name = '< ENTER THE APPLICATION MONITOR TEMPLATE NAME'
and CertExpiryDays.CertExpiryInDays <= 60
WITH NOLOCK
in the table layout, I have updated the "Node" option. click on Advanced and in the Add display settings, change to "Details Page Link".
I then change the Sort option to Cert Expires (ascending) and then Node (ascending)
Group by Node
Here are the views you get.
Application Template / Component View
Report View
I also have something very similar for IIS Website Certs .