Get certificate info into a CSV by using PowerShell

Doctor Scripto

Summary: Certificate management is always challenging. Let’s explore how to use PowerShell to export local certificate information to a comma-separated values (CSV) file on Windows 7 (or later) computers.

Q: Hey, Scripting Guy!

How can I get all my certificate info into a CSV on my Windows computers?

—SH

A: Hello SH,

Patrick Mercier here, with my first “Hey, Scripting Guy!” post. This question has come up at multiple customer sites, as they plan a new PKI infrastructure or a revamp of their current one!

There’s tons of resources on using PowerShell for querying certificates, but questions around finding expiring certificates, self-signed certificates, or certs issued by specific roots keep coming up when I meet with customers. My current customer needed to find self-signed certificates, so we took this local scan example and wrapped it in Invoke-Parallel to scan targeted systems! Thanks to Joel Mueller, a fellow Premier Field Engineer (PFE) at Microsoft who got me started on this, and to the rest of the “Hey, Scripting Guy!” community for providing a starting point.

As I’m sure you’ve seen in other posts here, the whole thing starts with the Get-ChildItem cmdlet.  At its most basic level, the following command lists all the certificates on your local system:

Let’s break it down:

• We’re asking for the child items of the certificate branch of the local machine (Get-ChildItem -path Cert:\LocalMachine). “Wait a minute!” you say. “I’ve only ever used the Get-ChildItem cmdlet with a file path to get a list of files and folders. Where do you get this cert:\localmachine business?” Simply put, this “path” is available due to the presence of a PowerShell Provider. For more info, check out Ed’s post: Find and use Windows PowerShell Providers. But the basics for today are that in providing CERT: as the path, I’m calling on the certificate provider in order to access specific information on my system.
• We’re doing this recursively (-Recurse), to get every child object below this point.
• We’re filtering out the containers (where-object {$_.PSIContainer -eq $false}).
• We’re ensuring that we’re grabbing all the attributes available (Format-List -Property *).

Running this command displays all the certificates installed on your local system, conveniently including a list of available attributes:

This example shows the GlobalSign Root CA in the root store of my machine. You should be able to find this cert on your system too. Alternatively, if you like doing things the hard way, you can bring up an MMC, load the certificates snap-in, and browse to the trusted root store. There you can find the GlobalSign Root CA – R1 certificate, and then copy each attribute value to Excel.

You would think that piping that command to a CSV would make for a happy day, wouldn’t you? Sadly, not so. Directly outputting this by using Export-CSV doesn’t give us the expected result.

Getting it all into a format we can manipulate is going to take a bit more effort. Enter the array and the PSObject.

So, my script now starts with defining an empty array, conveniently called $array.

Now, we see the familiar Get-ChildItem command. But instead of piping it directly out by using Export-CSV, we’ll use the foreach-object loop, and break down the output. Ultimately, what this does is:

• Create a new PSObject for each certificate found by the get-childitem cmdlet. Think of the PSObject as a row inside your data table or, ultimately, your Excel sheet. (New-Object -TypeName PSObject)
• Add the value of our selected attributes into “columns”. In this case, PSPath, FriendlyName, Issuer, NotAfter, NotBefore, SerialNumber, Thumbrint, DNSNameList, Subject, and Version are all populated. (Add-Member –MemberType NoteProperty -Name “%attrib%” -Value $_.%attrib%)
• Add the object to your array as a new row. ($array += $obj)
• Clear out the object, so that no data carries over on the next iteration of the loop. ($obj=$null)
• Export your array to your CSV. (Export-Csv)

As you see, we can then pipe our array out to the CSV file.

If all went well, you now have a CSV that contains the certificate information on your local machine! I would not be surprised if, after having done this, you discover expired certificates on your system. I’ll leave it to you to find the well-known one that I keep finding.

If the attributes included above don’t meet your needs, you can easily add (or remove) one from the loop simply by inserting an additional Add-Member line. Say you decide you need to include the PSProvider attribute. Simply insert the following above the $array += $obj in the loop:

$obj | Add-Member -MemberType NoteProperty -Name “PSProvider” -value $_.PSProvider

To see what attributes are available, run the first command provided above, and read the output!

I suspect that many of you will want to see how to scale this to scanning remote systems, so watch for a future post that will do just that.

I invite you to follow the Scripting Guys on Twitter and Facebook. If you have any questions, send email to them at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum.

Patrick Mercier, Premier Field Engineer

Microsoft

Doctor Scripto Scripter, PowerShell, vbScript, BAT, CMD

Follow