Patch Management · Data Integrity · Multi-Client Reporting

Patch Reporting
Methodology.

Cross-Validation · Status Classification · Client Delivery

A repeatable, multi-source reporting framework built to account for every device — including those that wouldn't appear in a standard patch report. I cross-reference two data sets, classify devices by real-world patch status, de-duplicate at scale, and deliver per-client reports with visual summaries ready for review.

Data Sources Cross-Referenced

Status Classifications

93%

Patch Compliance Achieved

Client Reports Generated

See the Methodology → Get In Touch

// why_two_reports

📋

Report A — Patch status per device (installed / missing)

📊

Report B — All devices regardless of patch inventory

🔀

Merge — Cross-reference to catch offline & agentless gaps

The Foundation

Why One Report Isn't Enough

Standard patch reports only surface devices with active patch inventory. Offline machines, devices with broken agents, and those with Windows Update issues vanish entirely — creating a false picture of compliance.

// REPORT_A

📋

Patch Status Report

Tells you whether each known device has the current monthly update installed or missing. Reliable for active, healthy endpoints — but silently excludes devices that can't report in.

Installed / MissingActive DevicesPatch Inventory

// REPORT_B

📊

Full Device Inventory Report

Captures every managed device regardless of patch inventory status. This is the control set — it ensures no endpoint goes unaccounted for, including those that would otherwise be invisible in Report A.

All DevicesLast Contact DatePending Reboot Flag

// MERGE_LOGIC

🔀

Cross-Reference & Merge

Combining both reports with color-coded layers and duplicate detection ensures every device lands in the final working report exactly once — correctly classified, no gaps, no double-counting.

De-duplicationColor LayersStatus Classification

Step by Step

How I Build the Report

A deliberate sequence that preserves data integrity at every stage — from raw exports to a clean, classified, client-ready dataset.

Export Both Reports to Excel

Pull Report A (patch status) and Report B (full inventory) separately. Remove the unnecessary column from each to keep the dataset clean before any merging begins.

Isolate Pending Reboot Devices

Filter Report B on the pending reboot flag. Copy only those flagged devices — with headers — into a dedicated worksheet. These get classified separately before the main merge.

Merge Report A Into the Reboot Sheet

Paste the full Report A dataset below the reboot-flagged devices. Apply a distinct color to the pasted rows — this becomes Color 1, used later to visually track what came from where during de-duplication.

De-duplicate by Client & Computer Name

Run duplicate detection scoped to client name and computer name only. Highlight duplicates in Color 2. This surfaces devices that appear in both the reboot set and Report A — they should only count once.

Promote Clean Data to Working Report

Select all Color 1 rows (Report A data) from the merge sheet and paste them over the top of Report A, replacing it. This makes Report A the working report with reboot devices already accounted for.

Append Full Inventory & Remove Duplicates

Paste all of Report B at the bottom of the working report as Color 3. Run de-duplication again scoped to client and computer name — this time selecting and deleting the duplicates to leave only unique records.

Align Columns & Classify Status

Insert columns where needed to align serial numbers and related fields across both data sets. Clear irrelevant true/false flags from the update type column. The working report now has consistent structure across all rows.

Apply Final Status Classifications

Sort by pending reboot color → mark as "1 — Installed." Filter by last contact date before patch release → mark as "3 — Offline Since Patch Release." Find/replace remaining 0s → "2 — Missing" and 1s → "1 — Installed."

Data Interpretation

What Each Status Means

Every device in the final report lands in one of four classifications. Each one is a deliberate decision — not a default — based on cross-referencing multiple signals.

1 — Installed

Patch Confirmed Present

The device has the current monthly update applied. This includes devices that returned a pending reboot flag — because the patch is already installed and simply awaiting a restart to complete.

Signal: patch_status = installed OR pending_reboot = true

2 — Missing

Patch Not Detected

The device is active and reachable, but the patch has not been applied. These devices are the primary remediation targets — they're online, they should have the patch, and they don't.

Signal: active device + no patch record after cross-reference

3 — Offline Since Patch Release

No Contact Since Patch Window

The device's last contact date predates the current patch release. It may or may not have the patch — we simply can't know. It's flagged as offline rather than missing to avoid inflating remediation counts.

Signal: last_contact_date < patch_release_date

Pending Reboot

Patch Installed — Restart Required

Captured from Report B before the merge. These devices already have the patch but haven't restarted to finalize it. They get classified as Installed — but the reboot flag is preserved as a separate, visible signal for the client.

Signal: pending_reboot = true → resolves to status 1

What This Produces

Outcomes & Deliverables

The process ends with per-client reports that are accurate, deduplicated, and ready for distribution — complete with visual summaries.

93%

Patch Compliance
Achieved

Client Reports
Generated Per Cycle

Devices Missed
Due to Agent Gaps

Distinct Status
Classifications

📁

Per-Client Workbooks

The final working report is split by client name into individual workbooks. Each client receives only their own devices — classified, deduplicated, and ready for review.

📊

Pivot Chart Summary

A visual pivot chart is generated per client showing patch status counts by computer — giving a fast, scannable overview without requiring anyone to read through raw rows.

📤

Individual File Export

Each client's workbook is exported as a standalone file, with all worksheets included. Reports are structured consistently across all clients so anyone reviewing them sees the same format every time.

Patch ReportingMethodology.

Why One Report Isn't Enough

How I Build the Report

What Each Status Means

Outcomes & Deliverables

Patch Reporting
Methodology.