SSL certificate expiration monitoring using PowerShell scripts

Question

Hello, 
 I am trying to create a SAM template that will monitor SSL certificates expirations on a group of Windows machines? Some servers are Web servers, and others are SQL Server machines. 
 Are there are PS scripts that can help with this?

SteveK · Answer

Here is what I use on the IIS/Web Server front. 
 Basically it goes through a couple steps: Imports the Web module Builds out an exclusion list (based on your script arguments in the monitor) Retrieves a list of Sites from IIS Uses the sites to build a list of certificates/bindings Then checks the cert store (LocalMachine/My) for expiration date Based off the results, it then calculates a count of certs about to expire within 30 days (warning), certs about to expire wihtin 7 days (critical) and certs that have already expired (critical). The message blocks for each of those will contain the certificates friendly name. I thought about tieing that back to the IIS site name, but that got a little more complicated than I would like the monitor to be. 
 
 In all honesty, you could probably just take the Variables for expiration from my script, as well as tweak the Retrieve Cert info (and everything below) and you would have something for all certs in that LocalMachine/My store. 
 
 #Sanatize Variables
 Clear-Variable Results -ErrorAction SilentlyContinue
 Clear-Variable Sites,Bindings,Certificates -ErrorAction SilentlyContinue
 Clear-Variable WarningCert,CriticalCert,ExpiredCert -ErrorAction SilentlyContinue

#Load Module
 Try {
 Import-Module WebAdministration
 } Catch {
 Write-Host "Message: failed to load WebAdministration module"
 Exit 1
 }

#Build Exclusion List
 If ([String]::IsNullOrWhiteSpace($Args) -eq $False) {
 $Exclusions = $Args
 }

#Set variables for expiration
 $WarningDate = (Get-Date).AddDays(30)
 $CriticalDate = (Get-Date).AddDays(7)

#Retrieve Sites
 Try{
 Switch ([String]::IsNullOrWhiteSpace($Exclusions)) {
 "TRUE" {
 $Sites = Get-ChildItem IIS:\Sites\* -ErrorAction Stop
 Write-Host "Message.ExcludedSites: None"
 Write-Host "Statistic.ExcludedSites: 0"
 }
 "FALSE" {
 $Sites = Get-ChildItem IIS:\Sites\* -ErrorAction Stop | Where-Object {$_.State -eq "Started" -and $_.Name -notin $Exclusions}
 Write-Host "Message.ExcludedSites: $($Exclusions -Join ",")"
 Write-Host "Statistic.ExcludedSites: $(($Exclusions | Measure-Object).count)"
 }
 }
 } Catch {
 Write-Host "Message: failed to retrieve sites"
 exit 1
 }

#Retrieve Bindings
 Try{
 $Bindings = Get-ChildItem IIS:\SSLBindings -ErrorAction Stop | Where-Object {$Sites.Name -contains $_.Sites.Value}
 } Catch {
 Write-Host "Message: failed to retrieve AppPools"
 exit 1
 }

#Retrieve Cert Info
 Try {
 $Certificates = Get-ChildItem CERT:LocalMachine/My -ErrorAction Stop | Where-Object {$Bindings.thumbprint -contains $_.Thumbprint}
 } Catch {
 Write-Host "Message: failed to retrieve certificates"
 }

#Total Cert information
 If ([String]::IsNullOrEmpty($Certificates)) {
 Write-Host "Statistic.TotalCerts: 0"
 Write-Message "Message.TotalCerts: Unable to retrieve certificates"
 Exit 0
 }

 Write-Host "Statistic.TotalCerts: $(($Certificates | Measure-Object).Count)"

#Warning - 30 days
 $WarningCert = $Certificates | Where-Object {$_.NotAfter -lt $WarningDate}

 If ([String]::IsNullOrEmpty($WarningCert)) {
 Write-Host "Statistic.WarningExpiration: 0"
 Write-Host "Message.WarningExpiration: No certs expiring in 30 days"
 } Else {
 Write-Host "Statistic.WarningExpiration: $(($WarningCert | Measure-Object).count)"
 
 If (($WarningCert | Measure-Object).Count -gt 0) {
 Write-Host "Message.WarningExpiration: Certs expiring in 30 days. $($WarningCert.FriendlyName -join ";")"
 } Else {
 Write-Host "Message.WarningExpiration: No certs expiring in 30 days"
 }
 
 }

#Critical - 7 days
 $CriticalCert = $Certificates | Where-Object {$_.NotAfter -lt $CriticalDate}

 If ([String]::IsNullOrEmpty($CriticalCert)) {
 Write-Host "Statistic.CriticalExpiration: 0"
 Write-Host "Message.CriticalExpiration: No certs expiring in 7 days"
 } Else {
 Write-Host "Statistic.CriticalExpiration: $(($CriticalCert | Measure-Object).count)"
 
 If (($CriticalCert | Measure-Object).Count -gt 0) {
 Write-Host "Message.CriticalExpiration: Certs expiring in 7 days. $($CriticalCert.FriendlyName -join ";")"
 } Else {
 Write-Host "Message.CriticalExpiration: No certs expiring in 7 days"
 }
 
 }

#Expired
 $ExpiredCert = $Certificates | Where-Object {$_.NotAfter -lt (Get-Date)}

 If ([String]::IsNullOrEmpty($ExpiredCert)) {
 Write-Host "Statistic.Expired: 0"
 Write-Host "Message.Expired: No certs expired"
 } Else {
 Write-Host "Statistic.Expired: $(($ExpiredCert | Measure-Object).count)"
 
 If (($ExpiredCert | Measure-Object).Count -gt 0) {
 Write-Host "Message.Expired: Certs expired. $($CriticalCert.FriendlyName -join ";")"
 } Else {
 Write-Host "Message.Expired: No certs expired"
 }
 
 }

#Retrieves the earliest expiration date
$Closest = ($Certificates | Select-Object FriendlyName,NotAfter | Sort-Object -property NotAfter)[0]
$ClosestTime = $Closest.NotAfter - (Get-Date)
If ($ClosestTime.Days -ge 0) {
Write-Host "Message.EarliestExpiration: $($Closest.FriendlyName) expires in $($ClosestTime.days) days"
} Else {
Write-Host "Message.EarliestExpiration: $($Closest.FriendlyName) expired $([Math]::Abs($ClosestTime.days)) days ago"
}
Write-Host "Statistic.EarliestExpiration: $($ClosestTime.Days)"
Exit 0

HillyBilly · Answer

Hi Raiden100,

Sorry I'm a little late to the game but not sure if you have found an answer to your problem. I also had a similar issue, where the SAM template only monitors the first website within IIS, and not any others. I also found that some of 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.

In SolarWinds Application Monitoring create a application monitoring template and assign a PowerShell Monitoring component.

Settings Information (Agentless) :-

Component Description:

*******

Checks for the expiry of the IIS 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 "${Node.Caption} , Site ID found in IIS as the "Script Arguments". Configured for Site ID = 1

ie. ${node.Caption},1

*******

Credential for monitoring: <Inherit from template>

Script Arguments:

${node.Caption},1
as per the description, if you website is ID 2 then change the digit to the Website ID (found in IIS).

Execution Mode: I've got mine set to Local Host

Run the script under specified account: option ticked.

$MyScript = {
    $SiteID = $args
    try
    {
    $objCert = $null
        $objCert = Get-WebBinding -Protocol https | Where-Object {($_.ItemXPath -replace '(?:.*?)id=''([^'']*)(?:.*)', '$1') -eq $SiteID}| Select-Object bindingInformation, certificateHash, certificateStoreName, ItemXPath

        if (($objCert -eq "") -or ($Null -eq $objCert))
        {
            $strMessage = "No sites are bound to a HTTPS Procotol"
        }
    
            if ($objCert -is [System.Array])
        {
            $strCertPath = "Cert:\LocalMachine\{0}\{1}" -f $objCert.certificateStoreName[0], $objCert.certificateHash[0]
        }
        else
        {
            $strCertPath = "Cert:\LocalMachine\{0}\{1}" -f $objCert.certificateStoreName, $objCert.certificateHash
        }
        $objCertAll = Get-ChildItem -Path $strCertPath | Select-Object -Property Issuer,notafter,FriendlyName,DnsNameList,Subject

        $SiteID =  ($objCert[0].ItemXPath -replace '(?:.*?)id=''([^'']*)(?:.*)', '$1').ToString().Trim()
        $WebSite = ($objCert[0].ItemXPath -replace '(?:.*?)name=''([^'']*)(?:.*)', '$1').ToString().Trim()
        $objSiteBinding = $objCert[0].bindingInformation.substring($objCert[0].bindingInformation.IndexOf(":")+1)
        $SiteBinding = $objSiteBinding.substring(0,$objSiteBinding.IndexOf(":"))
        $CertExpiresDays = ($objCertAll.notAfter – (Get-Date)).Days
        $CertExpiryDate = (Get-Date).AddDays($CertExpiresDays).ToString('dd/MM/yyyy')
        $CertFriendlyName = $objCertAll.FriendlyName.Trim()
        $CertSubject = $objCertAll.Subject
        $CertDNSNames = [system.string]::Join(", ", $objCertAll.DnsNameList.Unicode)
        $CertThumbprint = $objCert.certificateHash

        Write-Host ("Message.Web_SiteID: {0}" -f $WebSite)
        Write-Host ("Statistic.Web_SiteID: {0}" -f $SiteID)

        Write-Host ("Message.Site_Port_Binding: {0}" -f $SiteBinding)
        Write-Host ("Statistic.Site_Port_Binding: {0}" -f $SiteBinding)

        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_DNS_Names: {0}" -f $CertDNSNames)
        Write-Host "Statistic.Cert_DNS_Names: 0"

        Write-Host ("Message.Cert_Thumbprint: {0}" -f $CertThumbprint)
        Write-Host "Statistic.Cert_Thumbprint: 0"
    }
    Catch
    {
        $strMessage= "Unable to detect a IIS SSL certificate."
        Write-Host "Message.Site: $strMessage"
        $iSWStatistic = 0
        Write-Host "Statistic.Site: $iSWStatistic"
    }
}

$computerName = $args[0]

$iWebsiteID = $args[1]

if (Test-Connection -ComputerName $computerName -Quiet -Count 1)
{
    Invoke-Command -ComputerName $computerName -Credential '${CREDENTIAL}' -ScriptBlock $MyScript -ArgumentList $iWebsiteID
}

Settings Information (Agent) :-

Component Description:

*******

Checks for the expiry of the IIS 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.
Site ID found in IIS as the "Script Arguments". Configured for Site ID = 1

*******

Credential for monitoring: <None>

Script Arguments: 1
as per the description, if you website is ID 2 then change the digit to the Website ID (found in IIS).

Execution Mode: I've got mine set to Local Host

Run the script under specified account: option Unticked.

Edit the script and remove the following lines, as they are not required when a system has an agent installed.

Line 1 = $MyScript = {

Lines 65 and below (to the end) =

}

$computerName = $args[0]

$iWebsiteID = $args[1]

if (Test-Connection -ComputerName $computerName -Quiet -Count 1)
{
Invoke-Command -ComputerName $computerName -Credential '${CREDENTIAL}' -ScriptBlock $MyScript -ArgumentList $iWebsiteID
}

When you insert the script, carry out at test on a node that you know has an IIS Cert installed.
You should then have 8x Script Outputs. I remove the "_" (underscores) in the display name but that is a personal preference.
Under the "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 "Cert DNS Names, Cert Expiry Date, Cert Friendly Name, Cert Subject, Cert Thumbprint" is set to 0 (zero) as is the string value you are interested in.
"Cert Expires Days" is the amount of days before the Certificate will expire.
"Site Port Binding" is the SSL port number assigned to the website (useful if it's not the default 443
"Web SiteID" will be the IIS Site ID number.

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,
[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 = '< ENTER THE NAME OF THE APPLICATION MONITOR TEMPLATE >')
    and [Web_Cert_Expiry_Days].WebCertExpiryInDays <= 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 SQL Instances that use SSL Certs. I will post this on Twack for everyone to search / use.

SSL certificate expiration monitoring using PowerShell scripts

Top Replies