{"id":82965,"date":"2018-05-09T00:01:07","date_gmt":"2018-05-09T07:01:07","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/?p=82965"},"modified":"2019-02-18T09:09:59","modified_gmt":"2019-02-18T16:09:59","slug":"get-certificate-info-into-a-csv-by-using-powershell","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/get-certificate-info-into-a-csv-by-using-powershell\/","title":{"rendered":"Get certificate info into a CSV by using PowerShell"},"content":{"rendered":"<p><strong>Summary<\/strong>: Certificate management is always challenging. Let\u2019s explore how to use PowerShell to export local certificate information to a comma-separated values (CSV) file on Windows 7 (or later) computers.<\/p>\n<p>Q: Hey, Scripting Guy!<\/p>\n<p>How can I get all my certificate info into a CSV on my Windows computers?<\/p>\n<p>\u2014SH<\/p>\n<p>A: Hello SH,<\/p>\n<p>Patrick Mercier here, with my first \u201cHey, Scripting Guy!\u201d post. This question has come up at multiple customer sites, as they plan a new PKI infrastructure or a revamp of their current one!<\/p>\n<p>There\u2019s 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 <a target=\"_blank\" href=\"https:\/\/gallery.technet.microsoft.com\/scriptcenter\/Run-Parallel-Parallel-377fd430\" rel=\"noopener\">Invoke-Parallel<\/a> 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 \u201cHey, Scripting Guy!\u201d community for providing a starting point.<\/p>\n<p>As I\u2019m sure you\u2019ve seen in other posts here, the whole thing starts with the <strong>Get-ChildItem<\/strong> cmdlet.\u00a0 At its most basic level, the following command lists all the certificates on your local system:<\/p>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/certscan-02-05072018.png\"><img decoding=\"async\" width=\"640\" height=\"34\" class=\"alignnone size-full wp-image-82985\" alt=\"Screenshot of PowerShell\" src=\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/certscan-02-05072018.png\" \/><\/a><\/p>\n<p>Let\u2019s break it down:<\/p>\n<ul>\n<li>We\u2019re asking for the child items of the certificate branch of the local machine (Get-ChildItem -path Cert:\\LocalMachine). \u201cWait a minute!\u201d you say. \u201cI\u2019ve 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?\u201d Simply put, this \u201cpath\u201d is available due to the presence of a PowerShell Provider. For more info, check out Ed\u2019s post: <a target=\"_blank\" href=\"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2015\/04\/20\/find-and-use-windows-powershell-providers\/\" rel=\"noopener\">Find and use Windows PowerShell Providers<\/a>. But the basics for today are that in providing CERT: as the path, I\u2019m calling on the certificate provider in order to access specific information on my system.<\/li>\n<li>We\u2019re doing this recursively (<strong>-Recurse<\/strong>), to get every child object below this point.<\/li>\n<li>We\u2019re filtering out the containers (<strong>where-object {$_.PSIContainer -eq $false}<\/strong>).<\/li>\n<li>We\u2019re ensuring that we\u2019re grabbing all the attributes available (<strong>Format-List -Property *<\/strong>).<\/li>\n<\/ul>\n<p>Running this command displays all the certificates installed on your local system, conveniently including a list of available attributes:<\/p>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/certscan-03-05072018.png\"><img decoding=\"async\" width=\"1024\" height=\"375\" class=\"alignnone size-large wp-image-82995\" alt=\"Screenshot of PowerShell\" src=\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/certscan-03-05072018-1024x375.png\" \/><\/a><\/p>\n<p>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 \u2013 R1 certificate, and then copy each attribute value to Excel.<\/p>\n<p>You would think that piping that command to a CSV would make for a happy day, wouldn\u2019t you? Sadly, not so. Directly outputting this by using Export-CSV doesn\u2019t give us the expected result.<\/p>\n<p>Getting it all into a format we can manipulate is going to take a bit more effort. Enter the array and the <strong>PSObject<\/strong>.<\/p>\n<p>So, my script now starts with defining an empty array, conveniently called $array.<\/p>\n<p>Now, we see the familiar <strong>Get-ChildItem<\/strong> command. But instead of piping it directly out by using Export-CSV, we\u2019ll use the <strong>foreach-object<\/strong> loop, and break down the output. Ultimately, what this does is:<\/p>\n<ul>\n<li>Create a new <strong>PSObject<\/strong> for each certificate found by the <strong>get-childitem<\/strong> cmdlet. Think of the <strong>PSObject<\/strong> as a row inside your data table or, ultimately, your Excel sheet. (<strong>New-Object -TypeName PSObject<\/strong>)<\/li>\n<li>Add the value of our selected attributes into \u201ccolumns\u201d. In this case, PSPath, FriendlyName, Issuer, NotAfter, NotBefore, SerialNumber, Thumbrint, DNSNameList, Subject, and Version are all populated. (<strong>Add-Member \u2013MemberType NoteProperty -Name \u201c%attrib%\u201d -Value $_.%attrib%<\/strong>)<\/li>\n<li>Add the object to your array as a new row. (<strong>$array += $obj<\/strong>)<\/li>\n<li>Clear out the object, so that no data carries over on the next iteration of the loop. (<strong>$obj=$null<\/strong>)<\/li>\n<li>Export your array to your CSV. (<strong>Export-Csv<\/strong>)<\/li>\n<\/ul>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/certscan-04-05072018.png\"><img decoding=\"async\" width=\"1024\" height=\"500\" class=\"alignnone size-large wp-image-83005\" alt=\"Screenshot of code\" src=\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/certscan-04-05072018-1024x500.png\" \/><\/a><\/p>\n<p>As you see, we can then pipe our array out to the CSV file.<\/p>\n<p>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\u2019ll leave it to you to find the well-known one that I keep finding.<\/p>\n<p>If the attributes included above don\u2019t meet your needs, you can easily add (or remove) one from the loop simply by inserting an additional <strong>Add-Member<\/strong> line. Say you decide you need to include the <strong>PSProvider<\/strong> attribute. Simply insert the following above the <strong>$array<\/strong> <strong>+=<\/strong> <strong>$obj<\/strong> in the loop:<\/p>\n<p><strong>$obj | Add-Member -MemberType NoteProperty -Name &#8220;PSProvider&#8221; -value $_.PSProvider<\/strong><\/p>\n<p>To see what attributes are available, run the first command provided above, and read the output!<\/p>\n<p>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.<\/p>\n<p>I invite you to follow the Scripting Guys on <a target=\"_blank\" href=\"http:\/\/bit.ly\/scriptingguystwitter\" rel=\"noopener\">Twitter<\/a> and <a target=\"_blank\" href=\"http:\/\/bit.ly\/scriptingguysfacebook\" rel=\"noopener\">Facebook<\/a>. If you have any questions, send email to them at scripter@microsoft.com, or post your questions on the <a target=\"_blank\" href=\"http:\/\/bit.ly\/scriptingforum\" rel=\"noopener\">Official Scripting Guys Forum<\/a>.<\/p>\n<p><strong>Patrick Mercier, Premier Field Engineer<\/strong><\/p>\n<p><strong>Microsoft<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Summary: Certificate management is always challenging. Let\u2019s 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? \u2014SH A: Hello SH, Patrick Mercier here, [&hellip;]<\/p>\n","protected":false},"author":596,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[568],"tags":[56,3,45],"class_list":["post-82965","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-hey-scripting-guy","tag-guest-blogger","tag-scripting-guy","tag-windows-powershell"],"acf":[],"blog_post_summary":"<p>Summary: Certificate management is always challenging. Let\u2019s 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? \u2014SH A: Hello SH, Patrick Mercier here, [&hellip;]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/82965","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/596"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=82965"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/82965\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=82965"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=82965"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=82965"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}