Solved

How to create datasources from powershell script

  • 30 May 2023
  • 6 replies
  • 142 views
C
Rank
Userlevel 2
Badge +1
  • CaseyW Processor Participant
  • Processor Participant
  • 6 replies

Hello,

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
$asdf
}
$i++
}
)
$output

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?

icon

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

View original

6 replies

Stuart Weenig
Rank
Userlevel 7
Badge +18

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

Rebuilding my stuff here: https://github.com/sweenig/lm http://bit.ly/sweenigresume
C
Rank
Userlevel 2
Badge +1

Here is an example of the output:

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

Stuart Weenig
Rank
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:

cert.domain.com##cert.domain.com
cert.domain.com##cert.domain.com
cert.domain.com##cert.domain.com
cert.domain.com##cert.domain.com

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

cert.domain.com.daysRemaining: 112
cert.domain.com.daysRemaining: 121
cert.domain.com.daysRemaining: 305
cert.domain.com.daysRemaining: 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?

Rebuilding my stuff here: https://github.com/sweenig/lm http://bit.ly/sweenigresume
C
Rank
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!

Amr Abdelrahman
Rank
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.

Technical Support Engineer II, LM Community Champion
J
Rank

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. 

Example: 

$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 =).  

Reply


Terms & ConditionsCookie settings