How to create datasources from powershell script

  • 30 May 2023
  • 6 replies

Userlevel 2
Badge +1
  • Processor Participant
  • 6 replies


I wrote a PS script that takes a look at all issued certs on my microsoft CA and outputs 4 columns, The name of the cert, the effective date, the expiration date and the days remaining until cert expiration.


Here is the script for reference:

$templates = @('x.x.x.x.x.x.x.x.x.x.x.x')
$certs = $null
ForEach($template in $templates){
$certs += certutil -view -restrict "certificate template=$template,Disposition=20" -out "CommonName,NotBefore,NotAfter,CertificateTemplate"
$i = 0
$output = @(
ForEach($line in $certs){
If($line -like "*Issued Common Name: *"){
$asdf = New-Object -TypeName psobject
$asdf | Add-Member -membertype noteproperty -name 'Common Name' -value (($certs[$i] -replace "Issued Common Name: ","") -replace '"','').trim()
$asdf | Add-Member -membertype NoteProperty -name 'Effective Date' -value (($certs[$i+1] -replace "Certificate Effective Date: ","") -replace '\d+\:\d+\s+\w+','').trim()
$asdf | Add-Member -membertype NoteProperty -name 'Expiration Date' -value (($certs[$i+2] -replace "Certificate Expiration Date: ","") -replace '\d+\:\d+\s+\w+','').trim()
$expirationDate = [datetime]::MinValue
[datetime]::TryParse($asdf.'Expiration Date', [ref]$expirationDate)
$daysRemaining = ($expirationDate - (Get-Date)).Days
$asdf | Add-Member -MemberType NoteProperty -Name 'Days Remaining' -Value $daysRemaining

How can I create a datasource within LM that will parse out each common name, tie it to its corresponding “days remaining” value and alert based on that? Is this possible?


Best answer by Stuart Weenig 30 May 2023, 22:55

View original

6 replies

Userlevel 7
Badge +18

Just to be sure, what does the output look like (redact any private information)

Userlevel 2
Badge +1

Here is an example of the output:

Common Name                        Effective Date Expiration Date Days Remaining
-----------                        -------------- --------------- --------------                9/20/2021      9/20/2023                  112
True                9/29/2021      9/29/2023                  121
True                 4/1/2022       3/31/2024                  305
True                 4/6/2022       4/5/2024                   310

Userlevel 7
Badge +18

Alright, assuming you’re not going to be running this code on your collector but on different systems, you’ll have to put your script into a scriptblock and run it as shown here.

Assuming your common names in production will actually be unique, just use for loop to make your discovery scriptblock write-host output look like this:

And use pretty much the same code (but different for loop) to make your collection scriptblock write-host this 112 121 305 310

Then just setup a datapoint using ‘multiline key-value pairs’ and make the interpreter “##WILDVALUE##.daysRemaining”.

Then just set whatever threshold you want on that datapoint.

Have you checked that this has not already been built by someone else?

Userlevel 2
Badge +1

Thank you! Ill give this a shot! I looked around the exchange and couldn't find anything - Which is surprising, because I figured this would be a pretty common use case!

Userlevel 4
Badge +5

Stuart’s comment seems to get you in the right track to use the script within a datasource at first glance.
I just wanted to add that there’s a community PowerShell based datasource that has been published by a community member under name ‘Windows Certificates-’ and lmLocator code KPNWGW that I think would achieve what you’re looking for, or at least get you close.
Note: this datasource applies to all Windows devices by default, I would recommend changing the AppliesTo so it gets applied on a single device for testing/confirming the functionality.

You can simplify this script if you use Get-ChildItem to grab the certs directly from the certificate store. Using the Powershell provider usually takes care of the variable typing and formatting which is very nice. Below, I pulled out EnhancedKeyUsageList, but the GetFormat() method might align to your preference for CertUtil template name schema. 


$DesiredProperties = "CommonName,NotBefore,NotAfter,EnhancedKeyUsageList"

$AllCerts = Get-ChildItem -Recurse -Path "Cert:\" | Where-Object {$_.Thumbprint} | Select-Object -Property $DesiredProperties

## Other neat uses of Get-ChildItem for the Powershell Certificate provider
# $CodeSigningCert = Get-ChildItem -Recurse -Path "Cert:\CurrentUser\My" -CodeSigningCert
# $RemoteAuthCert = Get-ChildItem -Recurse -Path "Cert:\CurrentUser\My" -SSLServerAuthentication

Also, if you’re looking for performance optimizations: 

  • Using “+=” copies everything from the initial memory space into a new memory space with the extra object appended to the end. This is fine for small datasets, but you will see large performance impacts (and memory requirements) with increasing size.
  • Select-String is extremely efficient for parsing through text, even more so than falling back to .NET in many cases. Looping through line by line with foreach, or Foreach-Object will be slower especially when used with unoptimized regex or match statements (which is another ~deep subject =).