如何导出带SAN扩展的证书并优化certutil导出CSV可读性

原有脚本可读性调整 #

你当前使用的原生certutil导出命令存在两个影响可读性的问题：直接重定向输出的文件会夹带certutil运行日志、命令统计行，且默认输出编码为GBK，部分表格软件打开会出现乱码。可以先做基础优化，调整后导出的标准csv无冗余内容，打开即可正常查看：

$Local = $PSScriptRoot
$File = Join-Path $Local "IssuedCerts.csv"
# 导出字段可按需调整
$Header = "Request ID,Requester Name,Certificate Template,Serial Number,Certificate Effective Date,Certificate Expiration Date,Issued Country/Region,Issued Organization,Issued Organization Unit,Issued Common Name,Issued City,Issued State,Issued Email Address"

# 过滤certutil输出的冗余日志行，统一用UTF8编码输出
certutil -view -out $Header csv | 
    Where-Object { $_ -notmatch '^(CertUtil|  Row|  Maximum|The command completed successfully)' } | 
    Out-File $File -Encoding utf8

核心需求实现：导出即将过期证书+SAN扩展数据 #

certutil原生导出不支持直接输出格式化的SAN字段，且没有内置过期筛选能力，可以通过解析证书原始扩展字段+过期时间判断实现需求，脚本支持自定义即将过期的时间阈值（默认取30天内过期的证书），SAN字段会自动解析为分号分隔的可读格式（包含DNS名称、IP、邮箱等所有SAN条目）：

# 配置项
$Local = $PSScriptRoot
$outFile = Join-Path $Local "ExpiringCerts_WithSAN.csv"
$expireThresholdDays = 30 # 自定义过期阈值，单位为天，即导出多少天内即将过期的证书
$deadline = (Get-Date).AddDays($expireThresholdDays)

# 基础导出字段，额外保留证书核心标识字段用于匹配详情
$baseFields = "Request ID,Requester Name,Certificate Template,Serial Number,Certificate Effective Date,Certificate Expiration Date,Issued Common Name,Issued Email Address"
$rawCsv = certutil -view -out $baseFields csv | 
    Where-Object { $_ -notmatch '^(CertUtil|  Row|  Maximum|The command completed successfully|$)' } | 
    ConvertFrom-Csv

$result = foreach ($certRow in $rawCsv) {
    # 解析证书过期时间，过滤不在过期阈值范围内的证书
    try {
        $expireDate = [datetime]::ParseExact($certRow.'Certificate Expiration Date', 'M/d/yyyy h:mm:ss tt', [Globalization.CultureInfo]::GetCultureInfo('en-US'))
        if ($expireDate -gt $deadline) { continue }
    }
    catch { continue }

    # 拉取单张证书的扩展信息，解析SAN字段
    $certDetail = certutil -view -restrict "Request ID=$($certRow.'Request ID')" -out "Certificate Extensions"
    $sanContent = $certDetail | Select-String -Pattern 'Subject Alternative Name\(s\):(.*?)(?=\n\s*\n|  CertUtil)' -Context 0,10
    $sanList = @()
    if ($sanContent) {
        $sanMatches = [regex]::Matches($sanContent.Value, 'DNS Name=(.*?)(?=\s|$)|IP Address=(.*?)(?=\s|$)|RFC822 Name=(.*?)(?=\s|$)')
        foreach ($match in $sanMatches) {
            if ($match.Groups[1].Value) { $sanList += "DNS:$($match.Groups[1].Value)" }
            if ($match.Groups[2].Value) { $sanList += "IP:$($match.Groups[2].Value)" }
            if ($match.Groups[3].Value) { $sanList += "Email:$($match.Groups[3].Value)" }
        }
    }

    # 组装输出结果
    [PSCustomObject]@{
        'Request ID' = $certRow.'Request ID'
        'Requester Name' = $certRow.'Requester Name'
        'Certificate Template' = $certRow.'Certificate Template'
        'Serial Number' = $certRow.'Serial Number'
        'Certificate Effective Date' = $certRow.'Certificate Effective Date'
        'Certificate Expiration Date' = $certRow.'Certificate Expiration Date'
        'Issued Common Name' = $certRow.'Issued Common Name'
        'Issued Email Address' = $certRow.'Issued Email Address'
        'SAN Values' = $sanList -join '; '
    }
}

# 导出最终结果文件
$result | Export-Csv -Path $outFile -NoTypeInformation -Encoding utf8

使用说明 #

• 直接在CA服务器上运行该脚本即可，不需要额外安装第三方模块
• 调整$expireThresholdDays的值即可修改过期筛选范围，比如设置为90就是导出90天内过期的证书
• 导出的SAN字段会标注条目类型，多个条目用分号分隔，可直接在表格软件中筛选查看

内容的提问来源于stack exchange，提问作者Mark Delphi