HowTo: Powershell Script to cleanup expired certificates from a Microsoft CA Part 2 of 2

Dec 19

Written by:
Friday, December 19, 2014 2:01 PM  RssIcon

>> Here is the link to the first part of this series:
How To: Cleanup expired certificates from a Microsoft CA with Powershell and Shrink the DB Part 1 of 2

Here in part2 I explain the powershell script in detail (the script is used in step 2 of part 1 of this series) :


The Microsoft Enterprise CA I’m responsible for is running on a Microsoft Windows Server 2008 Enterprise Server
- with PowerShell 2.0 installed
- no 3rd party PS modules are used
- the certutil.exe is used by the powershell (PS) script

- the PS script I created is "Cleanup_MSPKI_Cert_v1.1.ps1" and contains 3 functions

 

On this CA Server in the C:\ root drive I create a folder_scripts “ (I don’t use PS remoting) and copy my powershell script “Cleanup_MSPKI_Cert_v1.1.ps1” into this folder

image

Per default the functions "Remove-ExpiredCertFromDB" writes the temporary files to a subfolder within C:\_scripts\PKICleanupLog.

You can change this default folderpath with the parameter  “CleanedFolderLogPath”

image

 

The 3 functions I have implemented are:

A.) Get-PublishedCATemplate

B.) Get-IssuedCert

C.) Remove-ExpiredCertFromDB


A.) Get-PublishedCATemplate

image

When you run this function without a parameter, it displays all Templates from the "Certificate Templates" folder with it's OID. This OID is used by the other to functions to display or delete certificates issued with this certain template. In the following picture you see the corresponding templates from the PKI SnapIn

image

 

function Get-PublishedCATemplate{              
    [CmdletBinding()]
    Param (
        [parameter()]
        [string]$filter    
    )       
    $FilterLen = ("msPKI-Cert-Template-OID =").length+3    
    $AllPublishedTemplates = Invoke-Expression "certutil.exe –catemplates –v | select-string msPKI-Cert-Template-OID"      
    $AllPublishedTemplates | foreach{        
        $tmp= ($_.line).Substring($FilterLen)        
        $splitarr = $tmp.split(" ",2)      
        $obj = New-Object PSObject                                      
        Add-Member -Input $obj -Name "name" -MemberType Noteproperty -Value $Splitarr[1].trim()
        Add-Member -Input $obj -Name "oid" -MemberType Noteproperty -Value $Splitarr[0].trim()               
        if ($PSBoundParameters["filter"]){   
            if ($Splitarr[1].trim() -match $filter){
                write-output $obj              
            }
        }
        else{
            write-output $obj              
        }
    }               
}

 

below I run the script with the –filter parameter and so I only get templates with “SCCM” in their name

image

I assign the oid of ONE template (=> change filter that you get only one result!)  to the variable WSTemplate

$WSTemplate = (Get-PublishedCATemplate -filter workstation).oid

image

 

 

B.) Get-IssuedCert

With this function  you can list the certificates  issued from all templates or a certain template (specified with it’s oid = $CertTemplate variable)  which are issued beginning at a certain date.

 

function Get-IssuedCert{
  [CmdletBinding()]
  Param (
    [ValidatePattern('^([0-9\.\s])+$')]
    [string]$CertTemplate,
    [ValidatePattern('^\d\d[\./]{1}\d\d[\./]{1}\d\d\d\d$')]
    [string]$Date
  )
  if ($PSBoundParameters["CertTemplate"]){    
    Invoke-Expression "certutil.exe -view -restrict 'certificate template=$CertTemplate,disposition=20,notbefore>=$Date' -out 'Request.RequestID,Request.RequesterName,NotBefore,NotAfter,Request.Disposition'"       
  }
  else {
    # displays Certificates issued with any custom template    
    Invoke-Expression "certutil.exe -view -restrict 'disposition=20,notbefore>=$Date' -out 'Request.RequestID,Request.RequesterName,NotBefore,NotAfter,Request.Disposition'"             
  }
}

 

The following example lists all  29 certificates (from ALL templates) issued from December 18. 2014 and later …. (with this version it’s not possible to select a time range / only a “start-date”)

Get-IssuedCert  -Date 18.12.2014

image

 

The following example lists ONLY the 3 certificates which are issued with the Template $WSTemplate (OID of  “…- Workstation – Authentication Certificate”) beginning December 18. 2014

$WSTemplate = (Get-PublishedCATemplate -filter workstation).oid
Get-IssuedCert -CertTemplate $WSTemplate -Date 18.12.2014

 

image

 

C.) Remove-ExpiredCertFromDB

This is an advanced function and all available parameters are displayed with the get-help command

- the expired certificates to view (1st step) and then delete are in one of 4 folders

image

- I select this “folder” with the –state parameter

image

- the script creates a log file (also needed for further parsing!) in a separate folder

image

these folders are created automatically if they don’t exist yet.

 

In a first step always run the cmdlet without the "-delete" parameter so nothing is really deleted

I also recommend the ISE instead of the shell

And I also always run this cmdlet with the “-verbose” parameter!

 

The following example displays all issued (and expired) certificates till 18.12.2014  --- they are not really deleted yet!

Remove-ExpiredCertFromDB -State issued -Date 18.12.2014  -Verbose

image

without the –delete switch parameter  - the log file has "-ViewOnly" in it’s name

below is the output from the example above / 396 entries “would be” deleted from the “issued folder” (or category) if you run the cmdlet with “-delete”

image

 

The following example lists / deletes  certificates from a certain (workstation authentication) template expired up to Dec. 18 2014

$WSTemplate = (Get-PublishedCATemplate -filter workstation).oid
Remove-ExpiredCertFromDB -State issued -CertTemplate $WSTemplate -Date 18.12.2014 -Verbose

 

image

 

With the added –delete switch parameter you really delete the entries…this step can take some time if there are a lot of entries!

$WSTemplate = (Get-PublishedCATemplate -filter workstation).oid
Remove-ExpiredCertFromDB -State issued -CertTemplate $WSTemplate -Date 18.12.2014 -Verbose -delete

image

the output at the end (and the log file)

image

when  you run the same cmdlet again, you see that there aren’t any entries to delete from the db

image

 

The latest (full) version of this script with the 3 functions you can download from the Microsoft Script Gallery: go to download