WSUS Status Overview

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