This a shameless copy of the original post by André Gibel over at https://www.gibel.net/
The reason why I copied it is because there is very little info on this subject and even though the post is from 2014, it still applies today.
Regularly (depending on the number of issued certificates) you have to perform a clean-up of expired certificates from your CA (Certification Authority) DB and then shrink the DB to get rid of the “white space”.
You have to perform the following 3 steps in order:
1. Make a backup of your CA DB (protected with a password) to another Server / medium
2. Clean-up all
expired certificates from all 4 categories with my PowerShell Script
- the script and all the details are explained below.
3. Shrink your CA database to get rid of the
“whitespace”
- run the
following command with the path to the .edb DB file
- at the end
the DB - file is more than
100 MB smaller than before, depending on the size
your database is.
- at this
point you have to enable and start the CA Service again
Here I explain the PowerShell script
in detail (the script is used in step 2)
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
Per default
the functions "Remove-ExpiredCertFromDB" writes the temporary files
to a subfolder within C:\_scripts\PKICleanupLog.
You can change this
default folder path with the parameter “CleanedFolderLogPath”
The 3
functions I have implemented are:
A.)
Get-PublishedCATemplate
B.) Get-IssuedCert
C.) Remove-ExpiredCertFromDB
A.) Get-PublishedCATemplate
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
}
}
}
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
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 18 December 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
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
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
- I select
this “folder” with the -state parameter
- the script
creates a log file (also needed for further parsing) in a separate folder
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
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”
The following
example lists / deletes certificates from a certain (workstation
authentication) template expired up to 18
december 2014
$WSTemplate =
(Get-PublishedCATemplate -filter workstation).oid
Remove-ExpiredCertFromDB -State issued -CertTemplate $WSTemplate -Date
18.12.2014 -Verbose
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
The output at the end (and the log file)
When you run the same cmdlet again, you see that there aren’t
any entries to delete from the DB
The latest
(full) version of this script with the 3 functions you can download from the
Microsoft Script Gallery: go
to download
Since
TechNet is retired and will be taken offline any time soon a backup can be
downloaded here:
you left out how to run these commands? i know you know how to do this stuff, but i'm running the powershell and nothing happens. how do i actually run these functions. can you include the steps to actually run the powershell functions
ReplyDeleteYou have to dot source the script to run the functions in the script, like so:
ReplyDelete". .\Cleanup_MSPKI_Cert_v1.1.ps1"
And the you can run one of the 3 functions in the script, like so:
"Get-IssuedCert -Date 18.12.2021"
A little more information on the dot sourcing. Took me forever to figure it out!
ReplyDeleteFIrst, you can only run the script from ISE IF you run ISE as Administrator. Better to just launch PowerShell As Administrator.
2nd, CD to the folder where your script is. Mine was C:\Scripts so I had to modify the script to change it from _scripts.
3rd, to dot source the script, enter it like this: . .\Cleanup_MSPKI_Cert_v1.2.ps1. If you just type clean & press tab after you put in the 1st dot, it'll fill in the script name for you. Press Enter to load the script.
4th, enter the function that you want to use. I only used the Get-IssuedCert & Remove-ExpiredCertFromDB functions. If you're in the US, you have to enter the dates using US date format (MM/DD/YYYY), not the dotted European format shown in the examples above
Your the best!
ReplyDeleteI have trouble running the script. Trying to call the function Get-PublishedCATemplate or just dot sourcing the script generates this error:
ReplyDeleteforeach : You cannot call a method on a null-valued expression.
At C:\_scripts\Cleanup_MSPKI_Cert_v1.2.ps1:41 char:30
+ $AllPublishedTemplates | foreach{
+ ~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [ForEach-Object], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull,Microsoft.PowerShell.Commands.ForEachObjectCommand
I have tried looking at the different variables contents but not yet able to figure out why it fails. Tried ISE as admin and a powershell windows as admin.
It looks like $PSBoundParameters returns NULL.
Let me clear that up. I meant $PSBoundParameters contains NULL (when fed a parameter with -filter).
DeleteBut looking more into it, it rather seams to fail after outputting the first template in the .
Update: Some old templates do not have the template name at the end of the OID. This fails the script when inserting an empty field into the $obj. Using display name could be a solution.
Delete