QueryInternalCA.ps1

$CaView = New-Object -Com CertificateAuthority.View.1
[void]$CaView.OpenConnection("SERVER\CA")

$templates = @{}
Get-ADObject -SearchBase (Get-ADRootDSE).ConfigurationNamingContext -filter {objectclass -eq "pKICertificateTemplate"} -Properties "CN", "DisplayName", "msPKI-Cert-Template-OID" | %{ $templates.add($_."msPKI-Cert-Template-OID",$_."DisplayName") }

$columns = @(
    "Binary Certificate",
    "Certificate Effective Date",
    "Certificate Expiration Date", 
    "Request Disposition",
    "Certificate Template",
    "Issued Organization Unit",
    "Issued Email Address",
    "Requester Name"
)

$CaView.SetResultColumnCount($columns.count)

$columns | %{ 
    $index = $CaView.GetColumnIndex($false, $_)
    #if ($_ -eq "Certificate Expiration Date") {
    #    $CaView.SetRestriction($index,16,0,(Get-Date))  
    #}
    if ($_ -eq "Request Disposition") {
        # brief disposition code explanation:
        # 9 - pending for approval
        # 15 - CA certificate renewal
        # 16 - CA certificate chain
        # 20 - issued certificates
        # 21 - revoked certificates
        # all other - failed requests
        $CaView.SetRestriction($index,1,0,20)    
    }
    if ($_ -eq "Certificate Effective Date") {
        # CVR_SORT_NONE 0
        # CVR_SEEK_EQ  1
        # CVR_SEEK_LT  2
        # CVR_SEEK_GT  16
        $CaView.SetRestriction($index,16,0,((Get-Date).AddDays(-90)))
        #$CaView.SetRestriction($index,2,0,((Get-Date).AddDays(-730)))
    }
    $CaView.SetResultColumn($index)
}

$RowObj= $CaView.OpenView()
$certArr = @()
while ($Rowobj.Next() -ne -1){
	$Cert = New-Object PsObject
	$ColObj = $RowObj.EnumCertViewColumn()
	[void]$ColObj.Next()
	do {
		$current = $ColObj.GetName()
        $value =  $ColObj.GetValue(1)
        $name = $ColObj.GetDisplayName()
        if ($current -eq "CertificateTemplate" -and $value -ne $null -and $templates.ContainsKey($value)) {
            $value = $templates[$value]
        }
        if ($current -eq "RawCertificate" ) {
            $x509 = New-Object System.security.cryptography.x509certificates.x509certificate2([system.Text.Encoding]::ASCII.GetBytes($value),0)
            $subject = $x509.GetNameInfo([System.Security.Cryptography.X509Certificates.X509NameType]::DnsName, $false) 
            if ($subject -eq "") { $subject = $x509.GetNameInfo([System.Security.Cryptography.X509Certificates.X509NameType]::DnsFromAlternativeName, $false) }
            if ($subject -eq "") { $subject = $x509.Subject }
            $Cert | Add-Member -MemberType NoteProperty "Subject" -Value $subject -Force
            $Cert | Add-Member -MemberType NoteProperty "Thumbprint" -Value $($x509.Thumbprint) -Force
            $Cert | Add-Member -MemberType NoteProperty "Serial Number" -Value $($x509.GetSerialNumberString()) -Force
            $Cert | Add-Member -MemberType NoteProperty "Issuer" -Value $($x509.GetIssuerName()) -Force
            $Cert | Add-Member -MemberType NoteProperty "Subject Alternative Name" -Value (($x509.Extensions | ?{ $_.Oid.FriendlyName -eq "Subject Alternative Name" } | %{ $_.Format(1) }) -join "") -Force
            $value = $([System.Convert]::ToBase64String($x509.GetRawCertData(), [System.Base64FormattingOptions]::InsertLineBreaks)) 
        }
		$Cert | Add-Member -MemberType NoteProperty $name -Value $value -Force  
	} until ($ColObj.Next() -eq -1)
	Clear-Variable ColObj
	$certArr += $Cert
}
$RowObj.Reset()


[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.Runtime") | Out-Null
$ctx = New-Object Microsoft.SharePoint.Client.ClientContext("http://sharepoint/")
$ctx.Credentials = [System.Net.CredentialCache]::DefaultCredentials
$list = $ctx.Web.Lists.GetByTitle("Internal Certificates")
$query = New-Object Microsoft.SharePoint.Client.CamlQuery
$query.ViewXml = "<View><Query></Query><RowLimit>4000</RowLimit></View>"
$items = $list.GetItems($query)
$ctx.Load($items)
$ctx.ExecuteQuery()
$thumbPrints = $items | %{ $_["Thumbprint"] }

$ignore = @("SCCMClientCert_AutoEnroll","Machine","QuickRDPService","QuickTLS","QuickTLS 2.0","Domain Controller","Computers 2008","Computers","Directory Email Replication","Domain Controller Authentication","Kerberos Authentication","1.3.6.1.4.1.311.21.8.14769547.14416007.9716902.9182016.6201848.218.9325047.14249600")
$x = 0
$certArr | %{
    if ($ignore -notcontains $_."Certificate Template" -and $thumbPrints -notcontains $_.Thumbprint) {
        Write-Host "$($_.Subject): $($_."Certificate Effective Date") - $($_."Certificate Expiration Date")"
        $createInfo = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
        $newItem = $list.AddItem($createInfo)
        $newItem["Title"] = $_.Subject
        $newItem["Thumbprint"] = $_.Thumbprint
        $newItem["SerialNumber"] = $_."Serial Number"
        $newItem["EffectiveDate"] = $_."Certificate Effective Date"
        $newItem["ExpirationDate"] = $_."Certificate Expiration Date"
        $newItem["Issuer"] = $_."Issuer"
        $newItem["SubjectAlternativeName"] = $_."Subject Alternative Name"
        $newItem["Template"] = $_."Certificate Template"
        $newItem["Certificate"] = $_."Binary Certificate"
        $newItem["Organization"] = $_."Issued Organization Unit"
        $newItem["EmailAddress"] = $_."Issued Email Address"
        $newItem["Requester"] = $_."Requester Name"
        $newItem.Update()
        $x++ 
        if ($x -ge 99) {
            $x = 0
            Write-Host "Updating..." -ForegroundColor "Cyan"
            $ctx.ExecuteQuery()
        }
    }
}
$ctx.ExecuteQuery()