Windows Patch Orchestrator

AAP Windows Patch Orchestrator: End-to-End Workflow & Design Specification

Target Audience: Automated coding agents and operations engineers managing enterprise Windows patch automation across 3,000+ virtual machines in Red Hat Ansible Automation Platform (AAP) 2.6.

---

1. Architectural Philosophy & Principles

1. "The JSON Fact Contract is the Product": Every stage emits structured facts via roles/_common/tasks/emit_fact.yml into workflow statistics (set_stats) and host facts (wpo_stage_facts). Human-readable reports (Excel on SMB) and dashboards (Loki/Grafana) materialize this contract.
2. Postcheck is a Health Gate, Not a Scanner Truth Gate: Postcheck runs immediately post-reboot to verify host health (WinRM, reboot execution, pending-reboot flag clearance, service health, OS version, KB list). Vulnerability scanners (Rapid7, Tenable, MDE) take 12–72 hours to re-scan; calling them during postcheck emits stale data. Scanner reconciliation is strictly decoupled into a Deferred Compliance Workflow (Phase 3).
3. Async Multi-Phase Slicing for 3,000+ Scale: Phase 1 triggers reboots asynchronously and finishes; Phase 2 executes 5–15 minutes later as an independent health check gate.
4. Three-Way Data Boundary:
   • PostgreSQL Database: Authoritative for mutable cycle state and historical SLA metrics (e.g., consecutive_missed_cycles).
   • SMB Share (\\smb_share_drive\Collab\InfraAssets\aap-patch-report\): Immutable per-cycle audit copies (*_dev_<cycle_id>.xlsx/json) and overwritten fixed-name files (*_dev.xlsx/json) for BI/Redshift ingestion.
   • Grafana / Loki: Real-time stream observability only (no state store queries against Loki).

---

2. High-Level Multi-Phase Lifecycle

flowchart TD
    subgraph Phase1["Phase 1: Deployment & Fire-and-Forget Reboot (site.yml / 20-45m)"]
        VB[00. Vault Bootstrap] --> PC[01. Precheck & Tag Policy]
        PC -->|Quarantined / Appliance| EM1[Emit Stage Fact & End Host]
        PC -->|Passed| PT[02. Patch Survey / Install WSUS or SCCM]
        PT --> RB[03. Fire-and-Forget Reboot Trigger]
        RB --> R1[05. Phase 1 Report & Loki Stream Push]
    end

    subgraph Delay["Intermission (5 - 15 minutes)"]
        W[Windows OS Reboots & Services Settle]
    end

    subgraph Phase2["Phase 2: Health Verification Gate (postcheck_health_gate.yml / 20-30m)"]
        VB2[Vault Bootstrap] --> PCH[04. Postcheck Health Gate]
        PCH --> R2[Phase 2 Post-Report & Loki Update]
    end

    subgraph Phase3["Phase 3: Deferred Compliance Reconciliation (T+24h to T+72h)"]
        DC[06_deferred_compliance.yml] --> TVM[Reconcile JSON vs MDE/vSphere Attributes]
        TVM --> CD[Emit Compliance Delta xlsx/json to SMB]
    end

    Phase1 --> Delay --> Phase2
    Phase2 -.-> Phase3

Loading

---

3. End-to-End Stage Breakdown & Design Nuances

Stage 0: Vault Bootstrap (roles/vault_bootstrap)

• Authenticates with HashiCorp Vault via AppRole at secret/data/infra/{{ wpo_env }}/{winrm,smb_report,rapid7}.
• Sets in-memory WinRM credentials (ansible_user, ansible_password, ansible_winrm_server_cert_validation: ignore) and SMB credentials on the Ansible Execution Environment without writing secrets to disk.

Stage 1: Precheck & Policy Gate (roles/precheck, playbooks/01_precheck.yml)

• Bounded Probing: Runs up to 3 bounded WinRM probes with short delays to distinguish transient network glitches from fatal authentication/DNS failures (precheck_winrm_attempts recorded).
• Tag Policy Resolution (roles/_common/tasks/tag_policy.yml):
  • Appliance (vm_tag_appliance_present, group appliance, or tag): Emits status skipped, reason appliance_skipped $\rightarrow$ halts workflow for this host (meta: end_host).
  • Hold_Power / Hold_Right_Size (discovered from vSphere Info and VM categories): wpo_patch_allowed: true, wpo_reboot_allowed: false. Patches install, but reboots are suppressed.
  • Standard VMs: Full patch and reboot allowed.
• Pending Reboot Hard-Quarantine: Inspects CBS (RebootPending), WU (RebootRequired), and SCCM pending reboot registry keys.
  • If a pending reboot exists before patching begins, the host is marked status: quarantined with reason: pending_reboot_before_cycle $\rightarrow$ meta: end_host.
  • Remediation: Operator runs playbooks/reboot_hosts.yml (dev-win-reboot-hosts), then re-runs the cycle.
• Disk Space Check: Requires $\ge 10,\text{GB}$ free on system drive C: (min_disk_free_gb).

Stage 2: Patch Execution (roles/patch, playbooks/02_patch.yml)

• Dual Control Model:
  • WSUS Path (~30% of fleet): Uses ansible.windows.win_updates for categories CriticalUpdates, SecurityUpdates, UpdateRollups, Updates. Bounded concurrency via serial/throttle. Catches WUA hard failures (e.g., HRESULT 0x80240438), records failed, and continues the remaining fleet. Zero updates detected is validated against WSUS server sync timestamps and registry config (WUServer, WUStatusServer).
  • SCCM Path (~70% of fleet): Queries WMI CCM_SoftwareUpdate / AdminService. SCCM manages deployment deadlines and maintenance windows.
• Dry Run Nuance (dry_run: true):
  • WSUS executes state: searched (search only; no install).
  • Forces wpo_reboot_required: false.
  • Generates cycle-specific artifacts *_dryrun_<cycle_id>.* only; never overwrites production BI targets.

Stage 3: Async Reboot Trigger (roles/reboot, playbooks/03_reboot.yml)

• Fire-and-Forget: Employs async raw execution shutdown /r /t 10 /c "AAP Windows Patch Orchestrator" or win_reboot with no wait loop. Emits wpo_stage_facts.reboot with status: reboot_requested and ends the Phase 1 run.
• Tag Enforcement: Strictly checks wpo_reboot_allowed. Suppresses reboot for Hold_Power and Hold_Right_Size.

Stage 4: Postcheck Health Gate (roles/postcheck, playbooks/04_postcheck.yml, playbooks/postcheck_health_gate.yml)

• Executed in Phase 2 (5–15 minutes after Phase 1).
• Precheck Quarantine Honor Guard: When wpo_stage_facts.precheck.status == 'quarantined', postcheck emits wpo_status: skipped, reason: skipped_due_to_precheck_quarantine, and immediately calls meta: end_host. This prevents false unreachable_after_reboot errors.
• Verification Criteria:
  1. WinRM connectivity restored.
  2. System uptime / last_boot_at confirms a real reboot occurred.
  3. Pending-reboot flags are cleared.
  4. Required automatic Windows services are running.
  5. Post-patch OS build number and installed KB list captured.

Stage 5: In-Run Compliance Reporting & Loki (roles/report, playbooks/05_report.yml)

• Aggregates all per-host facts into patch_report_document.
• SMB Export: Uses roles/report/files/build_compliance_report.py to create a 5-sheet workbook:
  1. Summary (Cycle stats, success/fail counts, duration)
  2. By Cluster (Breakdown across vCenter compute clusters)
  3. Per-Host (End-to-end status, patch mechanism, reboot outcome)
  4. KBs Installed (Exact KBs and titles applied per host)
  5. Failures (Failure reasons, error codes, quarantined hosts)
• Grafana / Loki Stream: Generates /tmp/patching-aap-artifacts/ local JSON/NDJSON files with _win suffixes (preventing collision with Linux patching) and pushes 1 summary stream + $N$ host streams to Loki (http://loki_ip:3100, job label aap-dev-windows-patch).
• Correlation ID Hierarchy: Resolves wpo_batch_id across sliced inventory jobs while resolving cycle IDs in order: $$\text{explicit } \texttt{wpo_cycle_id} \longrightarrow \texttt{awx_workflow_job_id} \longrightarrow \texttt{awx_job_id} \longrightarrow \texttt{latest persisted SMB report}$$

Stage 6: Deferred TVM Compliance Reconciliation (roles/tvm_reconcile, playbooks/06_deferred_compliance.yml)

---
# Phase 2b - Deferred compliance workflow.
#
# Runs T+24 to T+72h after a patch cycle. Reads the cycle's patch
# compliance JSON from SMB, cross-references with current MDE state
# (already in the shared inventory via vSphere Custom Attrs), and drops
# a compliance-delta xlsx + JSON on the same SMB share for InfoSec / TVM.
#
# AAP template setup:
#   Playbook   : playbooks/06_deferred_compliance.yml
#   Inventory  : shared vSphere dynamic inventory
#   Credential : HashiCorp Vault AppRole (same one)
#   Limit      : whatever host set the patch cycle targeted
#                (e.g. dev-win-patch-test)
#
# Cycle correlation:
#   When this playbook is a node inside the same AAP slice workflow as Phase 1
#   and Phase 2, omit wpo_cycle_id: the parent awx_workflow_job_id is reused.
#   A standalone launch may pass wpo_cycle_id explicitly; otherwise latest is
#   used as the final fallback.
#
# Optional extra vars:
#   wpo_env       dev (default) or prod. Drives Vault path + filenames.
#
# Output on the SMB share (\\<server>\<share>\<dir>\):
#   win_compliance_delta_<env>.xlsx           (fixed, latest)
#   win_compliance_delta_<env>.json
#   win_compliance_delta_<env>_<cycle_id>.xlsx  (audit copy per cycle)
#   win_compliance_delta_<env>_<cycle_id>.json
#
# Scheduling: attach an AAP schedule to this template. T+24h is a safe
# default; adjust based on how often your MDE feed updates the vSphere
# Custom Attrs.

- name: Deferred compliance reconciliation
  hosts: "{{ target_hosts | default(target_clusters | default(target_group | default('all'))) }}"
  gather_facts: false
  pre_tasks:
    - name: Enforce Windows-only targeting
      ansible.builtin.import_tasks: vars/windows_only_guard.yml
    - name: Bootstrap SMB report-share credentials from Vault
      ansible.builtin.include_role:
        name: vault_bootstrap
        tasks_from: fetch_smb.yml
  roles:
    - tvm_reconcile

• Runs $T+24\text{h}$ to $T+72\text{h}$ post-cycle (Phase 3).
• Ingests the cycle's persisted JSON fact contract from SMB and cross-references against live Microsoft Defender for Endpoint (MDE) posture via vSphere Custom Attributes (vm_attr_defender_*).
• Emits win_compliance_delta_<env>_<cycle_id>.xlsx (3 sheets: Summary, Per-Host, Coverage Gaps).

---

4. Key Artifact & Path Reference Table

Purpose	Location / Value
SMB Base Directory	\\smb_share_drive\Collab\InfraAssets\aap-patch-report\
Fixed Report (BI/Redshift)	win_patch_compliance_report_dev.{xlsx,json}
Cycle Audit Report	win_patch_compliance_report_dev_<cycle_id>.{xlsx,json}
TVM Delta Report	win_compliance_delta_dev_<cycle_id>.{xlsx,json}
EE Staging Directory	/tmp/patching-aap-artifacts/ (patch_report_latest_win.json)
Loki Endpoint & Job	http://loki_ip:3100 (job="aap-dev-windows-patch")
Vault AppRole Path	secret/data/infra/{{ wpo_env }}/{winrm,smb_report,rapid7}
Precheck Quarantine Reason	pending_reboot_before_cycle
Appliance Skip Reason	appliance_skipped
Policy Tags Resolved	Appliance, Hold_Power, Hold_Right_Size (from Info & VM categories)

---

5. Summary of AAP Workflow Templates

AAP Template	Playbook	Execution Phase & Role
dev-win-site-full-cycle	playbooks/site.yml	Phase 1: Full cycle (Precheck $\rightarrow$ Patch $\rightarrow$ Fire-and-forget Reboot $\rightarrow$ Report)
dev-win-postcheck-gate	playbooks/postcheck_health_gate.yml	Phase 2: Health verification gate (runs 5–15m after Phase 1)
dev-win-compliance-delta	playbooks/06_deferred_compliance.yml	Phase 3: Deferred scanner reconciliation ($T+24\text{h}$ to $T+72\text{h}$)
dev-win-reboot-hosts	playbooks/reboot_hosts.yml	Maintenance: Standalone reboot to clear precheck-quarantined hosts
win-test-connectivity	playbooks/test_winrm.yml	Preflight: Probes WinRM and outputs winrm_status_<ts>.xlsx

6. Project Scaffold

Here is the complete project scaffold for aap-windows-patch-orchestrator detailing the role and responsibility of each directory and file.

---

Complete Project Scaffold

aap-windows-patch-orchestrator/
├── .gitignore                                # Git ignore rules (DS_Store, xlsx, .venv, collections)
├── AGENTS.md                                 # Primary agent briefing doc (workflows, conventions, design rules)
├── PROJECT-STATUS.md                         # Current branch/merge status, pilot test notes, design tracker
├── README.md                                 # Project overview & operator entry point
├── ansible.cfg                               # Local Ansible configuration (collections_paths, roles_path)
│
├── collections/
│   └── requirements.yml                      # Galaxy collections: ansible.windows, ansible.utils, community.vmware, community.postgresql
│
├── docs/                                     # Architecture, operational, and design documentation
│   ├── ARCHITECTURE.md                       # Core system architecture, async multi-phase flow, data spine
│   ├── HUB.md                                # AAP Automation Hub & execution environment reference
│   ├── PATCHING-WORKFLOW-PRESENTATION.md     # Stakeholder workflow & design presentation
│   ├── PLAN.md                               # Historical implementation plan & phase breakdown
│   ├── REPORT_COLUMNS.md                     # Schema and column definitions for SMB Excel workbooks
│   ├── RUNBOOK.md                            # Operational runbook & troubleshooting guide for operators
│   ├── UNDER-DISCUSSION.md                   # Locked vs open architectural decisions (scale, tags, reboot ownership)
│   └── Windows Patch Orchestrator - Design Document.pdf
│
├── inventories/                              # Dynamic & static inventory configurations
│   ├── dev/
│   │   ├── group_vars/
│   │   │   ├── all.yml                       # Dev environment defaults, Vault paths, SMB paths
│   │   │   ├── dev_windows.yml               # Dev Windows fleet base vars
│   │   │   ├── dev-win-sccm.yml              # SCCM patch mechanism overrides for dev
│   │   │   └── dev-win-wsus.yml              # WSUS patch mechanism overrides for dev
│   │   ├── test_hosts.yml                    # Static pilot host inventory for smoke testing
│   │   └── vsphere.vmware.yml                # VMware vSphere dynamic inventory plugin config (dev)
│   └── prod/
│       ├── group_vars/
│       │   ├── all.yml                       # Prod environment defaults, Vault paths, SMB paths
│       │   └── prod_windows.yml              # Prod Windows fleet base vars
│       └── vsphere.vmware.yml                # VMware vSphere dynamic inventory plugin config (prod)
│
├── output/
│   └── reports/                              # Output directory for locally staged report workbooks
│
├── playbooks/                                # Entry point and stage playbooks for AAP Job Templates
│   ├── 01_precheck.yml                       # Stage 1: Precheck & tag policy validation
│   ├── 02_patch.yml                          # Stage 2: WSUS / SCCM patch detection & installation
│   ├── 03_reboot.yml                         # Stage 3: Fire-and-forget reboot trigger
│   ├── 04_postcheck.yml                      # Stage 4: Post-reboot system health verification
│   ├── 05_report.yml                         # Stage 5: Excel generation to SMB & Loki metric push
│   ├── 06_deferred_compliance.yml            # Phase 3: TVM / MDE posture reconciliation (T+24h to T+72h)
│   ├── inventory_scope_stats.yml             # Scopes fleet counts, OS breakdown & cluster metrics
│   ├── phase1.yml                            # Standalone Phase 1 execution (includes pre-report)
│   ├── postcheck_health_gate.yml             # Phase 2 standalone health gate (postcheck + report)
│   ├── pre_filter_windows.yml                # Filters active Windows VMs from VMware inventory
│   ├── preflight_ee.yml                      # Verifies Execution Environment dependencies & connectivity
│   ├── rapid7_onboard.yml                    # Rapid7 Scan Assistant deployment/onboarding playbook
│   ├── reboot_hosts.yml                      # Maintenance reboot playbook (remediates quarantined VMs)
│   ├── run_win_command.yml                   # Ad-hoc PowerShell command execution runner
│   ├── site.yml                              # Master Phase 1 full cycle (Precheck -> Patch -> Reboot -> Report)
│   ├── test_smb.yml                          # SMB share write/read verification smoke test
│   ├── test_winrm.yml                        # WinRM probe test & generates winrm_status_<ts>.xlsx
│   └── vars/                                 # Playbook-level safety guards
│       ├── powered_on_guard.yml              # Guard: Skips powered-off / suspended VMs
│       ├── require_scoped_target.yml         # Guard: Prevents full-inventory runs without --limit
│       └── windows_only_guard.yml            # Guard: Prevents targeting non-Windows hosts
│
├── reporting/                                # Database schemas and Grafana assets
│   ├── agent/
│   │   ├── mcp_notes.md                      # Notes for read-only database query agent
│   │   └── readonly_view.sql                 # Read-only SQL view definition for agent consumption
│   ├── ddl/
│   │   ├── reporting_schema.sql              # Core PostgreSQL database reporting schema (historical runs)
│   │   └── report_views.sql                  # Views for fleet SLA, compliance rates & aging distributions
│   └── grafana/
│       ├── README.md                         # Grafana setup and Loki integration docs
│       ├── dashboard.json                    # Historical PostgreSQL dashboard
│       └── dashboard_loki.json               # Real-time Loki log-stream dashboard
│
├── roles/                                    # Ansible roles structured by stage
│   ├── _common/                              # Shared cross-stage logic
│   │   └── tasks/
│   │       ├── bootstrap_winrm_vault.yml     # In-memory WinRM credential assignment from Vault
│   │       ├── emit_fact.yml                 # Core JSON fact contract emitter (set_stats + wpo_stage_facts)
│   │       ├── reachability_guard.yml        # WinRM reachability validation guard
│   │       └── tag_policy.yml                # Resolves Appliance / Hold_Power / Hold_Right_Size policies
│   ├── patch/                                # Patching logic (WSUS & SCCM)
│   │   ├── defaults/main.yml
│   │   └── tasks/
│   │       ├── main.yml                      # Dispatches to wsus.yml or sccm.yml based on patch_path
│   │       ├── sccm.yml                      # SCCM WMI/AdminService trigger & detection
│   │       └── wsus.yml                      # win_updates search/install, WUA error handling
│   ├── postcheck/                            # Health gate verification
│   │   ├── defaults/main.yml
│   │   └── tasks/
│   │       └── main.yml                      # Uptime check, pending-reboot verify, services, quarantine skip
│   ├── pre_report/                           # Pre-patching inventory state capture
│   │   ├── defaults/main.yml
│   │   ├── files/build_pre_report.py         # Python builder for pre-patch summary workbook
│   │   └── tasks/main.yml
│   ├── precheck/                             # Initial host validation
│   │   ├── defaults/main.yml
│   │   └── tasks/
│   │       └── main.yml                      # Bounded WinRM probe, disk check, pending-reboot quarantine
│   ├── rapid7_onboard/                       # Rapid7 Scan Assistant installer
│   │   ├── defaults/main.yml
│   │   └── tasks/main.yml                    # Fetches cert/installer from Vault/SMB & registers service
│   ├── reboot/                               # Asynchronous reboot dispatcher
│   │   ├── defaults/main.yml
│   │   └── tasks/main.yml                    # Fire-and-forget shutdown /r, respects tag policies
│   ├── report/                               # Report aggregation & export
│   │   ├── defaults/main.yml
│   │   ├── files/
│   │   │   ├── build_report.py               # 5-sheet compliance workbook builder (xlsx + json)
│   │   │   └── build_winrm_report.py         # Connectivity workbook builder
│   │   ├── tasks/
│   │   │   ├── main.yml                      # Builds artifacts, delivers to SMB share
│   │   │   └── push_loki.yml                 # Streams patch_report_document to Loki endpoint
│   │   └── templates/
│   │       ├── loki.ndjson.j2                # NDJSON template for Loki log stream
│   │       └── prometheus_metrics.j2         # Prometheus metrics scrape format
│   ├── tvm_reconcile/                        # Vulnerability scanner reconciliation (Phase 3)
│   │   ├── defaults/main.yml
│   │   ├── files/build_compliance_delta.py   # Compares cycle JSON vs vSphere MDE custom attributes
│   │   └── tasks/main.yml                    # Produces 3-sheet compliance delta workbook on SMB
│   └── vault_bootstrap/                      # Vault AppRole secret management
│       ├── defaults/main.yml
│       ├── meta/main.yml
│       └── tasks/
│           ├── _auth.yml                     # AppRole login & token acquisition
│           ├── fetch_rapid7.yml              # Retrieves Rapid7 pairing keys
│           ├── fetch_smb.yml                 # Retrieves SMB share credentials
│           └── main.yml                      # Orchestrates credential fetch for WinRM, SMB, Rapid7
│
├── schemas/
│   └── fact_contract.schema.json             # JSON Schema validating the stage fact contract
│
├── scripts/                                  # Helper & verification CLI scripts
│   ├── test_phase1_pre_report.py             # Local mock runner for pre-report generation
│   └── test_smb_push.py                      # Local script to test SMB file uploads
│
├── surveys/
│   └── workflow_survey.json                  # AAP Workflow Launch Survey definition (inputs & toggles)
│
└── tests/                                    # Unit tests & test runners
    ├── README.md
    └── unit/
        ├── test_pre_report_builder.py        # Tests for pre-report Python script
        ├── test_workflow_decisions.py        # Tag policy, batch_id & cycle decision tree tests
        └── workflow_decisions.py             # Python model simulating workflow decision tree

Claude analysis

Clear on both. Let me take the reboot ownership one first because it has a subtlety that will bite if we flip it naively.

we want Ansible to own reboot so every reboot flows through your fact contract into Loki and Redshift. Right call for the observability goal. SCCM reboots are invisible to your pipeline, they happen off in a maintenance window and your dashboard never sees them. Moving reboot to Ansible closes that gap. Agreed on direction.

But we cannot just start rebooting SCCM hosts while SCCM still thinks it owns reboot. If both reboot, we get double reboots, users hit with restarts twice, and reboots landing outside the window we meant to control. So this is not add Ansible reboot, it is transfer ownership. One side suppresses, the other takes over.

The transfer is a change on the SCCM side, in the deployment, not on the hosts. On the software update deployment for your dev and prod collections, set the deadline behavior to suppress the reboot, the setting that stops SCCM forcing a restart outside the window for servers and workstations. Then SCCM installs and reports, but never restarts. Ansible then owns reboot inside your window.

That hands us exactly the sequence we want, and every step emits a fact.

Ansible triggers the SCCM deployment or install, install only, no reboot. Ansible confirms install completed by reading client state. Ansible reboots inside the window, fire and forget per your Phase 1. Phase 2 health gate confirms the host came back and pending reboot cleared. Ansible triggers scan 113 and deployment eval 108 so SCCM reconciles and reports compliant. Every one of those is a stage fact, so Loki and Redshift see the whole arc, which SCCM owned reboot never gave we.

Timing wrinkle worth flagging. Today SCCM reboots on those subsequent Sundays automatically. When Ansible takes over, your workflow must run on that same Sunday cadence in the same window, because SCCM will no longer do it. So the schedule that currently triggers is a maintenance window on the SCCM side becomes an AAP schedule firing your Phase 1 then Phase 2. Do not suppress SCCM reboot until the AAP schedule is live, or we get a Sunday where nobody reboots and patches sit pending.

One more, keep it per patch_path and even per tier. Your Hold_Power and Hold_Right_Size tags already say some hosts must not reboot. Those still must not, regardless of who owns reboot. So reboot ownership is Ansible for standard SCCM hosts, suppressed entirely for hold tagged hosts, and SCCM installs for all of them. The tag policy we already built handles this, it just now also governs the Ansible reboot decision.

Net, three coordinated changes. SCCM deployment set to suppress reboot. AAP schedule takes over the Sunday window. Ansible reboot respects tag policy. Do them in that order with the schedule live before the suppress, and we get comprehensive post patch telemetry with no double reboot gap.

Lab dropped, out of scope from here.

One thing to confirm so I frame the next piece right. When we say SCCM owns reboot today, is the install itself also fully SCCM automated on those Sundays, or does SCCM install and we want Ansible to trigger the install too. That decides whether Ansible is trigger plus reboot, or reboot only with SCCM still auto installing on schedule.