Organizations use WSUS to manage when devices receive updates and also how potential system reboots are scheduled. By using WSUS, organizations ensure that the right updates are sent to devices at the right time. Due to potential issues with windows updates, updating all your devices at the same time might not be the best setup. By utilizing the options that WSUS provides, you can ensure your updates run smoothly.
The report below uses a combination of event log scanning and Windows update scanned data to recreate the WSUS Server Computer Status Chart. To report on update installation errors, Windows event ID 20 (Installation failure) and 31 (Windows Update failed to download an update) are used. If you’re interested in more ways to efficiently Windows updates you can take a look at the related Windows Server Update Services blog post.
Before running the audit, update the Patch Tuesday KB numbers three times in the query below. You can find the most recent KB numbers in our Patch Tuesday Audit query.
To use this report in a chart widget, prefix the report name with “Chart:” and select it in the chart report widget found on a dashboard.
WSUS Configuration Query
Select Top 1000000 'Computers with Errors' As Name,
Count(Distinct tblQuickFixEngineering.AssetID) As Total
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Inner Join dbo.tblNtlog On tblQuickFixEngineering.AssetID = tblNtlog.AssetID
Inner Join dbo.tblNtlogMessage On
tblNtlog.MessageID = tblNtlogMessage.MessageID
Left Join (Select Top 1000000 tblQuickFixEngineering.AssetID
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB5009627','KB5009601','KB5009610',
'KB5009621','KB5009586','KB5009619','KB5009624','KB5009595','KB5009585',
'KB5009546','KB5009557','KB5009545',
'KB5009543','KB5009555','KB5009566')) As SubQuery2 On
tblQuickFixEngineering.AssetID = SubQuery2.AssetID
Where (tblNtlog.Eventcode = 20 And SubQuery2.AssetID Is Null And
tblNtlog.TimeGenerated > DateAdd(DAY, (DateDiff(DAY, 1, DateAdd(MONTH,
DateDiff(MONTH, 0, DateAdd(mm, -1, GetDate())), 0)) / 7) * 7 + (2 * 7), 1))
Or
(SubQuery2.AssetID Is Null And tblNtlog.TimeGenerated > DateAdd(DAY,
(DateDiff(DAY, 1, DateAdd(MONTH, DateDiff(MONTH, 0, DateAdd(mm, -1,
GetDate())), 0)) / 7) * 7 + (2 * 7), 1) And tblNtlog.eventcode = 31)
Union
Select Distinct Top 1000000 'Computers needing updates' As Name,
Count(tblAssets.AssetID) As Total
From dbo.tblAssets
Inner Join dbo.tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join (Select Top 1000000 tblQuickFixEngineering.AssetID
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB5009627','KB5009601','KB5009610',
'KB5009621','KB5009586','KB5009619','KB5009624','KB5009595','KB5009585',
'KB5009546','KB5009557','KB5009545',
'KB5009543','KB5009555','KB5009566')) As SubQuery1 On
tblAssets.AssetID = SubQuery1.AssetID
Where tblState.Statename = 'Active' And tblAssets.Assettype = -1 And
SubQuery1.AssetID Is Null
Union
Select Distinct Top 1000000 'Computers installed' As Name,
Count(tblAssets.AssetID) As Total
From dbo.tblAssets
Inner Join dbo.tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join (Select Top 1000000 tblQuickFixEngineering.AssetID
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB5009627','KB5009601','KB5009610',
'KB5009621','KB5009586','KB5009619','KB5009624','KB5009595','KB5009585',
'KB5009546','KB5009557','KB5009545',
'KB5009543','KB5009555','KB5009566')) As SubQuery1 On
tblAssets.AssetID = SubQuery1.AssetID
Where tblState.Statename = 'Active' And tblAssets.Assettype = -1 And
SubQuery1.AssetID Is Not Null
