What a Certificate Inventory Actually Looks Like (Before vs. After CLM)

The Spreadsheet Reality

Let’s be honest about what certificate inventory looks like at most organizations. It’s a spreadsheet. Maybe it’s a SharePoint list. Maybe it’s a Confluence page that hasn’t been updated in 8 months.

Here’s a real (anonymized) example of what I’ve seen in production environments:

"cert-inventory-2024-FINAL-v3-UPDATED.xlsx"
Last modified: 14 months ago
Shared with: 3 people (one has left the company)
Rows: 47 (actual certificate count: 312)

This post shows exactly what the before and after looks like when an organization moves from ad-hoc tracking to proper Certificate Lifecycle Management.

The “Before” State: Spreadsheet Tracking

What the Spreadsheet Typically Contains

| Server Name | Cert Type | Expiry Date | Owner | Notes          |
|-------------|-----------|-------------|-------|----------------|
| PROD-WEB01  | SSL       | 2025-03-15  | John  |                |
| PROD-WEB02  | SSL       | 2025-03-15  | John  | same as WEB01? |
| VPN         | IKE       | ???         | IT    | check this     |
| mail        | Exchange  | 2025-06-01  | Mike  | Mike left      |
| API server  | public    | see email   |       |                |

Common Spreadsheet Problems

Problem	Frequency	Impact
Missing entries	85% of inventories	Certs expire without warning
Stale data	90% of inventories	False sense of security
No owner assigned	60% of entries	Nobody accountable
Ambiguous naming	75% of entries	Can’t map cert to system
No expiry alerts	95% of spreadsheets	Reactive only
No renewal history	99% of spreadsheets	Repeat mistakes

AD CS vs. CLM: Capability Coverage

Each tool excels in its domain — together they provide full lifecycle coverage

AD CS = Issuance Engine

Templates, enrollment, renewal, key generation

CLM = Visibility Layer

Inventory, alerts, deployment, ownership, compliance

The Real Data Model Gap

A spreadsheet captures maybe 5-8 fields per certificate. A proper inventory needs 25-40 fields to be operationally useful:

What spreadsheets track:          What you actually need:
━━━━━━━━━━━━━━━━━━━━━━━━━━       ━━━━━━━━━━━━━━━━━━━━━━━━━━
□ Server name                     ■ FQDN (all SANs)
□ "Certificate type"              ■ Certificate serial number
□ Expiry date                     ■ SHA-256 fingerprint
□ Owner name                      ■ Issuing CA + chain
□ Notes                           ■ Key algorithm + size
                                  ■ Signature algorithm
                                  ■ Issue date
                                  ■ Expiry date
                                  ■ Auto-renewal status
                                  ■ Deployment locations (all)
                                  ■ Associated private key hash
                                  ■ Certificate template (if AD CS)
                                  ■ Compliance status
                                  ■ Business owner
                                  ■ Technical owner
                                  ■ Application/service mapping
                                  ■ Environment (prod/staging/dev)
                                  ■ Renewal source
                                  ■ Last scan date
                                  ■ Discovery method
                                  ■ Risk score
                                  ■ Vulnerability flags
                                  ■ Related incident history
                                  ■ Change history

The “After” State: CLM Dashboard

What Good Inventory Contains

A properly managed certificate inventory provides:

1. Complete Discovery Data

{
  "certificate": {
    "serial_number": "04:A3:B7:C9:D2:E1:F0:8A",
    "fingerprint_sha256": "2B:45:A8:...",
    "subject": {
      "common_name": "api.company.com",
      "organization": "Company Inc",
      "country": "US"
    },
    "san_entries": [
      "api.company.com",
      "api-v2.company.com",
      "internal-api.company.com"
    ],
    "issuer": {
      "common_name": "DigiCert TLS RSA SHA256 2020 CA1",
      "organization": "DigiCert Inc"
    },
    "validity": {
      "not_before": "2026-01-15T00:00:00Z",
      "not_after": "2027-01-15T23:59:59Z",
      "days_remaining": 182,
      "lifetime_days": 365
    },
    "key_info": {
      "algorithm": "RSA",
      "size": 2048,
      "public_key_hash": "SHA256:abc123..."
    },
    "signature_algorithm": "SHA256withRSA"
  },
  "deployment": {
    "locations": [
      {
        "host": "prod-lb-01.internal",
        "port": 443,
        "platform": "F5 BIG-IP",
        "last_verified": "2026-07-17T06:00:00Z"
      },
      {
        "host": "prod-lb-02.internal",
        "port": 443,
        "platform": "F5 BIG-IP",
        "last_verified": "2026-07-17T06:00:00Z"
      }
    ],
    "total_installations": 2
  },
  "ownership": {
    "business_owner": "API Platform Team",
    "technical_contact": "sarah.chen@company.com",
    "cost_center": "CC-4521",
    "application": "Customer API Gateway"
  },
  "lifecycle": {
    "renewal_method": "automated",
    "renewal_source": "DigiCert CertCentral API",
    "auto_deploy": true,
    "last_renewal": "2026-01-14T14:30:00Z",
    "renewal_count": 3,
    "incidents": []
  },
  "compliance": {
    "meets_policy": true,
    "minimum_key_size": "pass",
    "approved_ca": "pass",
    "maximum_lifetime": "pass",
    "pci_scope": true,
    "sox_relevant": false
  }
}

2. Relationship Mapping

Certificate: *.company.com (wildcard)
├── Deployed to: prod-web-01 (nginx)
├── Deployed to: prod-web-02 (nginx)  
├── Deployed to: prod-lb-01 (F5)
├── Used by: Customer Portal (app)
├── Used by: Partner API (app)
├── Issued by: DigiCert (public CA)
├── Template: N/A (public cert)
└── Renewal: Automated via ACME
    ├── Last renewed: 2026-01-15
    ├── Next renewal: 2026-11-16 (auto)
    └── Contacts notified: 3 people

3. Operational Dashboard Metrics

┌─────────────────────────────────────────────────────┐
│  CERTIFICATE INVENTORY DASHBOARD                     │
├─────────────────────────────────────────────────────┤
│                                                      │
│  Total Certificates:  312    │  Compliance: 94%     │
│  Expiring <30 days:   4     │  Auto-renew: 67%    │
│  Expired:              0     │  Coverage:   89%     │
│                                                      │
│  By Environment:              By CA:                 │
│  ├── Production: 89          ├── DigiCert: 112      │
│  ├── Staging: 45             ├── Let's Encrypt: 98  │
│  ├── Development: 67         ├── Internal CA: 78    │
│  └── Infrastructure: 111     └── Sectigo: 24       │
│                                                      │
│  Risk Indicators:                                    │
│  ⚠️  3 certs with weak keys (RSA 1024)              │
│  ⚠️  2 certs nearing max lifetime policy            │
│  ✅  0 expired certificates                         │
│  ✅  All production certs have auto-renewal         │
└─────────────────────────────────────────────────────┘

Before vs. After: The Metrics

Metric	Before (Spreadsheet)	After (CLM)
Certificates tracked	47 (of 312)	312 (100%)
Accuracy of data	~60%	99%+ (automated)
Time to find a cert	15-30 minutes	<5 seconds
Expiry visibility	14 days (if lucky)	90 days (policy-based)
Owner assignment	30% have owners	100% required
Renewal automation	0%	67%
Audit preparation time	2-3 weeks	<1 hour
Mean time to detect expiry	After outage	90 days before
Outages per year (cert-related)	4-6	0
Compliance audit findings	12	0

The Data Model: What a CLM Platform Stores

Core Certificate Record

-- Simplified schema for certificate inventory
CREATE TABLE certificates (
  id UUID PRIMARY KEY,
  serial_number VARCHAR(100) UNIQUE,
  fingerprint_sha256 VARCHAR(64) UNIQUE,
  common_name VARCHAR(255),
  subject_dn TEXT,
  issuer_dn TEXT,
  issuer_ca_id UUID REFERENCES certificate_authorities(id),
  not_before TIMESTAMP,
  not_after TIMESTAMP,
  key_algorithm VARCHAR(20),
  key_size INTEGER,
  signature_algorithm VARCHAR(50),
  is_wildcard BOOLEAN,
  is_self_signed BOOLEAN,
  certificate_pem TEXT,
  discovery_method VARCHAR(50),
  first_discovered TIMESTAMP,
  last_seen TIMESTAMP,
  status VARCHAR(20), -- active, expired, revoked, pending
  risk_score INTEGER,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Subject Alternative Names
CREATE TABLE certificate_sans (
  id UUID PRIMARY KEY,
  certificate_id UUID REFERENCES certificates(id),
  san_type VARCHAR(20), -- dns, ip, email, uri
  san_value VARCHAR(255)
);

-- Deployment locations
CREATE TABLE deployments (
  id UUID PRIMARY KEY,
  certificate_id UUID REFERENCES certificates(id),
  hostname VARCHAR(255),
  port INTEGER,
  platform VARCHAR(100),
  ip_address INET,
  last_verified TIMESTAMP,
  verification_status VARCHAR(20)
);

-- Ownership and accountability
CREATE TABLE certificate_ownership (
  id UUID PRIMARY KEY,
  certificate_id UUID REFERENCES certificates(id),
  business_owner VARCHAR(255),
  technical_owner VARCHAR(255),
  team VARCHAR(100),
  application VARCHAR(255),
  environment VARCHAR(50),
  cost_center VARCHAR(50)
);

Relationship Tracking

-- Certificate chains
CREATE TABLE certificate_chains (
  child_cert_id UUID REFERENCES certificates(id),
  parent_cert_id UUID REFERENCES certificates(id),
  chain_position INTEGER,
  PRIMARY KEY (child_cert_id, parent_cert_id)
);

-- Renewal history
CREATE TABLE renewal_history (
  id UUID PRIMARY KEY,
  old_certificate_id UUID REFERENCES certificates(id),
  new_certificate_id UUID REFERENCES certificates(id),
  renewal_date TIMESTAMP,
  renewal_method VARCHAR(50),
  automated BOOLEAN,
  triggered_by VARCHAR(255)
);

Migration Path: Spreadsheet to CLM

Phase 1: Discovery (Week 1-2)

Step 1: Run automated discovery scans
  - Network scanning (internal)
  - CT log queries (external)
  - AD CS database export
  - Cloud provider cert inventory (ACM, Key Vault, etc.)

Step 2: Import existing spreadsheet data
  - Map spreadsheet columns to CLM fields
  - Flag entries that can't be validated
  - Mark discovered-but-not-in-spreadsheet certs as "new"

Step 3: Reconcile
  - Match spreadsheet entries to discovered certs
  - Identify spreadsheet entries with no matching cert (stale)
  - Identify discovered certs with no spreadsheet entry (gaps)

Phase 2: Enrichment (Week 2-3)

Step 4: Assign ownership
  - Map certificates to applications
  - Assign business and technical owners
  - Establish accountability chains

Step 5: Classify and tag
  - Environment (production, staging, dev)
  - Compliance scope (PCI, SOX, HIPAA)
  - Criticality (critical, high, medium, low)
  - Renewal method (manual, automated, hybrid)

Step 6: Establish policies
  - Minimum key sizes
  - Maximum certificate lifetimes
  - Approved CAs
  - Required fields for new certificates

Phase 3: Automation (Week 3-4)

Step 7: Configure alerts
  - 90-day expiry warning (informational)
  - 60-day expiry alert (action needed)
  - 30-day expiry critical (escalation)
  - 7-day expiry emergency (incident)

Step 8: Enable auto-renewal where possible
  - ACME-compatible certificates
  - Cloud-managed certificates
  - Internal CA with auto-enrollment

Step 9: Continuous discovery
  - Schedule recurring scans
  - Monitor CT logs for new issuances
  - Integrate with CI/CD for new deployments

ROI of the Migration

Cost Factor	Spreadsheet Approach	CLM Platform
Annual cert-related outages	4-6 × $50,000 avg	0
Staff time (manual tracking)	20 hrs/month	2 hrs/month
Audit preparation	80 hrs/audit	4 hrs/audit
Emergency renewal labor	30 hrs/year	0 hrs/year
Total annual cost	$300,000-$500,000	Platform license + setup

The math is straightforward: if your organization has more than 50 certificates, the cost of NOT having a CLM platform exceeds the cost of having one — usually within the first year.

---

About QCecuring

QCecuring provides the CLM platform that transforms certificate chaos into operational clarity. Our solution automates discovery, tracks every certificate across hybrid environments, and ensures you never experience a preventable certificate outage again.

Tags: certificate inventory, CLM, certificate lifecycle management, spreadsheet tracking, certificate management, data model, certificate discovery, automation, compliance, PKI governance