
Applies to Windows Server 2016 to 2025, with PowerShell remoting (WinRM) enabled. The script runs in Windows PowerShell 5.1 or PowerShell 7.
Your patching tool (WSUS, Configuration Manager, Intune or Azure Update Manager) tells you what it tried to install. This report asks each server what it actually has: the last update, the monthly build number, whether it’s waiting for a reboot, and whether that version of Windows still gets updates at all. The SQL Server patch report does the same for SQL Server, which Windows Update usually leaves alone.
What the report shows
- Each server with its patch ring and owner, from a small table you keep: test, pre-production, production wave 1 and so on.
- OS build and monthly update revision, for example 20348.2849. Servers on the same version of Windows should all show the same number after patching; an odd one out is the one that missed.
- The last update installed, when, and how many in the last 35 days.
- Pending reboot and last boot. An update that’s installed but waiting for a reboot isn’t protecting anything.
- Support status, from the extended support date for each Windows version.
- Exceptions: servers deliberately left behind, with the reason, the risk owner and the expiry date. They show as EXCEPTION instead of failing, until the date passes.
- The compliance figure: servers patched within 35 days with no reboot pending, by ring and in total.
Why it matters
Patching is where auditors and cyber insurance questionnaires look first, and “the tool says it’s fine” isn’t evidence. A server that dropped out of WSUS, or that’s been pending a reboot since March, looks fine in the tool and fails here.
The thresholds are the ones from the Audit page: OVERDUE after 35 days without an update (one monthly cycle plus a week), REBOOT NEEDED when a reboot has been pending for more than 7 days, and OUT OF SUPPORT always red.
Collect daily. Report weekly during the patch cycle so stragglers get chased, and monthly as the compliance figure for the IT manager and risk.
The script
Servers in the list are reduced to the computer name (a named instance and the default instance on the same server are checked once). Everything is read on the server in a single Invoke-Command call: the OS from CIM, the build and revision from the registry, the updates from Get-HotFix, and the two registry keys Windows sets when a reboot is pending.
# Invoke-WindowsPatchAudit.ps1
# Windows patch level for every server in the list: OS build and monthly update
# revision (UBR), last update installed, updates in the last 35 days, pending
# reboot and last boot. It is the independent check that patching landed. It
# doesn't replace WSUS, Configuration Manager, Intune or Azure Update Manager.
# Schedule it daily.
#
# Needs PowerShell remoting (WinRM) to each server: everything is read in one
# Invoke-Command call. Named instances in the list are reduced to the computer
# name, and each computer is checked once.
#
# Tables: run sql\01_Create_Tables.sql first.
# Report: sql\WindowsPatchReport.sql
# ---- Settings ---------------------------------------------------------------
$ServerListPath = 'C:\DBA\Servers.txt'
$InventoryServer = 'SQLMON01'
$InventoryDatabase = 'DBA'
# -----------------------------------------------------------------------------
. "$PSScriptRoot\DbaReportHelpers.ps1"
# Runs on each server
$RemoteCheck = {
$OS = Get-CimInstance -ClassName Win32_OperatingSystem
$CV = Get-ItemProperty -Path 'HKLM:\SOFTWARE\Microsoft\Windows NT\CurrentVersion'
$Fixes = @(Get-HotFix | Where-Object { $_.InstalledOn })
$Last = $Fixes | Sort-Object InstalledOn -Descending | Select-Object -First 1
$Since = (Get-Date).AddDays(-35)
$Reasons = @()
if (Test-Path 'HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Component Based Servicing\RebootPending') {
$Reasons += 'Component Based Servicing'
}
if (Test-Path 'HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\RebootRequired') {
$Reasons += 'Windows Update'
}
[pscustomobject]@{
OSName = $OS.Caption
OSVersion = $OS.Version
OSBuildUBR = "$($CV.CurrentBuild).$($CV.UBR)"
LastBootTime = $OS.LastBootUpTime
LastUpdateKB = $Last.HotFixID
LastUpdateInstalled = $Last.InstalledOn
UpdatesLast35Days = @($Fixes | Where-Object { $_.InstalledOn -ge $Since }).Count
RebootPending = [bool]$Reasons
RebootPendingReason = $Reasons -join ', '
}
}
$Computers = Get-ServerList -Path $ServerListPath |
ForEach-Object { Get-ComputerName $_ } |
Sort-Object -Unique
$CaptureDate = Get-Date
$Repo = Open-SqlConnection -Server $InventoryServer -Database $InventoryDatabase
try {
Assert-InventoryTable $Repo 'dbo.CollectionLog', 'dbo.WindowsPatchAudit'
foreach ($Computer in $Computers) {
try {
$Result = Invoke-Command -ComputerName $Computer -ScriptBlock $RemoteCheck -ErrorAction Stop
$Row = [ordered]@{ CaptureDate = $CaptureDate; ServerName = $Computer }
foreach ($Name in 'OSName', 'OSVersion', 'OSBuildUBR', 'LastBootTime', 'LastUpdateKB',
'LastUpdateInstalled', 'UpdatesLast35Days', 'RebootPending', 'RebootPendingReason') {
$Row[$Name] = $Result.$Name
}
Add-InventoryRow -Connection $Repo -Table 'dbo.WindowsPatchAudit' -Row $Row
Write-CollectionLog $Repo -Collection 'WindowsPatch' -ServerName $Computer -CaptureDate $CaptureDate `
-Status 'SUCCESS' -Rows 1
Write-Host ("{0} : build {1}, last update {2} on {3:yyyy-MM-dd}, reboot pending {4}" -f
$Computer, $Row.OSBuildUBR, $Row.LastUpdateKB, $Row.LastUpdateInstalled, $Row.RebootPending)
}
catch {
Write-CollectionLog $Repo -Collection 'WindowsPatch' -ServerName $Computer -CaptureDate $CaptureDate `
-Status 'FAILED' -ErrorMessage $_.Exception.Message
Write-Warning "$Computer : $($_.Exception.Message)"
}
}
}
finally {
$Repo.Close()
}
DbaReportHelpers.ps1: the shared helper file (save it in the same folder as the script). This version adds the functions the newer collectors use; it replaces the older copy and the earlier scripts still work with it.
# DbaReportHelpers.ps1
# Shared helpers for the auditing scripts. Save it next to them and dot-source it:
# . "$PSScriptRoot\DbaReportHelpers.ps1"
# Windows Authentication throughout: run the scripts as a service account that
# can reach every server. No passwords live in any of these files.
function Get-ServerList {
# One server per line. Blank lines and anything after # are ignored,
# and duplicates (in any case) are removed.
param([Parameter(Mandatory)][string]$Path)
Get-Content -Path $Path |
ForEach-Object { ($_ -split '#')[0].Trim() } |
Where-Object { $_ } |
Sort-Object -Unique
}
function Get-ComputerName {
# SERVER\INSTANCE -> SERVER, for the Windows calls (CIM, ping, remoting)
param([Parameter(Mandatory)][string]$Server)
($Server -split '\\')[0]
}
function Open-SqlConnection {
param(
[Parameter(Mandatory)][string]$Server,
[string]$Database = 'master'
)
$Conn = New-Object System.Data.SqlClient.SqlConnection
$Conn.ConnectionString = "Server=$Server;Database=$Database;Integrated Security=True;" +
"TrustServerCertificate=True;Connect Timeout=15;Application Name=DBA reports"
$Conn.Open()
$Conn
}
function Invoke-SqlQuery {
# Runs a query and returns a DataTable (the leading comma stops PowerShell unrolling it).
param($Connection, [string]$Sql, [hashtable]$Parameters = @{})
$Cmd = $Connection.CreateCommand()
$Cmd.CommandText = $Sql
$Cmd.CommandTimeout = 300
foreach ($Name in $Parameters.Keys) {
$null = $Cmd.Parameters.AddWithValue("@$Name", $Parameters[$Name])
}
$Table = New-Object System.Data.DataTable
$null = (New-Object System.Data.SqlClient.SqlDataAdapter $Cmd).Fill($Table)
, $Table
}
function Invoke-SqlNonQuery {
param($Connection, [string]$Sql)
$Cmd = $Connection.CreateCommand()
$Cmd.CommandText = $Sql
$Cmd.CommandTimeout = 300
$null = $Cmd.ExecuteNonQuery()
}
function Add-InventoryRow {
# Inserts one row from an ordered hashtable of column = value.
# Every value goes in as a parameter, never pasted into the SQL text.
param(
$Connection,
[Parameter(Mandatory)][string]$Table,
[Parameter(Mandatory)][System.Collections.IDictionary]$Row
)
$Columns = @($Row.Keys)
$Cmd = $Connection.CreateCommand()
$Cmd.CommandText = "INSERT INTO $Table (" + ($Columns -join ', ') + ') VALUES (' +
(($Columns | ForEach-Object { "@$_" }) -join ', ') + ');'
foreach ($Column in $Columns) {
$Value = $Row[$Column]
if ($null -eq $Value -or $Value -is [System.DBNull] -or
($Value -is [string] -and $Value.Trim() -eq '')) {
$Value = [System.DBNull]::Value
}
$null = $Cmd.Parameters.AddWithValue("@$Column", $Value)
}
$null = $Cmd.ExecuteNonQuery()
}
function Invoke-SqlQuerySet {
# Like Invoke-SqlQuery, but returns a DataSet: one DataTable per result set.
param($Connection, [string]$Sql, [hashtable]$Parameters = @{})
$Cmd = $Connection.CreateCommand()
$Cmd.CommandText = $Sql
$Cmd.CommandTimeout = 600
foreach ($Name in $Parameters.Keys) {
$null = $Cmd.Parameters.AddWithValue("@$Name", $Parameters[$Name])
}
$Set = New-Object System.Data.DataSet
$null = (New-Object System.Data.SqlClient.SqlDataAdapter $Cmd).Fill($Set)
$Set
}
function Assert-InventoryTable {
# Stops the run early with a clear message if 01_Create_Tables.sql hasn't been run.
param($Connection, [Parameter(Mandatory)][string[]]$Table)
foreach ($Name in $Table) {
$Id = (Invoke-SqlQuery $Connection "SELECT OBJECT_ID(@Name, 'U') AS Id" @{ Name = $Name }).Rows[0].Id
if ($Id -is [System.DBNull]) {
throw "Table $Name is missing. Run sql\01_Create_Tables.sql in the DBA database first."
}
}
}
function Add-DataTableRows {
# Inserts every row of a DataTable, adding the run's CaptureDate and ServerName. Returns the row count.
param(
$Connection,
[Parameter(Mandatory)][string]$Table,
[Parameter(Mandatory)]$Data,
[Parameter(Mandatory)][string]$ServerName,
[Parameter(Mandatory)][datetime]$CaptureDate
)
foreach ($Source in $Data.Rows) {
$Row = [ordered]@{ CaptureDate = $CaptureDate; ServerName = $ServerName }
foreach ($Column in $Data.Columns.ColumnName) {
$Row[$Column] = $Source[$Column]
}
Add-InventoryRow -Connection $Connection -Table $Table -Row $Row
}
$Data.Rows.Count
}
function Write-CollectionLog {
# One row per server per collection per run, so a server that failed is never just missing.
param(
$Connection,
[Parameter(Mandatory)][string]$Collection,
[Parameter(Mandatory)][string]$ServerName,
[Parameter(Mandatory)][datetime]$CaptureDate,
[Parameter(Mandatory)][ValidateSet('SUCCESS', 'PARTIAL', 'FAILED')][string]$Status,
[int]$Rows = 0,
[string]$ErrorMessage
)
Add-InventoryRow -Connection $Connection -Table 'dbo.CollectionLog' -Row ([ordered]@{
CaptureDate = $CaptureDate
Collection = $Collection
ServerName = $ServerName
CollectionStatus = $Status
RowsCollected = $Rows
ErrorMessage = $ErrorMessage
})
}
The tables
The script stops with a clear message if these are missing. Run this once before the first collection.
-- Run once in the DBA database on the inventory server, before the first run of
-- the script. Safe to run again: each table is only created if it's missing.
IF DB_ID(N'DBA') IS NULL
CREATE DATABASE DBA;
GO
USE DBA;
GO
-- Shared by the collectors: one row per server per run, so a server that
-- failed is never just missing from the report
IF OBJECT_ID(N'dbo.CollectionLog', N'U') IS NULL
BEGIN
CREATE TABLE dbo.CollectionLog
(
CollectionLogID BIGINT IDENTITY(1,1) NOT NULL
CONSTRAINT PK_CollectionLog PRIMARY KEY NONCLUSTERED,
CaptureDate DATETIME2(0) NOT NULL,
Collection VARCHAR(30) NOT NULL, -- Integrity, WindowsPatch, SqlPatch, Security, Capacity
ServerName NVARCHAR(128) NOT NULL,
CollectionStatus VARCHAR(20) NOT NULL, -- SUCCESS, PARTIAL, FAILED
RowsCollected INT NULL,
ErrorMessage NVARCHAR(MAX) NULL
);
CREATE CLUSTERED INDEX CX_CollectionLog
ON dbo.CollectionLog (Collection, ServerName, CaptureDate);
END;
GO
IF OBJECT_ID(N'dbo.WindowsPatchAudit', N'U') IS NULL
BEGIN
CREATE TABLE dbo.WindowsPatchAudit
(
CaptureDate DATETIME2(0) NOT NULL,
ServerName NVARCHAR(128) NOT NULL, -- the Windows computer name
OSName NVARCHAR(256) NULL,
OSVersion NVARCHAR(64) NULL,
OSBuildUBR NVARCHAR(32) NULL, -- e.g. 20348.2849: the build plus monthly update revision
LastBootTime DATETIME2(0) NULL,
LastUpdateKB NVARCHAR(32) NULL,
LastUpdateInstalled DATETIME2(0) NULL,
UpdatesLast35Days INT NULL,
RebootPending BIT NULL,
RebootPendingReason NVARCHAR(200) NULL
);
CREATE CLUSTERED INDEX CX_WindowsPatchAudit
ON dbo.WindowsPatchAudit (ServerName, CaptureDate);
END;
GO
-- Maintained by hand: which patch ring each server is in, and who owns it
IF OBJECT_ID(N'dbo.ServerPatchGroup', N'U') IS NULL
CREATE TABLE dbo.ServerPatchGroup
(
ServerName NVARCHAR(128) NOT NULL CONSTRAINT PK_ServerPatchGroup PRIMARY KEY,
PatchGroup NVARCHAR(50) NOT NULL, -- e.g. Test, Pre-production, Production wave 1
Owner NVARCHAR(128) NULL,
MaintenanceWindow NVARCHAR(100) NULL -- e.g. 'Sunday 02:00-06:00'
);
GO
-- Maintained by hand: servers or instances deliberately left behind, and until when
IF OBJECT_ID(N'dbo.PatchException', N'U') IS NULL
CREATE TABLE dbo.PatchException
(
PatchExceptionID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_PatchException PRIMARY KEY,
ServerName NVARCHAR(128) NOT NULL, -- computer name for Windows, instance name for SQL
Scope VARCHAR(10) NOT NULL
CONSTRAINT CK_PatchException_Scope CHECK (Scope IN ('Windows', 'SQL')),
Reason NVARCHAR(400) NOT NULL,
RiskOwner NVARCHAR(128) NOT NULL,
ExpiresOn DATE NOT NULL,
CreatedOn DATE NOT NULL CONSTRAINT DF_PatchException_CreatedOn DEFAULT (CAST(SYSDATETIME() AS DATE))
);
GO
IF OBJECT_ID(N'dbo.WindowsSupportLifecycle', N'U') IS NULL
CREATE TABLE dbo.WindowsSupportLifecycle
(
OSNamePattern NVARCHAR(100) NOT NULL CONSTRAINT PK_WindowsSupportLifecycle PRIMARY KEY, -- LIKE pattern
ExtendedSupportEnds DATE NULL
);
GO
IF NOT EXISTS (SELECT 1 FROM dbo.WindowsSupportLifecycle)
INSERT INTO dbo.WindowsSupportLifecycle (OSNamePattern, ExtendedSupportEnds)
VALUES (N'%Server 2012 R2%', '2023-10-10'),
(N'%Server 2012%', '2023-10-10'),
(N'%Server 2016%', '2027-01-12'),
(N'%Server 2019%', '2029-01-09'),
(N'%Server 2022%', '2031-10-14'),
(N'%Server 2025%', NULL); -- add the date from Microsoft's lifecycle page
GO
The report queries
Run these in the DBA database. Fill in dbo.ServerPatchGroup and dbo.PatchException first; the report works without them, but every server shows as “(no group)” and nothing is excepted.
/*
WindowsPatchReport.sql - run in the DBA database.
Weekly during the patch cycle, monthly as the compliance figure.
Fill in dbo.ServerPatchGroup (rings and owners) and dbo.PatchException
(agreed exceptions with an expiry date) so the report can use them.
*/
USE DBA;
DECLARE @PatchDays INT = 35, -- monthly cycle plus a week's grace
@RebootDays INT = 7, -- pending reboot older than this = not patched
@Today DATE = CAST(SYSDATETIME() AS DATE);
/* 1. Every server, worst first ------------------------------------------- */
WITH Latest AS
(
SELECT W.*,
ROW_NUMBER() OVER (PARTITION BY W.ServerName ORDER BY W.CaptureDate DESC) AS RN
FROM dbo.WindowsPatchAudit AS W
),
Lifecycle AS
(
SELECT L.ServerName, S.ExtendedSupportEnds,
ROW_NUMBER() OVER (PARTITION BY L.ServerName ORDER BY LEN(S.OSNamePattern) DESC) AS RN
FROM Latest AS L
JOIN dbo.WindowsSupportLifecycle AS S ON L.OSName LIKE S.OSNamePattern
WHERE L.RN = 1
),
Report AS
(
SELECT
L.ServerName,
G.PatchGroup,
G.Owner,
L.OSName,
L.OSBuildUBR,
LC.ExtendedSupportEnds,
L.LastUpdateKB,
L.LastUpdateInstalled,
DATEDIFF(DAY, L.LastUpdateInstalled, L.CaptureDate) AS DaysSinceUpdate,
L.UpdatesLast35Days,
L.RebootPending,
L.RebootPendingReason,
L.LastBootTime,
DATEDIFF(DAY, L.LastBootTime, L.CaptureDate) AS DaysSinceBoot,
E.Reason AS ExceptionReason,
E.RiskOwner AS ExceptionOwner,
E.ExpiresOn AS ExceptionExpires,
L.CaptureDate,
CASE
WHEN L.CaptureDate < DATEADD(DAY, -2, SYSDATETIME()) THEN 'NO RECENT DATA'
WHEN LC.ExtendedSupportEnds < @Today THEN 'OUT OF SUPPORT'
WHEN L.LastUpdateInstalled IS NULL
OR DATEDIFF(DAY, L.LastUpdateInstalled, L.CaptureDate) > @PatchDays THEN 'OVERDUE'
WHEN L.RebootPending = 1
AND DATEDIFF(DAY, L.LastUpdateInstalled, L.CaptureDate) > @RebootDays THEN 'REBOOT NEEDED'
WHEN L.LastBootTime < L.LastUpdateInstalled THEN 'REBOOT NEEDED'
ELSE 'OK'
END AS RawStatus
FROM Latest AS L
LEFT JOIN Lifecycle AS LC
ON LC.ServerName = L.ServerName AND LC.RN = 1
LEFT JOIN dbo.ServerPatchGroup AS G
ON G.ServerName = L.ServerName
OUTER APPLY
(
SELECT TOP (1) X.Reason, X.RiskOwner, X.ExpiresOn
FROM dbo.PatchException AS X
WHERE X.ServerName = L.ServerName AND X.Scope = 'Windows' AND X.ExpiresOn >= @Today
ORDER BY X.ExpiresOn DESC
) AS E
WHERE L.RN = 1
)
SELECT
ServerName, PatchGroup, Owner, OSName, OSBuildUBR,
LastUpdateKB, LastUpdateInstalled, DaysSinceUpdate, UpdatesLast35Days,
RebootPending, DaysSinceBoot, ExtendedSupportEnds,
CASE WHEN RawStatus <> 'OK' AND ExceptionReason IS NOT NULL
THEN 'EXCEPTION until ' + CONVERT(CHAR(10), ExceptionExpires, 23)
ELSE RawStatus END AS PatchStatus,
ExceptionReason, ExceptionOwner
FROM Report
ORDER BY CASE RawStatus WHEN 'OUT OF SUPPORT' THEN 0 WHEN 'OVERDUE' THEN 1 WHEN 'REBOOT NEEDED' THEN 2
WHEN 'NO RECENT DATA' THEN 3 ELSE 4 END,
DaysSinceUpdate DESC;
/* 2. The monthly compliance figure ---------------------------------------- */
WITH Latest AS
(
SELECT W.*,
ROW_NUMBER() OVER (PARTITION BY W.ServerName ORDER BY W.CaptureDate DESC) AS RN
FROM dbo.WindowsPatchAudit AS W
)
SELECT
COALESCE(G.PatchGroup, '(no group)') AS PatchGroup,
COUNT(*) AS Servers,
SUM(CASE WHEN DATEDIFF(DAY, L.LastUpdateInstalled, L.CaptureDate) <= @PatchDays
AND ISNULL(L.RebootPending, 0) = 0 THEN 1 ELSE 0 END) AS Compliant,
CAST(100.0 * SUM(CASE WHEN DATEDIFF(DAY, L.LastUpdateInstalled, L.CaptureDate) <= @PatchDays
AND ISNULL(L.RebootPending, 0) = 0 THEN 1 ELSE 0 END)
/ COUNT(*) AS DECIMAL(5,1)) AS CompliantPct
FROM Latest AS L
LEFT JOIN dbo.ServerPatchGroup AS G ON G.ServerName = L.ServerName
WHERE L.RN = 1
GROUP BY ROLLUP (COALESCE(G.PatchGroup, '(no group)'))
ORDER BY GROUPING(COALESCE(G.PatchGroup, '(no group)')), PatchGroup;
/* 3. Servers that couldn't be checked in the latest run ------------------- */
SELECT L.ServerName, L.CaptureDate, L.ErrorMessage
FROM dbo.CollectionLog AS L
WHERE L.Collection = 'WindowsPatch'
AND L.CaptureDate = (SELECT MAX(CaptureDate) FROM dbo.CollectionLog WHERE Collection = 'WindowsPatch')
AND L.CollectionStatus <> 'SUCCESS';
/* 4. Exceptions that have expired, or expire within 14 days --------------- */
SELECT ServerName, Scope, Reason, RiskOwner, ExpiresOn,
CASE WHEN ExpiresOn < @Today THEN 'EXPIRED' ELSE 'EXPIRES SOON' END AS ExceptionStatus
FROM dbo.PatchException
WHERE ExpiresOn < DATEADD(DAY, 14, @Today)
ORDER BY ExpiresOn;
Watch out for
- Get-HotFix isn’t the whole picture. It lists Windows updates only, not drivers, Office or SQL Server, and it can’t tell you which updates are missing. Your patching tool is the source for missing updates; this is the cross-check that they landed.
- Remoting and permissions. The service account needs to be able to remote into each server, which usually means local Administrators. Servers it can’t reach are listed in the third query, not silently dropped.
- Pending reboot false positives. Some installers set the same registry keys. A server that always shows pending needs looking at once, not ignoring every month. The script deliberately doesn’t use PendingFileRenameOperations, which many applications set all the time.
- Fill in the Windows Server 2025 date. The lifecycle table leaves it empty for you to add from Microsoft’s lifecycle page, and update the others if Microsoft changes them.
- Exceptions without end dates. The table won’t take one: every exception needs an owner and an expiry. The fourth query lists the ones that have expired or expire within 14 days.
Where it fits
This is part of my Auditing section. The Audit page sets out what a patching report should cover and the thresholds behind it. The UK National Cyber Security Centre’s Cyber Essentials scheme sets the 14-day rule for high and critical updates.
The other Auditing reports: Licensing | Availability: ping and response time | Disk space alerts | Server inventory | Database inventory | Backups | SSIS errors | Integrity checks | SQL Server patching | Security | Capacity
Clive Roberts DBA
3 thoughts on “Windows Patch Report: Did This Month’s Updates Land?”
Comments are closed.