Certificate Management | PowerShell Commands to Get Field Data from an SSL Certificate File for Populating the template to Bulk Upload Certificates - Support and Troubleshooting

Certificate Management | PowerShell Commands to Get Field Data from an SSL Certificate File for Populating the template to Bulk Upload Certificates Summary .ns-kb-css-body-editor-container { p { font-size: 12pt; font-family: Lato; color: #000000; } span { font-size: 12pt; font-family: Lato; color: #000000; } h2 { font-size: 24pt; font-family: Lato; color: black; } h3 { font-size: 18pt; font-family: Lato; color: black; } h4 { font-size: 14pt; font-family: Lato; color: black; } a { font-size: 12pt; font-family: Lato; color: #00718F; } a:hover { font-size: 12pt; color: #024F69; } a:target { font-size: 12pt; color: #032D42; } a:visited { font-size: 12pt; color: #00718f; } ul { font-size: 12pt; font-family: Lato; } li { font-size: 12pt; font-family: Lato; } img { display: ; max-width: ; width: ; height: ; } } This article contains PowerShell commands that can be run against an exported SSL Certificate file to get the values needed from the certificate to be populated to the cmdb_ci_certificate.xlsx template downloaded from the Bulk Upload Certificates module within the Certificate Management application on ServiceNow instances.

The Documentation on Uploading Certificates in Bulk assumes the implementer already knows what exactly should be migrated from a certificate and only provides the field names on the template/cmdb_ci_certificate table.

To help customers that may not be sure what to populate from the certificate to the template, t hese are one to one commands that will return only a single value from the certificate for the single corresponding field on the template that it maps to like below example output.

fingerprint: 19BEBA7BBDFB15B7FD24C04BE0AE2EF9131BC702

Optionally, to speed up the process, the last command is a one liner command that will print out all of the available values from the certificate and the template fields they map to in one go.

Release .ns-kb-css-body-editor-container { p { font-size: 12pt; font-family: Lato; color: #000000; } span { font-size: 12pt; font-family: Lato; color: #000000; } h2 { font-size: 24pt; font-family: Lato; color: black; } h3 { font-size: 18pt; font-family: Lato; color: black; } h4 { font-size: 14pt; font-family: Lato; color: black; } a { font-size: 12pt; font-family: Lato; color: #00718F; } a:hover { font-size: 12pt; color: #024F69; } a:target { font-size: 12pt; color: #032D42; } a:visited { font-size: 12pt; color: #00718f; } ul { font-size: 12pt; font-family: Lato; } li { font-size: 12pt; font-family: Lato; } img { display: ; max-width: ; width: ; height: ; } } All

Instructions .ns-kb-css-body-editor-container { p { font-size: 12pt; font-family: Lato; color: #000000; } span { font-size: 12pt; font-family: Lato; color: #000000; } h2 { font-size: 24pt; font-family: Lato; color: black; } h3 { font-size: 18pt; font-family: Lato; color: black; } h4 { font-size: 14pt; font-family: Lato; color: black; } a { font-size: 12pt; font-family: Lato; color: #00718F; } a:hover { font-size: 12pt; color: #024F69; } a:target { font-size: 12pt; color: #032D42; } a:visited { font-size: 12pt; color: #00718f; } ul { font-size: 12pt; font-family: Lato; } li { font-size: 12pt; font-family: Lato; } img { display: ; max-width: ; width: ; height: ; } } I. One to One Field Mapping Commands

The below list of PowerShell commands will each return the indicated field value from an SSL Certificate file.

1. Replace in each command with the full path to the SSL Certificate file.

2. Then run the command in PowerShell.

version: $pemPath = " ";$cert = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2($pemPath);Write-Host "version: $($cert.Version)" valid_from : $pemPath = " ";$cert = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2($pemPath);Write-Host "valid_from: $($cert.NotBefore)" ** Important: This returns the time in MM/DD/YY HH:MM:SS format and has to be converted to time in milliseconds using any online Unix Epoch Time converter tool. valid_to: $pemPath = " ";$cert = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2($pemPath);Write-Host "valid_to: $($cert.NotAfter)" ** Important: This returns the time in MM/DD/YY HH:MM:SS format and has to be converted to time in milliseconds using any online Unix Epoch Time converter tool. serial_number: $pemPath = " ";$cert = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2($pemPath);Write-Host "serial_number: $($cert.SerialNumber)" subject_distinguished_name: $pemPath = " ";$cert = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2($pemPath);Write-Host "subject_distinguished_name: $($cert.Subject)" subject_common_name: $pemPath = " ";$cert = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2($pemPath);$subject = $cert.Subject;$subject -split ",? ?(?=[COSLE][NU]?\=)" | ForEach-Object { if($_ -match "CN="){$CN = $_ -replace "CN=";Write-Host "subject_common_name: $CN" }} subject_organization: $pemPath = " ";$cert = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2($pemPath);$subject = $cert.Subject;$subject -split ",? ?(?=[COSLE][NU]?\=)" | ForEach-Object { if($_ -match "O="){$O = $_ -replace "O=";Write-Host "subject_organization: $O" }} subject_organizational_unit: $pemPath = " ";$cert = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2($pemPath);$subject = $cert.Subject;$subject -split ",? ?(?=[COSLE][NU]?\=)" | ForEach-Object {if($_ -match "OU="){$OU = $_ -replace "OU=";Write-Host "subject_organizational_unit: $OU" } } subject_email: $pemPath = " ";$cert = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2($pemPath);$subject = $cert.Subject;$subject -split ",? ?(?=[COSLE][NU]?\=)" | ForEach-Object {if($_ -match "E="){$E = $_ -replace "E=";Write-Host "subject_email: $E" } } subject_country: $pemPath = " ";$cert = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2($pemPath);$subject = $cert.Subject;$subject -split ",? ?(?=[COSLE][NU]?\=)" | ForEach-Object {if($_ -match "C="){$C = $_ -replace "C=";Write-Host "subject_country: $C" } } subject_state: $pemPath = " ";$cert = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2($pemPath);$subject = $cert.Subject;$subject -split ",? ?(?=[COSLE][NU]?\=)" | ForEach-Object {if($_ -match "S="){$S = $_ -replace "S=";Write-Host "subject_state: $S" } } subject_locality: $pemPath = " ";$cert = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2($pemPath);$subject = $cert.Subject;$subject -split ",? ?(?=[COSLE][NU]?\=)" | ForEach-Object {if($_ -match "L="){$L = $_ -replace "L=";Write-Host "subject_localization: $L" } } subject_alternative_name: $pemPath = " ";$cert = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2($pemPath);$sanExtension = $cert.Extensions | Where-Object { $_.Oid.FriendlyName -eq "Subject Alternative Name" };$caExtension = $cert.Extensions;$sanData = $sanExtension.Format($true);Write-Host "subject_alternative_name:";Write-Host $sanData issuer_distinguished_name: $pemPath = " ";$cert = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2($pemPath);$issuer =$cert.Issuer; Write-Host "issuer_distinguished_name: $($cert.Issuer)" issuer_common_name: $pemPath = " ";$cert = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2($pemPath);$issuer =$cert.Issuer;$issuer -split ",? ?(?=[COSLE][NU]?\=)" | ForEach-Object { if($_ -match "CN="){$issuerCN = $_ -replace "CN=";Write-Host "issuer_common_name: $issuerCN" }} fingerprint: $pemPath = " ";$cert = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2($pemPath);Write-Host "fingerprint: $($cert.Thumbprint)" fingerprint_algorithm: $pemPath = " ";$cert = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2($pemPath);$fingerprintAlgorithm = $cert.SignatureAlgorithm.FriendlyName;if($fingerprintAlgorithm -match '^(sha\d+|sha\d+)|(md5)') {$fingerprintAlgorithm = $Matches[0].ToUpper()};Write-Host "fingerprint_algorithm: $($fingerprintAlgorithm)" key_size: $pemPath = " ";$cert = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2($pemPath);$rsaKey = $cert.PublicKey.Key; Write-Host "key_size: $($rsaKey.KeySize)" signature_algorithm: $pemPath = " ";$cert = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2($pemPath);Write-Host "signature_algorithm: $($cert.SignatureAlgorithm.FriendlyName)" is_ca: $pemPath = " ";$cert = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2($pemPath);$sanExtension = $cert.Extensions | Where-Object { $_.Oid.FriendlyName -eq "Basic Constraints" }; Write-Host "is_ca:"$caExtension.CertificateAuthority.ToString().ToUpper() II. One Liner PowerShell Command to Get and Print All Certificate Field Values at once 1. Replace at the beginning of this command with the full path to the SSL Certificate file. 2. Run it as a single line command in PowerShell. $pemPath = " ";$cert = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2($pemPath);Write-Host "`n`n";Write-Host "subject_distinguished_name: $($cert.Subject)";$subject = $cert.Subject; if ($subject) {$subject -split ",? ?(?=[COSLE][NU]?\=)" | ForEach-Object { if($_ -match "CN="){$CN = $_ -replace "CN=";Write-Host "subject_common_name: $CN" }elseif($_ -match "O="){$O = $_ -replace "O=";Write-Host "subject_organization: $O" }elseif($_ -match "OU="){$OU = $_ -replace "OU=";Write-Host "subject_organizational_unit: $OU" }elseif($_ -match "C="){$C = $_ -replace "C=";Write-Host "subject_country: $C" }elseif($_ -match "S="){$S = $_ -replace "S=";Write-Host "subject_state: $S" }elseif($_ -match "L="){$L = $_ -replace "L=";Write-Host "subject_localization: $L" }elseif($_ -match "E="){$E = $_ -replace "E=";Write-Host "subject_email: $E" }}};$issuer =$cert.Issuer; Write-Host "issuer_distinguished_name: $($cert.Issuer)";if ($issuer) {$issuer -split "\s*,\s*" | ForEach-Object { if($_ -match "CN="){$issuerCN = $_ -replace "CN=";Write-Host "issuer_common_name: $issuerCN" }}};Write-Host "valid_from: $($cert.NotBefore)";Write-Host "valid_to: $($cert.NotAfter)";Write-Host "fingerprint: $($cert.Thumbprint)";$fingerprintAlgorithm = $cert.SignatureAlgorithm.FriendlyName;if($fingerprintAlgorithm -match '^(sha\d+|sha\d+)|(md5)') {$fingerprintAlgorithm = $Matches[0].ToUpper()};Write-Host "fingerprint_algorithm: $($fingerprintAlgorithm)";Write-Host "serial_number: $($cert.SerialNumber)"; Write-Host "version: $($cert.Version)";Write-Host "signature_algorithm: $($cert.SignatureAlgorithm.FriendlyName)";$rsaKey = $cert.PublicKey.Key; Write-Host "key_size: $($rsaKey.KeySize)";$sanExtension = $cert.Extensions | Where-Object { $_.Oid.FriendlyName -eq "Subject Alternative Name" };$caExtension = $cert.Extensions | Where-Object { $_.Oid.FriendlyName -eq "Basic Constraints" }; Write-Host "is_ca:"$caExtension.CertificateAuthority.ToString().ToUpper();if ($sanExtension) {$sanData = $sanExtension.Format($true);Write-Host "subject_alternative_name:";Write-Host $sanData}