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:

• File: “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:

• Server name
• “Certificate type”
• Expiry date
• Owner name
• Notes

What you actually need:

• FQDN (all SANs)
• Certificate serial number
• SHA-256 fingerprint
• Issuing CA + chain
• 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

Metric	Value	Metric	Value
Total Certificates	312	Compliance	94%
Expiring under 30 days	4	Auto-renew	67%
Expired	0	Coverage	89%

By Environment:

• Production: 89
• Staging: 45
• Development: 67
• Infrastructure: 111

By CA:

• DigiCert: 112
• Let’s Encrypt: 98
• Internal CA: 78
• 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	under 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	under 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)

1. Run automated discovery scans
   • Network scanning (internal)
   • CT log queries (external)
   • AD CS database export
   • Cloud provider cert inventory (ACM, Key Vault, etc.)
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”
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)

4. Assign ownership
   • Map certificates to applications
   • Assign business and technical owners
   • Establish accountability chains
5. Classify and tag
   • Environment (production, staging, dev)
   • Compliance scope (PCI, SOX, HIPAA)
   • Criticality (critical, high, medium, low)
   • Renewal method (manual, automated, hybrid)
6. Establish policies
   • Minimum key sizes
   • Maximum certificate lifetimes
   • Approved CAs
   • Required fields for new certificates

Phase 3: Automation (Week 3-4)

7. Configure alerts
   • 90-day expiry warning (informational)
   • 60-day expiry alert (action needed)
   • 30-day expiry critical (escalation)
   • 7-day expiry emergency (incident)
8. Enable auto-renewal where possible
   • ACME-compatible certificates
   • Cloud-managed certificates
   • Internal CA with auto-enrollment
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