Use PowerShell to Generate Report of Certificates Issued by your Root CA

series of tubes

Some of you may love using certutil.exe, most of you probably don’t. I personally prefer to do things in PowerShell as the data is much easier to manipulate and read. Before getting started I’ll be honest. Yes, this still relies on certutil, but it takes that data and makes it actually useable.

First things first: certutil is a real jerk. You’d think you could simply filter by the names of the various templates to see what certificates were issued, but no. They want you to filter by the templates’ Object Identifier which is hidden away in the Extensions tab under the Certificate Template Information extension.

Because filtering on the name is too easy

You can use certutil to dump this information with the following command

certutil.exe -v -template

It will appear in the output as “TemplatePropOID” as seen here

Unfortunately you’ll probably notice that this value starts off with a return character, a few spaces, and sometimes words at the end as well. The only portion of this we can actually use is the numerical part. I’ve solved this with a bit of PowerShell trickery.

$templateDump = certutil.exe -v -template
$i = 0
$templates = @(ForEach($line in $templateDump){
If($line -like "*TemplatePropOID =*"){(($templateDump[$i + 1]) -split " ")[4]}
$i++
})

With the command above, you will store all the Object Identifiers for your templates as the array $templates. I’m not great with regular expressions so I’m sure there’s probably a better way to accomplish this. Anyway, essentially what I’m doing is taking the output of certutil.exe -v -template and going through it line by line looking for the phrase “TemplatePropOID =“. When it finds a line containing this, it splits that line into multiple lines based on the whitespace characters.

In command line example above, the multiple line split would equate to





1.3.6.1.4.1.311.21.8.1174692.16553431.10109582.10256707.16056698.204.11486880.6766769
Web
client
and
server

Notice the 4 blank lines at the start? that’s 0 – 3 of the array. The 4th item in the array is the Object Identifier, and then the rest we simply don’t care about. That’s why you see the [4] in the PowerShell command above, I’m dropping everything except that single line.

In my environment when I break it down this way, the numerical value for the template is always the 4th item in the array that’s generated. Clear as mud? perfect. Try running it on your CA and see how it looks. It’s possible yours may be different, I can’t be sure.

ProTip: If you only care about a specific template and you already know what the Object Identifier is, you can easily simplify this by storing it as a variable instead of worrying about all the stuff I just posted above. It’s less dynamic but at the same time there’s less headache. Here’s an example

$templates = @(
'1.3.6.1.4.1.311.21.8.1174692.16553431.10109582.10256707.16056698.204.11486880.6766769'
)

Alright so now that you (hopefully) have the Object Identifiers, you should be able to have some more fun with PowerShell and certutil. Let’s get every certificate that’s been issued by each template and store it as an array named $certs

$certs = $null
ForEach($template in $templates){
$certs += certutil -view -restrict "certificate template=$template,Disposition=20" -out "CommonName,NotBefore,NotAfter,CertificateTemplate"
}

So, here I’m looping through the $templates array and returning all the successfully issued certificates based on each template. (disposition 20 refers to issued certs, there are different codes for different statuses like revoked, failed, etc.)

Please note, in the example above I’m searching through ALL certificate templates. This can take a very long time if you never clean up your CA. I’d recommend excluding certain certificate templates that you know you don’t care about by using an If statement. For example

$certs = $null
ForEach($template in $templates){
If($template -ne "1.3.6.1.4.1.311.21.8.1174692.16553431.10109582.10256707.16056698.204.1638972.6366950"){
$certs += certutil -view -restrict "certificate template=$template,Disposition=20" -out "CommonName,NotBefore,NotAfter,CertificateTemplate"
}
}

I’m returning the values I think are important. Common Name, Effective (Issue) Date, Expiration Date, and the Template. From here, we can parse through the $certs array and get something that’s actually useable in PowerShell

$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()
$asdf | Add-Member -membertype NoteProperty -name 'Template' -value (($certs[$i+3] -replace "Certificate Template: ","") -replace '"','').trim()
$asdf
}
$i++
}
)

Do yourself a favor and paste this into your PowerShell ISE so you can actually read it. The logic here is similar to how I got the Template Object Identifiers. I’m looping through the $certs array line by line looking for the phrase “*Issued Common Name: *”. When I find that phrase, I logically know that this line and the next 3 after it have the information I’m looking for. I’m storing this information in a new PowerShell object called $asdf (lol this is what I use when I can’t think of a good name for a variable). I’m also removing the extra info like whitespaces and timestamps so the output will be clean and easily readable (that’s what the .replace and .trim() are doing). I then drop this into the $output array.

I can then output $output to the screen and…

hey look, useable data

As you can see in the example output above, the data is now actually useable. You can sort it, export it to CSV, filter it easily, etc.

This got me what I needed, but was this helpful for you? Or am I a moron? Please feel free to comment or offer suggestions. I’m not pretending to know everything and I’d love to see your thoughts on this.

5 thoughts on “Use PowerShell to Generate Report of Certificates Issued by your Root CA

  1. Hi Vince!

    This was ultra helpful in my use case. There is an issue with some of my certificates having multiple “Issued Common Name”:

    Row 1:
    Issued Common Name: “name1.adatum.com
    name2.adatum.com
    name3.adatum.com”
    Certificate Expiration Date: 11.07.2024 09:40
    Certificate Template: “1.3.6.1.4.1.311.21.8.10636565.12288928.10044084.5746025.3420161.206.13627342.3895982”

    this messes up the properties and one of the common names will appear in the column for expiration date. Sadly, the amount of names can vary from one to two or 4.

    How would you suggest I get around this?

    Thanks!
    Marc

    1. I’m sorry I didn’t see your comment until now, but the way I’m doing it is a bit lazy. It finds the first matching phrase and then just assumes the next few lines are the correct values. In your case you probably need to find each matching phrase individually and add that to the psobject instead. For the multiple common names I’m not sure how to make it look pretty but you can probably find each one and maybe join them together? I’d need to have an example cert to mess with.

  2. Certutil definitely sucks. Looking through some older examples online it seems like it was possible at some point server 2008? possibly to search certificates based off of a friendly name instead of oid. In any case if the adcsadministration module is installed there is a Get-CATemplate cmdlet that provides the template and OID so you can use “(Get-CATemplate | Where-Object {$_.Name -eq “TemplateName”}).oid to get the oid quicker. Also if you assign the output of certutil in csv to a variable you can parse it more easily via a convertfrom-csv in a more powershell friendly way.

  3. So surprised everyone wants the template number. Means nothing to me. All I want to do is get a dump of the certificate name, i.e. SCCM Client Certificate. Will you code do this?

Leave a Reply to Vince Cancel reply

Your email address will not be published. Required fields are marked *