• 0
Dominique

How to get the Certificate Common Name (FQDN) in the alerts description

Question

Posted (edited)

Hello,

I am using the two script from Cole McDonald:

On 8/22/2019 at 9:11 AM, Cole McDonald said:

I've lightened the load slightly on the winCertCheck (which is technically no longer the same DS as I've replaced the entirety of the scripts with simplified .NET based powershell scripts to avoid using invoke-command which tends to lead to some resource constraint issues.  This should help though, will keep the same instances alive from the old code as the output is identical to the previous version by @Jonathan Arnold:

##--------------- Discovery ------------------##
$readOnly     = [System.Security.Cryptography.X509Certificates.OpenFlags]"ReadOnly"
$localMachine = [System.Security.Cryptography.X509Certificates.StoreLocation]"LocalMachine"
$store        = new-object System.Security.Cryptography.X509Certificates.X509Store( "\\##SYSTEM.SYSNAME##\root", $localMachine )

$store.Open( $readOnly )

$store.Certificates `
| Select-Object {$_.Thumbprint + "##" + $_.Thumbprint + "##" + $_.Subject + $_.CommonName} `
| Format-Table -HideTableHeaders
##--------------------------------------------##
##-------------- Counters --------------------##
$readOnly     = [System.Security.Cryptography.X509Certificates.OpenFlags]"ReadOnly"
$localMachine = [System.Security.Cryptography.X509Certificates.StoreLocation]"LocalMachine"
$store        = new-object System.Security.Cryptography.X509Certificates.X509Store( "\\##SYSTEM.SYSNAME##\root", $localMachine )

$store.Open( $readOnly )

$store.Certificates `
| Where-Object {($_.Thumbprint -like "##WILDVALUE##")} `
| Select-Object @{
	Name       = "DaysUntilExpire"
	Expression = {((Get-Date -Date $_.NotAfter) - (Get-Date)).Days}
} `
| Format-List
##--------------------------------------------##

(please note the line continuations to help readability of the code)

As always, neither I nor Beyond Impact warranty this code.  It's working in our environment, I can't guarantee it'll work in yours.  This doesn't account for anything that needs credentials other than what the collector uses.

==========================================================================================================================

But when testing them on a current alert

I could not get the correct common name displayed... and I do not see the certificate listed in the alert in the list of certificates produced by the script...

I might have missed something!!!

Thanks,

Dom

 

2020-06-17_12-28-27 SOPRDSCIRIUS1 - Alert.png

2020-06-17_12-26-40 SOPRDSCIRIUS1 - Result Cert 01.png

Edited by Dominique

Share this post


Link to post
Share on other sites

20 answers to this question

Recommended Posts

  • 0

Thanks for your patience Stuart... 

I found the location

##$store        = new-object System.Security.Cryptography.X509Certificates.X509Store( "\\##SYSTEM.SYSNAME##\root", $localMachine )##
$store        = new-object System.Security.Cryptography.X509Certificates.X509Store( "\\##SYSTEM.SYSNAME##\my", $localMachine )

Now I have the good certificate listed... I need to add as you said the expiration date set to combined both...

Thanks,

Dom

Share this post


Link to post
Share on other sites
  • 1

Are the alarms in your first screenshot coming from the right DS? Looks like that alarm is coming from "SSL Certificate Expiration". Is that the same DS you built?

The common name is stored in the instance description right? If so, it should show up under the instance description in the alarm.

Share this post


Link to post
Share on other sites
  • 1

Yeah, I think you're getting your DataSources crossed. The one from your screenshot is called "SSLCerts-" (displayname="SSL Certificate Expiration"). That DS is not a scripted DS, so the changes you are making to the scripted DS "_SSL_Certificates" will not impact the alerts opened by "SSLCerts-". 

The SSLCerts- DS isn't meant to check all certs on a system, just those that are presented on a live web service discovered when the collector probes the TCP ports 443, 465, 636, and 5671. 

Took a peek at your account and it looks like the current version of the script stores the CN as the WildAlias, which is perfectly fine (and probably preferable to what you were showing in your screenshot above).  Let's talk about assumptions...

I assume that you are writing _SSL_Certificates as a replacement for SSLCerts-. 
I assume that you can fix the script error that is currently causing the "No Data" error on collection.

Given those two assumptions, the output of your collection script should look something like this (actual values made up):

Days: 25

(Technically there are other simpler ways that you could output your data, but this way leaves the door open to adding more datapoints later on if desired.)

 

Your datapoint "CommonName" doesn't need to exist. Instead, you need to create a datapoint and the key would be "Days".  You'd need to set a threshold on this datapoint so alarms open.

When an alert opens, you'd have the instance name in the alert, which is currently set to the CN. 

Share this post


Link to post
Share on other sites
  • 1

All of that makes sense and you are on the right track. The only thing i would add is that once you are getting the expiration date collecting successfully and the threshold set on the _SSL_Certificates DS, you might as well disable the SSLCerts- DS as you would be double alerting.

Share this post


Link to post
Share on other sites
  • 1

Isn't the CN in the $_.Subject variable? It looks like it is. That would mean that the instance name is the CN. 

You don't need the CN in a datapoint. The only datapoint you need is the "valid for". If you wanted to make more complex datapoints that compare the issued or expiration dates to now, you could do that, but it's not required for what it seems you need.

Share this post


Link to post
Share on other sites
  • 1

Your collect script still isn't outputting anything and you don't have a threshold. There can't be any alerts where this would show up.  What you're showing here is the alarm message from a completely different datasource.

Order of operations:

1. Get your collect script returning the number of days until expiration

2. Get your datapoint configured to read in the number of days into an actual datapoint

3. Set a threshold on the datapoint

4. Either build a custom alert message for that one datapoint (or modify the global alert template) to include the ##INSTANCE## token so that the instance's name is included in the email.

Either way, one datasource isn't going to add information to an alert from a different datasource. No matter what you do in _SSL_Certificates, it will not have any impact whatsoever on SSLCerts- alerts.

Share this post


Link to post
Share on other sites
  • 1

Ah, i see. The problem is not that instance is getting discovered but the name isn't showing up, it's that the instance isn't getting discovered at all. Your script is not fetching those personal certs and will have to be modified somehow to pull in the personal certificates. Unfortunately, that's beyond my scripting skills as i'm not a windows/powershell guy.  I could google it for you, but you probably don't need that kind of help from me.

Share this post


Link to post
Share on other sites
  • 0
2 hours ago, Stuart Weenig said:

Are the alarms in your first screenshot coming from the right DS? Looks like that alarm is coming from "SSL Certificate Expiration". Is that the same DS you built?

The common name is stored in the instance description right? If so, it should show up under the instance description in the alarm.

1. yes the alarm are coming from the SSL Certificate Expiration

2. No I did not see the common name anywhere in the current Datasource it is why I was creating a new one with the script in this thread.

Share this post


Link to post
Share on other sites
  • 0

Hello,

I want to see the common name in the alert description but for now it is not displayed anywhere...

Thanks,

Dom

Share this post


Link to post
Share on other sites
  • 0

Thanks Stuart,

The source of the question I am trying to resolve is coming from the datasource called "SSLCerts-" (displayname="SSL Certificate Expiration") which is incomplete and missing data (missing the "Common Name") for our team to be able to use it.

The DS "_SSL_Certificates" has been created to try to collect the information needed like the “common name”. I used this one as it seems simpler to use and update than “SSLCerts”, I might be wrong, let me know.

So the SSLCerts- DS is fine as is and for testing I didn’t want to touch it…. As it is already active in Production even if it is incomplete so far. 

I am writing _SSL_Certificates as a replacement for SSLCerts- or as addition to it to provide more information

I will check for this error I did not see thanks for pointing it out the one causing the "No Data" error on collection.

The output is not enough for our technician as they need the common name to be able to renew the certificate this is a company requirement

image.png.ff45860658a45437295f6efe89f8f695.png 

I need the Common Name to be displayed as well as the Days before expiration both fields needs to there.

If it is possible to add the “Common Name” to the DS “SSLCerts-“ directly it is fine with me but I could not get it, it is why I went to the “_SSL_Certificates” which looks like will be able to display the “Common Name” and then I will adjust it to get the expiration date as well.

Thanks,
Dom

 

Share this post


Link to post
Share on other sites
  • 0

Thanks Stuart but apparently I do not get the "common name" is it buried somewhere else than 

##--------------- Discovery ------------------##
$readOnly     = [System.Security.Cryptography.X509Certificates.OpenFlags]"ReadOnly"
$localMachine = [System.Security.Cryptography.X509Certificates.StoreLocation]"LocalMachine"
$store        = new-object System.Security.Cryptography.X509Certificates.X509Store( "\\##SYSTEM.SYSNAME##\root", $localMachine )

$store.Open( $readOnly )

$store.Certificates `
| Select-Object {$_.Thumbprint + "##" + $_.Thumbprint + "##" + $_.Subject + $_.CommonName} `
| Format-Table -HideTableHeaders
##--------------------------------------------##

$ !SSLCerts 10.32.156.78 443
Total received certs: 2
No.1 certificate
    CN        : CN=soprdscirius1.ad.xxxxxx.yyyy.zzz, OU=X Information Technology Services (MITS), O="Local, Los Angeles", STREET=757 Ww Plaza, L=Los Angeles, ST=CA, OID.2.5.4.17=90095, C=US
    Issued At : Fri Jun 23 17:00:00 PDT 2017
    Expire At : Tue Jun 23 16:59:59 PDT 2020
    Valid for : 5 days
No.2 certificate
    CN        : CN=InCommon RSA Server CA, OU=InCommon, O=Internet2, L=Ann Arbor, ST=MI, C=US
    Issued At : Sun Oct 05 17:00:00 PDT 2014
    Expire At : Sat Oct 05 16:59:59 PDT 2024
    Valid for : 1570 days
 

Thank,
Dom

Share this post


Link to post
Share on other sites
  • 0
Posted (edited)

Thanks Stuart, Yes on the certificate, or with the SSLCerts it appears in the subject line but it never shows up in the Alert ...

I nee the CN to appear in the Description of the Alert which is not the case for now
Device:
 
Time:
 
2020-06-15 19:20 (67 h 30 m)
ID:
 
DS1475028
Alert Message:
 
LMD20268926 error - SOPRDSCIRIUS1 SSL Certificate Expiration443 Days
ID: LMD20268926
The SSL Certificate on SOPRDSCIRIUS1, port 443 , is going to expire in 7.0 days.
Datasource:
 
SSL Certificate Expiration
Instance:
 
SSLCerts-443
Datapoint:
 
Days
Effective Thresholds:
 
<= 28 7 2
Escalation Chain:
 
NoEscalation
Alert Rule:
 
Error
Instance Description:
 
 
Value:
 
5.0
Full Path:
 
Data Center/Irvine-SDC
IT Ops Systems Management Group/Production/SDC
IT Ops Systems Management Group/TEST
Devices by Type/Windows Servers

Thanks,
Dom

Edited by Dominique

Share this post


Link to post
Share on other sites
  • 0
Posted (edited)

Hello,

I understand what I do on _SSL_Certificates does not affect the SSLCerts DS but I would like the CN to appear in the _SSLCertificates but it does not with this script... is it the location of the certificate which is worng?

I need the CN to appear in the Description of the Alert which is not the case for now. Apparently I see some certificates but not the one listed below with its CN value.. I have most of the certificates under "Trusted Root Certificates" but not the one under "Personal" folder...

It is the certificate listed below in the personal folder I need with its common name listed in clear text in its description...

Thanks,

Dom

2020-06-18_14-58-44 SOPRDSCIRIUS1 - Personal Cert.png

2020-06-18_14-58-44 SOPRDSCIRIUS1 - Trusted Cert.png

Edited by Dominique

Share this post


Link to post
Share on other sites
  • 0
Posted (edited)

Hello,

Some updates:

======================================================================================================

##--------------- Discovery ------------------##
$readOnly     = [System.Security.Cryptography.X509Certificates.OpenFlags]"ReadOnly"
$localMachine = [System.Security.Cryptography.X509Certificates.StoreLocation]"LocalMachine"
##$store        = new-object System.Security.Cryptography.X509Certificates.X509Store( "\\##SYSTEM.SYSNAME##\root", $localMachine )##
$store        = new-object System.Security.Cryptography.X509Certificates.X509Store( "\\##SYSTEM.SYSNAME##\my", $localMachine )

$store.Open( $readOnly )

$store.Certificates `
| Select-Object {$_.Thumbprint + "##" + $_.Subject } `
| Format-Table -HideTableHeaders
##--------------------------------------------##

======================================================================================================

and

======================================================================================================

##-------------- Counters --------------------##
$readOnly     = [System.Security.Cryptography.X509Certificates.OpenFlags]"ReadOnly"
$localMachine = [System.Security.Cryptography.X509Certificates.StoreLocation]"LocalMachine"
$store        = new-object System.Security.Cryptography.X509Certificates.X509Store( "\\##SYSTEM.SYSNAME##\root", $localMachine )
$properties = @(
    @{n='DaysUntilExpire';e={($_.NotAfter -[datetime]::Today ).Days}},
    @{n='CommonName';e={"##WILDALIAS##"}}
)

$store.Open( $readOnly )

$store.Certificates `
| Where-Object {($_.Thumbprint -like "##WILDVALUE##")} `
| Select-Object $properties

======================================================================================================

with two datapoints ...

I am progressing but not yet okay... I did a poll and I have No data for CommonName and DaystoExpire but I have the information in the Raw Request/Response output so I think I have wrongly associated some fields!!!!

Thanks,

Dom

 

 

2020-06-19_20-56-25 datapoints.png

2020-06-19_20-57-44 poll 01.png

2020-06-19_20-57-44 poll 02.png

Edited by Dominique

Share this post


Link to post
Share on other sites
  • 0
Posted (edited)

Hello,

The field CommonName is filled up but not with the value expected, it has the WildValue F23456... and not the CN... From the script results I thought the WildAlias was filled with the CN (see attachment) but apparently it is not strange!!!...

The DaysToExpire is filled up in the Raw Request/Response but not but does not in the Datapoint. Not sure why the "##WildValue##.DaysToExpire" is not showing the value ...

I think the datapoints are not correctly defined...

Thanks,

Dom

2020-06-19_22-15-43 Script Discovery Results 02.png

2020-06-19_20-56-25 datapoints.png

2020-06-19_22-15-43 Script Discovery Results.png

Edited by Dominique

Share this post


Link to post
Share on other sites
  • 0
Posted (edited)

Finally the Test Script "Collector Attributes" is showing the correct value in the correct fields...

Trying to populate the Fields in the Datapoint now!!!

 

2020-06-19_22-15-43 Script Discovery Results 03.png

Edited by Dominique

Share this post


Link to post
Share on other sites
  • 0

Almost there

CommonName is filled properly but there is still the error message !!! Why?

The Days to Expiration are correct

 

2020-06-19_22-15-43 Script Discovery Results 04.png

Share this post


Link to post
Share on other sites
  • 0

Also I noticed if I try to remove the Description or Property from the Collector Attributes Script the fields are again NaN with the error:

 

##-------------- Counters --------------------##
$readOnly     = [System.Security.Cryptography.X509Certificates.OpenFlags]"ReadOnly"
$localMachine = [System.Security.Cryptography.X509Certificates.StoreLocation]"LocalMachine"
$store        = new-object System.Security.Cryptography.X509Certificates.X509Store( "\\##SYSTEM.SYSNAME##\my", $localMachine )
$properties = @(
    @{n='WildValue';e={"##WildValue##"}},
    @{n='CommonName';e={$_.Subject}},
    @{n='Description';e={"##Description##"}},
    @{n='Properties';e={"##Properties##"}},
    @{n='DaysUntilExpire';e={($_.NotAfter -[datetime]::Today ).Days}}
)

$store.Open( $readOnly )

$store.Certificates `
| Where-Object {($_.Thumbprint -like "##WildValue##")} `
| Select-Object $properties

Also I could not get rid of the message in the CommonName even the field is filled ...? How to clean this error message?

Attribute not valid or not found in output - (method=namevalue, param=CommonName)

Thanks,

Dom

2020-06-19_22-15-43 Script Discovery Results 04.png

Share this post


Link to post
Share on other sites
  • 0

You cannot put text into a datapoint. LM is giving you an error because you are trying to put text (the CN) into a number field (the datapoint).

Since your DS is in script mode and not batchscript mode, the only thing you need to output is the value itself.

I updated your collect script to contain just what it needs:

##-------------- Counters --------------------##
$readOnly     = [System.Security.Cryptography.X509Certificates.OpenFlags]"ReadOnly"
$localMachine = [System.Security.Cryptography.X509Certificates.StoreLocation]"LocalMachine"
$store        = new-object System.Security.Cryptography.X509Certificates.X509Store( "\\##SYSTEM.SYSNAME##\my", $localMachine )
$properties = @(
#    @{n='WildValue';e={"##WildValue##"}},
#    @{n='CommonName';e={$_.Subject}},
#    @{n='Description';e={"##Description##"}},
#    @{n='Properties';e={"##Properties##"}},
    @{n='DaysUntilExpire';e={($_.NotAfter -[datetime]::Today ).Days}}
)

$store.Open( $readOnly )

$store.Certificates `
| Where-Object {($_.Thumbprint -like "##WildValue##")} `
| Select-Object $properties `
| Format-Table -HideTableHeaders

Also, your discovery has some problems that I started to resolve. Essentially, if the wildalias is the same, LM thinks it's the same instance. So, even though your WILDVALUE is distinct, the WILDALIAS on two of your certificates is not. Therefore, any certificates that have the same "$_.Subject" will be treated as the same instance. 

Share this post


Link to post
Share on other sites
  • 0

I think you were running into some powershell width issues where it was only returning the first 120 characters of each line of output. I added the following to the beginning of both scripts to fix that:

$pshost = Get-Host              # Get the PowerShell Host.
$pswindow = $pshost.UI.RawUI    # Get the PowerShell Host's UI.

$newsize = $pswindow.BufferSize # Get the UI's current Buffer Size.
$newsize.width = 800            # Set the new buffer's width to 800 columns.
$pswindow.buffersize = $newsize # Set the new Buffer Size as active.

$newsize = $pswindow.windowsize # Get the UI's current Window Size.
$newsize.width = 800            # Set the new Window Width to 800 columns.
$pswindow.windowsize = $newsize # Set the new Window Size as active.

I also modified your discovery script so that your WildAliases are unique. In one case, there was a certificate that didn't have a subject, so i added some logic to gracefully handle that. When the subject is known, the last 5 digits of the thumbprint are appended to the instance name to make it (hopefully) unique.

I also added some instance level properties for each instance. Cert.issuer, cert.serialnumber, and cert.thumbprint. These instances could be used to dynamically group certificates if wanted. If the instance level properties aren't needed, those lines can simply be commented out.

I also removed the valid range constraint just to make sure we were getting values on all of them. You had one certificate that was returning a negative number. If you want that negative number to open an alert, you can't exclude it from valid range.  If you truly want to ignore negative values, just put the lower value in the valid range.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.